How to CREATE TABLE in SQL Server

How do I use the SQL Server CREATE TABLE command correctly?

CREATE TABLE builds a new, empty table and defines its columns, data types, and constraints in SQL Server.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why use CREATE TABLE?

CREATE TABLE lets you persist structured data, enforce data types, and set rules such as primary keys, defaults, and constraints in SQL Server. Every relational object—orders, customers, products—starts with this command.

What is the basic syntax?

The minimal form requires a table name and one column.Real-world tables usually include multiple columns, a primary key, and indexes for performance.

Syntax template

CREATE TABLE schema.table_name ( column_name data_type [NULL|NOT NULL] [constraint], ...);

How to add constraints?

Place constraints inline or at the bottom of the column list. Use PRIMARY KEY for uniqueness, FOREIGN KEY to link tables, CHECK for business rules, and DEFAULT for automatic values.

How to create tables for an ecommerce app?

Define Customers, Products, Orders, and OrderItems with proper keys.Link foreign keys so deleting a customer can cascade to orders when needed.

What are best practices?

Pick the narrowest data type, name constraints explicitly, and store dates in DATETIME2. Keep DDL scripts in version control and include IF NOT EXISTS checks in migrations.

Common mistakes and fixes

Forgetting a primary key causes duplicate rows—always declare one. Using float for money leads to rounding errors—use DECIMAL(10,2) instead.

Why How to CREATE TABLE in SQL Server is important

How to CREATE TABLE in SQL Server Example Usage


-- Create a Products table with a CHECK constraint
CREATE TABLE dbo.Products (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock INT NOT NULL CHECK (stock >= 0)
);

How to CREATE TABLE in SQL Server Syntax


-- Basic
CREATE TABLE dbo.Customers (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    email NVARCHAR(255) UNIQUE NOT NULL,
    created_at DATETIME2 DEFAULT SYSDATETIME()
);

-- With foreign keys
CREATE TABLE dbo.Orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
        REFERENCES dbo.Customers(id)
        ON DELETE CASCADE
);

CREATE TABLE dbo.Products (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL
);

CREATE TABLE dbo.OrderItems (
    id INT IDENTITY(1,1) PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    CONSTRAINT fk_orderitems_order FOREIGN KEY (order_id) REFERENCES dbo.Orders(id),
    CONSTRAINT fk_orderitems_product FOREIGN KEY (product_id) REFERENCES dbo.Products(id)
);

Common Mistakes

Using FLOAT for money columns: FLOAT introduces rounding errors. Fix by switching to DECIMAL(10,2) or MONEY for precise currency storage.
Omitting primary keys: Without a PRIMARY KEY the table allows duplicate rows and breaks relationships. Always define a primary key, preferably on a single, non-nullable column.