What are System Tables, File Tables, External Tables and Graph Tables in SQL Server

1. System Tables

• Definition: System tables are internal tables used by SQL Server to store information about the database and server itself. These are metadata tables that provide system-level information and are maintained by SQL Server.
• Usage: They are used to store system information such as database schema, configuration settings, user roles, and permissions. You typically access them through views like sys.objects, sys.columns, or sys.indexes instead of directly.
• Examples:
• sys.tables – information about tables
• sys.indexes – details of indexes
• sys.columns – column metadata for tables

2. File Tables

• Definition: File Tables allow you to store files and documents in the database and access them through SQL Server as though they are stored in a file system. This feature integrates SQL Server with the file system.
• Usage: File Tables are typically used when you want to store unstructured data like documents, images, or videos directly in SQL Server without additional logic. They are particularly useful when managing files using the Windows API.
• Examples: A FileTable would have columns like file_stream, file_type, and path_locator which allow SQL Server to treat files in the table as files in a directory.

3. External Tables

• Definition: External tables are used to query data that resides outside SQL Server, such as in a Hadoop cluster or an Azure Blob storage. SQL Server uses the PolyBase feature to read the external data.
• Usage: External tables allow SQL Server to process and query big data or data that resides on external storage platforms. This is useful for integrating on-premises SQL Server with cloud-based storage solutions.
• Examples: Creating an external table involves specifying an external data source (like Hadoop or Azure Blob), and external file format (like CSV or Parquet), which the table will reference.

  CREATE EXTERNAL TABLE Sales
  (
      ID int,
      Name nvarchar(100)
  )
  WITH (DATA_SOURCE = myHadoopCluster, LOCATION = '/data/sales', FILE_FORMAT = myFileFormat);

4. Graph Tables

• Definition: Graph tables are a new feature in SQL Server (introduced in SQL Server 2017) that allows you to model many-to-many relationships using graph theory. These tables are part of the SQL Server graph database capability and are used to represent nodes and edges in a graph.
• Usage: Graph tables are useful when you need to manage complex relationships, such as social networks, where entities (nodes) are connected via relationships (edges). SQL Server provides special syntax for creating and querying graph tables.
• Examples: You define node tables (representing entities) and edge tables (representing relationships). Node tables store information about entities, while edge tables define the relationships between them.

  CREATE TABLE Person (ID INT PRIMARY KEY, Name NVARCHAR(100)) AS NODE;

  CREATE TABLE Friends (Person1_ID INT, Person2_ID INT) AS EDGE;

Summary:

• System Tables: Metadata and configuration info for SQL Server.
• File Tables: Storing files/documents in SQL Server accessible via the file system.
• External Tables: Querying data from external sources like Hadoop or Azure.
• Graph Tables: Representing nodes and edges for graph-based data structures.