T-SQL CREATE TABLE Examples

Here are various code examples for creating a table with Transact-SQL (T-SQL).

The following code creates a table called Artists in the Music database. The table has 3 columns called ArtistId, ArtistName, and ActiveFrom, each with a different data type.

In most cases, you'll probably want to add a primary key and perhaps some other constraints. See below for examples.

Add an Identity Column

This example sets the ArtistId as an identity column. This means that the value for that column will be automatically populated as an incrementing value (each row's value is incremented from the previous row). The 1,1 specifies that the value will start at 1 and it will increment by 1 each time (i.e. 1, 2, 3,... etc).

Add a NOT NULL Constraint

A NOT NULL constraint ensures that there are no null values in the column. Here we add a NOT NULL constraint to the first two columns.

Add a Primary Key Constraint

A primary key constraint sets that column the primary key for the table.

Create a Foreign Key

This example creates an Artists table and an Albums table, then creates a relationship between them. We do this by creating a foreign key constraint on the Albums table. We specify that the Albums.ArtistId column is a foreign key to the Artists.ArtistId column.

Create a DEFAULT Definition

A DEFAULT definition is used to provide a default value for a column. The default value is used when no value has been supplied during an INSERT or UPDATE operation.

Here's what that might look like within a CREATE TABLE statement:

Create a UNIQUE Constraint

A UNIQUE constraint is used to enforce uniqueness on nonprimary key columns.

Now, if someone tries to insert a duplicate value into the Genre column, an error will occur.

Create a CHECK Constraint

A CHECK constraint is used to limit the values that are accepted by one or more columns.

Here's what that might look like within a CREATE TABLE statement: