How to Create Tables in SQL Server — Traditional and Graph Tables Explained

Now that you know how to create a database, let’s dive into creating tables — the core structure where your data lives.

Basic Table Creation Example

Here’s a simple example of creating a table named test inside a database called test:

USE [test]
GO

CREATE TABLE [dbo].[test](
    [id] INT IDENTITY(1,1) NOT NULL,
    [tracename] NVARCHAR(100) NULL,
    [enable] INT NULL,
    CONSTRAINT PK_test PRIMARY KEY (id)
) ON [PRIMARY]
GO

Explanation:

id INT IDENTITY(1,1) NOT NULL — The id column is an integer, set to auto-increment starting from 1 (IDENTITY(1,1)), and it cannot be NULL.
tracename NVARCHAR(100) NULL — This column can store Unicode text up to 100 characters and can be left empty (NULL allowed).
enable INT NULL — An integer column that also allows NULL values.
PRIMARY KEY(id) — The primary key uniquely identifies each row. Here, the id column is the primary key.

By default, if you don’t specify NULL or NOT NULL, SQL Server assumes the column can be NULL.

What is a Primary Key?

The primary key uniquely identifies each record in a table. It enforces uniqueness and non-nullability on that column or set of columns, ensuring no two rows have the same primary key value.

Modern SQL Server Table Features

Beyond traditional tables, SQL Server now supports graph tables — designed for representing complex relationships and networks directly inside the database.

What are Graph Tables?

Graph tables are specialized tables that let you model nodes and edges — perfect for use cases like social networks, recommendation engines, fraud detection, and more.

Node tables represent entities (like people, products, or places).
Edge tables represent relationships between nodes (like friendships, purchases, or connections).

Example of Creating a Node Table:

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

Example of Creating an Edge Table:

CREATE TABLE FriendOf AS EDGE;

Edges store the relationships between nodes, allowing queries that traverse these relationships efficiently.

Other Key Differences & Features in Modern SQL Server Tables

Temporal Tables: Automatically keep a history of data changes for auditing and point-in-time querying.
Partitioned Tables: Large tables can be split into partitions to improve manageability and query performance.
Memory-Optimized Tables: Designed for in-memory OLTP, these tables offer high-performance transaction processing.
Columnstore Indexes: Optimized for analytical queries by storing data column-wise instead of row-wise, boosting data warehouse workloads.

Summary

Use CREATE TABLE to define tables with columns, data types, and constraints.
PRIMARY KEY ensures uniqueness and is essential for data integrity.
Modern SQL Server supports graph tables for connected data models.
Advanced table features like temporal tables, partitioning, and in-memory tables can significantly improve performance and flexibility.

Would you like me to create a follow-up post with example queries for graph traversal or how to use temporal tables? Let me know!

Search This Blog