Creating a SQL Server database table using T-SQL can involve a small amount of code or a large amount, depending on your requirements for the table.

In this article, I create a basic a table with T-SQL that requires a minimum amount of code.

Syntax

The basic syntax for creating a table with T-SQL goes like this:

CREATE TABLE
    { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( { <column_definition> } [ ,...n ] )
[ ; ]

Basically what this means is that you start your statement with CREATE TABLE, followed by the table name. The table name can be prefixed with the schema name, or alternatively, it can be prefixed by both the database name followed by the schema name.

Once you’ve done that, the following lines define the columns that the table will contain. So you type out each column name, followed by its definition.

Example

Here’s an example of one of the tables I created in my article explaining how to create a database.

CREATE TABLE Artists (
  ArtistId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  ArtistName nvarchar(255) NOT NULL,
  ActiveFrom date
);

Before you create any tables, you first need to create the database. In my case, it was done simply with CREATE DATABASE Music although you can add extra options if required.

In the above example, the table name is Artists, and it contains three columns; ArtistId, ArtistName, and ActiveFrom.

Each column’s name is followed by its definition.

• The definition starts with the data type that the column will accept. In this case ArtistId has a data type of int.
• This column also uses IDENTITY(1,1) to specify that an automatically generated number will be generated for each row. The (1,1) part indicates that the numbering will start at 1 and will increment by 1 with each new row. See SQL Server IDENTITY() Property for more information.
• The NOT NULL part specifies that this column must contain a value (i.e. it cannot be null).
• The PRIMARY KEY part specifies that this column is the primary key for the table. A primary key column is the column that uniquely identifies each row. It must contain a unique value, and it cannot be null.

We can see that the next column uses nvarchar(255) data type and the next one uses date.

Create a Relationship

When you create two or more tables, you can also create a relationship between them.

This helps to maintain referential integrity. It enables you to have data in one table that references data in another, while being confident that your database will prevent any inconsistencies from arising in the data between the two tables.

You can create a relationship when you create the table, or you can go back and add it later. To find out how to do both of these, see How to Create a Relationship in SQL.

More Options

The CREATE TABLE statement can be a lot more complex than the above example would imply. How complex you make it will depend on your requirements.

For example, you can create a partitioned table, specify encrypting columns, create computed columns, specify collation, and much more.

See Microsoft’s documentation for the full syntax options available when creating a table in SQL Server.