SQL Server tables, data types, constraints and indexes
A good schema makes correct data easy and wrong data impossible, and good indexes make the common queries cheap. This page covers the data definition side of T-SQL in the order you meet it when designing a table: pick data types, create the table, change it later, remove it, enforce rules with constraints, and add the indexes your queries need. It uses a dbo.Products table for OrdersPortal next to the Customers and Orders tables from the sample data.
No database was available while this page was written, so the samples were not run here. Statements and behavior were checked against the Microsoft Learn T-SQL reference for SQL Server 2025 (17.x); version notes say where older releases differ.
Topics on this page
Section titled “Topics on this page”| Topic | Use it when |
|---|---|
| Data types | You decide what each column can store. |
| CREATE TABLE | You define a new table with its columns and keys. |
| ALTER TABLE | You add, change or remove columns and constraints on an existing table. |
| Drop and truncate | You remove a table or all of its rows. |
| Constraints | You want the database itself to reject invalid data. |
| Indexes | Queries need to find rows without scanning the whole table. |
Data types
Section titled “Data types”Every column has a data type that limits what it stores and how much space it uses. The ones you will use most:
| Kind | Types | Notes |
|---|---|---|
| Exact numbers | int, bigint, smallint, tinyint, decimal(p,s), bit |
decimal for money and quantities; bit for flags. |
| Approximate numbers | float, real |
Scientific values only; never money. |
| Text | varchar(n), nvarchar(n), varchar(max), nvarchar(max) |
nvarchar stores Unicode (UTF-16). varchar with a _UTF8 collation (SQL Server 2019+) can also store Unicode. |
| Date and time | date, time(n), datetime2(n), datetimeoffset(n) |
Prefer these over the older datetime and smalldatetime. |
| Other | uniqueidentifier, varbinary(n), json |
The native json type is new in SQL Server 2025 and Azure SQL; earlier versions store JSON in nvarchar(max). |
Choose the narrowest type that fits the real domain: narrower rows mean more rows per 8 KB page, smaller indexes and less memory. decimal(10,2) holds up to 99,999,999.99 exactly; float cannot represent 19.99 exactly, and the error accumulates over sums.
DECLARE @PriceF float = 0.1, @PriceD decimal(10,2) = 0.1;
SELECT @PriceF * 3 AS FloatResult, -- approximate @PriceD * 3 AS DecimalResult, -- exact CASE WHEN @PriceF * 3 = 0.3 THEN 'equal' ELSE 'not equal' END AS FloatEquals;Expected result (the float product is stored as 0.30000000000000004; depending on the client it is displayed as 0.3 or with all digits, but the comparison shows it is not 0.3):
FloatResult DecimalResult FloatEquals0.3 0.30 not equalPitfalls:
varcharfor names and free text without a UTF-8 collation loses characters outside the code page (for example田中becomes??). Usenvarcharor a UTF-8 collation.nvarchar(max)everywhere. Large-value types cannot be index keys and can be stored off-row; size columns to the real domain.datetimeprecision. It rounds to increments of .000, .003 or .007 seconds;datetime2is precise to 100 ns and uses less space at low precision.
CREATE TABLE
Section titled “CREATE TABLE”CREATE TABLE schema.name (column definitions, table constraints) creates a table. Each column gets a type, nullability (NULL or NOT NULL; always state it explicitly, because session settings can change the default), and optionally a DEFAULT, an IDENTITY(seed, increment) property, or a computed expression. Keys and other constraints can be declared inline on a column or as named table constraints at the end; named ones are easier to manage in scripts and migrations.
An IDENTITY column generates increasing numbers but does not guarantee there are no gaps: rolled-back inserts and restarts consume values. If you need numbers shared across tables, or before the insert, use a SEQUENCE object (SQL Server 2012).
CREATE TABLE dbo.Products ( Id INT IDENTITY(1,1) NOT NULL, Sku VARCHAR(20) NOT NULL, Name NVARCHAR(100) NOT NULL, UnitPrice DECIMAL(10,2) NOT NULL, IsActive BIT NOT NULL CONSTRAINT DF_Products_IsActive DEFAULT (1), CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_Products_CreatedAt DEFAULT (SYSUTCDATETIME()), CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (Id), CONSTRAINT UQ_Products_Sku UNIQUE (Sku), CONSTRAINT CK_Products_UnitPrice CHECK (UnitPrice >= 0));
INSERT INTO dbo.Products (Sku, Name, UnitPrice)OUTPUT inserted.Id, inserted.Sku, inserted.IsActiveVALUES ('PAN-24', N'Frying pan 24 cm', 39.90), ('KNF-CH', N'Chef''s knife', 54.00);Expected result:
Id Sku IsActive1 PAN-24 12 KNF-CH 1Pitfalls:
- Unnamed constraints.
DEFAULT 1without a name gets a generated name such asDF__Products__IsAct__3B75D760, different on every server, which makes laterALTER TABLE … DROP CONSTRAINTscripts fragile. - A random
uniqueidentifieras the clustered key.NEWID()values insert all over the index, causing page splits and fragmentation; use an ever-increasing key, orNEWSEQUENTIALID()if you need GUIDs. - Forgetting the schema. Create objects as
dbo.Products(or your own schema) so ownership and permissions are predictable.
ALTER TABLE
Section titled “ALTER TABLE”ALTER TABLE changes an existing table without recreating it: ADD columns or constraints, ALTER COLUMN to change a type, size or nullability, and DROP COLUMN or DROP CONSTRAINT. Every ALTER TABLE takes a schema-modification lock, so even a quick change waits for, and then blocks, running queries on that table.
Adding a NOT NULL column to a table that already has rows requires a DEFAULT. In Enterprise edition (SQL Server 2012 and later) this is a metadata-only, near-instant operation when the default is a runtime constant; in Standard edition every row is updated, which can take a long time on a big table. ALTER COLUMN … NOT NULL succeeds only if no row currently holds NULL, and shrinking a column fails if existing data would be truncated.
ALTER TABLE dbo.Products ADD Category NVARCHAR(50) NOT NULL CONSTRAINT DF_Products_Category DEFAULT (N'Uncategorized');
ALTER TABLE dbo.Products ALTER COLUMN Name NVARCHAR(150) NOT NULL; -- widen; existing data is kept
SELECT Id, Sku, Category FROM dbo.Products ORDER BY Id;Expected result:
Id Sku Category1 PAN-24 Uncategorized2 KNF-CH UncategorizedPitfalls:
NOT NULLwithout a default fails on a non-empty table with error 4901. Add the default, or add the column as nullable, backfill in batches, then make itNOT NULL.ALTER COLUMNon a column used by an index or constraint fails until you drop and recreate the dependent object.- Running schema changes at peak time. Even metadata-only changes need a brief exclusive lock; long-running queries make the
ALTERwait and everything else queue behind it. UseWAIT_AT_LOW_PRIORITYfor online index operations and schedule changes.
Drop and truncate
Section titled “Drop and truncate”DROP TABLE removes the table definition, its data, indexes, triggers and permissions. DROP TABLE IF EXISTS (SQL Server 2016) does not fail when the table is missing, and one statement can drop several tables. A table referenced by a foreign key cannot be dropped until the referencing constraint or table is dropped first.
TRUNCATE TABLE keeps the table and removes all its rows; DELETE removes some or all rows one at a time. The comparison is on Changing data. Outside a transaction, the only way back from a DROP is a restore.
DROP TABLE IF EXISTS dbo.ProductImportStaging; -- no error if it does not exist
TRUNCATE TABLE dbo.OrderStatusHistory; -- empty, structure and indexes keptPitfalls:
- Dropping in the wrong environment. Script destructive changes, review them, and check
@@SERVERNAMEandDB_NAME()at the top of the script. - Dependency order. Drop child tables (or their foreign keys) before parents, or the statement fails with error 3726.
- Views and procedures that reference the table are not dropped and fail at run time. Find dependents with
sys.sql_expression_dependenciesfirst.
Constraints
Section titled “Constraints”Constraints are rules the Database Engine enforces on every write, whichever application, script or tool makes it:
PRIMARY KEYidentifies each row: unique, not null, one per table. It creates a unique clustered index by default if the table has no clustered index yet, otherwise a nonclustered one.UNIQUEforbids duplicates in other candidate keys. It creates a unique nonclustered index by default and allows a singleNULLper column (a filtered unique index allows many).FOREIGN KEYrequires a value to exist in the referenced key, with optionalON DELETE/ON UPDATE CASCADE,SET NULLorSET DEFAULT.CHECKrequires a Boolean condition to not be false for each row; note thatUNKNOWN(from aNULL) passes.DEFAULTandNOT NULLcomplete the picture.
ALTER TABLE dbo.Orders ADD CONSTRAINT CK_Orders_Status CHECK (Status IN ('Pending', 'Shipped', 'Cancelled', 'OnHold'));
INSERT INTO dbo.Orders (Id, CustomerId, OrderDate, Status, Amount)VALUES (1010, 99, '2026-11-15', 'Pending', 10.00); -- customer 99 does not existExpected result: the INSERT fails with error 547, “The INSERT statement conflicted with the FOREIGN KEY constraint …”, and no row is added.
Pitfalls:
- Validation only in the application. Batch jobs, admin scripts and other services bypass it. Put the invariant in a constraint and keep friendly checks in the application.
WITH NOCHECK. Adding or re-enabling a constraint without checking existing rows marks it untrusted (is_not_trusted = 1), and the optimizer can no longer use it. Re-enable withWITH CHECK CHECK CONSTRAINT.- Unindexed foreign keys. SQL Server does not index the referencing column automatically. Index
Orders.CustomerId, or deletes onCustomersand joins scanOrders.
Indexes
Section titled “Indexes”Rowstore indexes are B+ trees. A clustered index stores the table’s rows in the leaf level, ordered by the key, so there can be only one; a table without one is a heap. A nonclustered index is a separate structure holding the key columns plus a row locator (the clustered key, or a row ID for a heap); a table can have up to 999. INCLUDE columns add non-key values to the leaf level so the index can cover a query without a lookup into the base table. Filtered indexes (WHERE …) index a subset of rows, and columnstore indexes serve large analytic scans.
Indexes speed up reads that filter, join or sort on their leading key columns, and slow down every insert, update and delete that has to maintain them. Design them from real query patterns: equality columns first, then range columns, then INCLUDE what the query returns. Check plans for seeks versus scans and key lookups, and use Query Store and the missing-index DMVs as hints, not as instructions.
-- Supports: WHERE CustomerId = @c ORDER BY OrderDate DESC, returning Status and AmountCREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate ON dbo.Orders (CustomerId, OrderDate DESC) INCLUDE (Status, Amount);
-- A small index for the operations queue onlyCREATE NONCLUSTERED INDEX IX_Orders_Pending ON dbo.Orders (OrderDate) WHERE Status = 'Pending';
SELECT i.name, i.type_desc, i.has_filterFROM sys.indexes AS iWHERE i.object_id = OBJECT_ID(N'dbo.Orders')ORDER BY i.index_id;Expected result (the primary key index name is system-generated because the sample table declared it inline):
name type_desc has_filterPK__Orders__… CLUSTERED 0IX_Orders_CustomerId_OrderDate NONCLUSTERED 0IX_Orders_Pending NONCLUSTERED 1Pitfalls:
- Indexing every column “just in case”. Each index adds write cost, log volume, memory and maintenance; unused indexes show up in
sys.dm_db_index_usage_statswith writes but no seeks or scans. - Wrong key order. An index on
(OrderDate, CustomerId)cannot seekWHERE CustomerId = 42efficiently; the leading column must match the predicate. - Non-sargable predicates. Functions on the column, implicit conversions and leading
%wildcards prevent seeks no matter which index exists.
Choosing between them
Section titled “Choosing between them”| Need | Use |
|---|---|
| Identify each row | PRIMARY KEY on a narrow, stable, ever-increasing column |
| Another value that must be unique | UNIQUE constraint (or a filtered unique index to allow many NULLs) |
| Parent row must exist | FOREIGN KEY, plus an index on the referencing column |
| Business rule on one row | CHECK constraint |
| Fast lookups by a non-key column | Nonclustered index with INCLUDE for returned columns |
| A hot subset such as “pending” | Filtered index |
| Large aggregations over millions of rows | Columnstore index |
Interview questions
Section titled “Interview questions”What is the difference between a clustered and a nonclustered index? A clustered index stores the data rows themselves in the leaf level in key order, so a table has at most one. A nonclustered index is a separate B+ tree of key values plus a pointer to the row (the clustered key or a RID), and a table can have many. A query that needs columns not in a nonclustered index does a key lookup into the clustered index unless those columns are included.
Does a primary key always create a clustered index?
No. It creates a clustered index only if the table does not already have one and you do not specify NONCLUSTERED. You can deliberately make the primary key nonclustered and cluster on a different column.
How many NULLs does a UNIQUE constraint allow in SQL Server?
One per column (or one per combination for a multi-column constraint), because NULLs are treated as equal for uniqueness. To allow many, use a filtered unique index WHERE Column IS NOT NULL.
What is a covering index?
An index that contains every column a query reads, as key or INCLUDE columns, so the query can be answered from the index alone without lookups into the base table.
Senior scenario: inserts into a 500-million-row order table have become slow and the table has 23 indexes. What do you do?
Measure before dropping anything. Use sys.dm_db_index_usage_stats (since the last restart) and Query Store to find indexes with many updates and few or no seeks and scans, and look for duplicates and indexes whose keys are left-prefixes of others. Consolidate overlapping indexes into one with the right key order and INCLUDE list, drop the unused ones after confirming no monthly or year-end job needs them (disable first so they can be rebuilt), and check that the clustered key is narrow and ever-increasing so inserts append instead of splitting pages. Then re-measure insert latency and the plans of the top read queries.
Related pages
Section titled “Related pages”- SQL Server introduction: key terms such as heap, execution plan and tempdb.
- Querying data: sargable filters that let indexes seek.
- Changing data: how constraints reject bad writes, and
DELETEversusTRUNCATE. - Temporal tables: system-versioned tables that keep row history.