Create Table in SQL Server

In this tutorial, we learn how to Create Table in SQL Server. We can create Tables either using the Create Table statement or using the Table designer option in SSMS. We learn both of these methods of Creating the SQL Server tables in the tutorial.

Before Creating the tables

Table Names

The fully qualified table names consists of three parts. database name, schema name & the actual table name. The fully qualified table name must be UNIQUE in a SQL Server instance.

Note that, whenever we create the database, the SQL Server automatically creates the default schema dbo. We can create more schema using the create schema <schema_name> query.

The table name must be unique under a schema.

The name must follow the naming rules for the identifiers. Also note that you should avoid using the reserved keywords. If you use them then you must always enclose the table in square brackets as delimited identifiers.

Columns

We must also give a name to each column, which also must follow the naming rules for the identifiers. The column name must be UNIQUE within the table. Again avoid using the reserved keywords, else you need to follow the delimited identifiers rules when referring the columns in Query.

Data Types

It is also necessary to assign data type to each column. The data type defines the type of the data that the column is going to hold. This can be a number, string , date and Boolean etc.

Primary Key

Each table must also have primary key (although SQL Server allows you to create table without Primary Key). Primary key is a column (or columns), whose value we can use to uniquely identify a row.

NULL/NOT NULL

We also need to specify whether the column accepts the NULL value or not using the keyword NULL (allows NULL) or NOT NULL (does not allows NULL).

Using NULL allows us to insert a row to the table, without providing any value to the column

Create Table Syntax

The Simplified syntax of creating the table as shown below

The syntax starts with CREATE TABLE. We follow it up with the name of the table table_name.

Next inside the brackets we specify the column definitions. Each column definition must start with column name (column_1, column_2 etc.) followed by data type (data_type) of the column.

We also need to designate at least one column as primary key by using the PRIMARY KEY keyword after the column name.

Create Table Example Query

The following is the example Create Table Query for a table with the name employee. We have added 7 columns in the table.

The EmployeeID is the Primary Key with the data type as int, which stands for integer value. You can only store numbers in this column. We also mark this as PRIMARY KEY.

The Salary column has the data type decimal(10,0),

The other columns are FirstName, LastName, Department, Designation & Email. All of them has the data type as Varchar of various lengths. The varchar data type accepts string data. We specify the max length of the string can accept as the argument to varchar.

The columns can be either NULL or NOT NULL. If we do not specify anything, then the SQL Server treats the column as NULL.

Create table Using SQL Statement

Now, let us create the table using the above query.

Open SSMS. If you have not installed it, then refer to the install SSMS tutorial. Connect to the SQL Server instance.

First, we need to create a database to hold our table. Create a new database HR. You can refer to the tutorial how to create the database.

1. Select the newly created database i.e. HR.
2. On the menu select File -> New -> Query with current connection
3. This will open the Query window
4. Paste the following query to the Query window
5. Click on Execute to run the query
6. Commands completed successfully message appears

You can view the employee table from the object explorer.

Select the tables under HR database under database node. Right click and refresh. You will see the employee table. Expand it and then expand the columns node to view the columns.

Create Table Using Table Designer

We can also create the table using the Table Designer

1. Open SSMS and connect to the database instance.
2. Select Database node and expand it.
3. Select the database, where you want to create the new table (HR in the example below).
4. Select Tables node and right click and select new -> tables.
5. Type column names, choose data types from the drop down, and choose whether to allow nulls for each column.
6. Select the EmployeeID Column and click on the icon the toolbar to mark it as Primary key. (You can also use the Table Designer -> Set Primary Key Option.
7. Click on Save Icon in the tool bar or file -> Save to save the table.
8. Enter the table name as Employee and click on Ok

Syntax of Create Table Command

The create table syntax is much more complex. The complete Syntax is as shown below. We will learn them in other tutorials.

Reference

Create Table TSQL Syntax