Creating Tables in SQL Server

Tables are the core building blocks of any SQL Server database. The CREATE TABLE statement defines the table name, columns, data types, and constraints that enforce data integrity. In this tutorial, you will learn how to design and create tables effectively.

Basic Table Creation

A minimal table needs a name and at least one column with a data type. SQL Server offers many data types including INT, BIGINT, VARCHAR, NVARCHAR, DECIMAL, DATE, DATETIME2, and BIT.

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    HireDate DATE DEFAULT GETDATE(),
    Salary DECIMAL(10,2),
    IsActive BIT DEFAULT 1
);

IDENTITY Columns

The IDENTITY(seed, increment) property auto-generates sequential values for a column. It is the SQL Server equivalent of auto-increment in MySQL. The seed is the starting value, and the increment is the step.

CREATE TABLE Orders (
    OrderID BIGINT IDENTITY(1000, 1) PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME2 DEFAULT SYSDATETIME(),
    OrderTotal DECIMAL(12,2) NOT NULL
);

Constraints

Constraints enforce rules on your data. SQL Server supports NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY, and FOREIGN KEY constraints.

CREATE TABLE Products (
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    SKU VARCHAR(20) NOT NULL UNIQUE,
    ProductName NVARCHAR(200) NOT NULL,
    Price DECIMAL(10,2) NOT NULL
        CONSTRAINT CK_Products_Price CHECK (Price > 0),
    CategoryID INT NOT NULL,
    CreatedAt DATETIME2 DEFAULT SYSDATETIME()
);

Foreign Keys

Foreign keys link tables together and enforce referential integrity. When a foreign key is defined, SQL Server ensures that values in the child table always reference a valid row in the parent table.

CREATE TABLE OrderItems (
    ItemID INT IDENTITY(1,1) PRIMARY KEY,
    OrderID BIGINT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL DEFAULT 1,
    UnitPrice DECIMAL(10,2) NOT NULL,

    CONSTRAINT FK_OrderItems_Orders
        FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    CONSTRAINT FK_OrderItems_Products
        FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Checking If a Table Exists

Before creating a table, you can check if it already exists to avoid errors. SQL Server 2016+ supports the DROP TABLE IF EXISTS syntax.

-- SQL Server 2016+
DROP TABLE IF EXISTS TempReport;

CREATE TABLE TempReport (
    ReportID INT IDENTITY(1,1) PRIMARY KEY,
    ReportName NVARCHAR(100) NOT NULL,
    GeneratedAt DATETIME2 DEFAULT SYSDATETIME()
);

Good table design with appropriate data types, constraints, and relationships is the foundation of a reliable database. Take the time to plan your schema before writing any application code.