SQL Server Create Table - T-SQL

Database tables are some of a relational database’s most crucial database objects. Database tables allow us to store data in a given database.

A relational database such as SQL Server allows us to create a single table of the same name within the same database and schema. We can then create one or more columns in the specified table where each column comprises the column name, the data type, and other properties such as constraints.

In this tutorial, we will learn how to create a table in SQL Server using the CREATE TABLE statement.

SQL Server Create Table Statement.

As the name suggests, the CREATE TABLE statement in Transact-SQL allows us to create a new database table.

The statement syntax is as shown below:

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

NOTE: The above shows a simplified syntax of the CREATE TABLE statement. You can reference the documentation to discover more about more complex parameters.

Statement Arguments

The following shows the arguments of the above statement:

• database_name - This specifies the database name we wish to create the table. The specified database must exist on the server. Otherwise, the statement will return an error.
• schema_name - This parameter specifies the name of the schema to which the table belongs.
• table_name - This defines the name of the table we wish to create. The specified table name must follow the naming rules in SQL Server and can have a maximum of 128 characters.
• column_definition - the column definition defines the columns that will be included in the table. This will include the column names, the constraints, the data types, and more.

NOTE: Each table must have a column that acts as the primary key.

As mentioned, the CREATE TABLE statement is more complex and supports many more options than those provided in this section. Stay tuned for more upcoming SQL Server tutorials to explore more advanced options for creating a table in SQL Server.

SQL Server Create Table Example

The following statement demonstrates how we can use the CREATE TABLE statement in SQL Server to create a table called network_info.

CREATE TABLE network_info (
    id INT IDENTITY(1,1) PRIMARY KEY,
    ip_address VARCHAR(50) NOT NULL,
    subnet_mask VARCHAR(50),
    gateway VARCHAR(50),
    dns_server VARCHAR(50)
);

In the example above, we use the CREATE TABLE statement to create a new table called network_info in the currently selected database.

In the table, we define the following columns and properties.

• id - This defined an auto-incrementing primary key column.
• ip_address - This column allows us to store the IP address of the network device or interface. We set this column to have the NOT NULL constraint, which ensures that we cannot have NULL values in this column.
• subnet_mask - This column allows us to store the subnet mask associated with the IP address as a VARCHAR string.
• gateway and dns_server - Finally, we have the last two columns to store the gateway and DNS Server addresses as string types.

NOTE: If we do not specify the target schema name, SQL Server will create the table in the default schema of the user who is executing the command. The default schema name is set to dbo.

Creating a Table in Specific Schema.

We can also specify which schema we wish to create the table. For example, if we have a schema called Data, we can specify that we wish to create the network_info table in that schema by specifying the schema name in the statement as shown:

CREATE Data.TABLE network_info (
    id INT IDENTITY(1,1) PRIMARY KEY,
    ip_address VARCHAR(50) NOT NULL,
    subnet_mask VARCHAR(50),
    gateway VARCHAR(50),
    dns_server VARCHAR(50)
);

In this case, we tell the SQL Server that wishes to create the table in the Data schema.

Conclusion

In this tutorial, you learned the fundamentals of working with the CREATE TABLE statement in SQL Server to create a new table in the default or a given schema.