Tables (Transact‑SQL)

Tables are the fundamental objects used to store data in a relational database. This reference provides syntax, options, and examples for creating, altering, and dropping tables, as well as defining constraints and indexes.

CREATE TABLE

Creates a new table in the database.

CREATE TABLE [schema_name.]table_name
(
    column_name data_type [column_constraint],
    ...
    [table_constraint]
)
[ON { partition_scheme_name | filegroup_name } ]

Example

CREATE TABLE dbo.Products
(
    ProductID   INT           IDENTITY(1,1) PRIMARY KEY,
    Name        NVARCHAR(100) NOT NULL,
    Price       MONEY         CHECK (Price >= 0),
    CreatedDate DATETIME2     DEFAULT SYSDATETIME()
);

ALTER TABLE

Modifies an existing table’s structure.

Add a Column

ALTER TABLE dbo.Products
ADD Description NVARCHAR(500) NULL;

Add a Constraint

ALTER TABLE dbo.Products
ADD CONSTRAINT CK_Price_NonNegative CHECK (Price >= 0);

DROP TABLE

Deletes a table and its data.

DROP TABLE [IF EXISTS] schema_name.table_name;

Table Constraints

Constraints enforce data integrity. Common constraints include:

• PRIMARY KEY – Uniquely identifies each row.
• FOREIGN KEY – Enforces referential integrity.
• UNIQUE – Guarantees uniqueness of column values.
• CHECK – Validates column values against a Boolean expression.
• DEFAULT – Supplies a default value for a column.

Indexes

Indexes improve query performance. Create an index on a table using CREATE INDEX.

CREATE NONCLUSTERED INDEX IX_Products_Name
ON dbo.Products (Name);

Best Practices

• Use appropriate data types and lengths.
• Define primary keys as narrow, unique, and not nullable.
• Prefer clustered indexes on columns frequently used for range queries.
• Document constraints and indexes for maintainability.