Create New Table in SQL Server

Tables are database objects that contain all the data in a database. In a table, data is logically organized in rows and columns. Each column represents a field and each row represents a unique record. Each column has a data type associated with it. It represents the type of data in that column. Each table is uniquely named in a database.

Number of tables in a database is only limited by the number of objects allowed in the database (2,147,483,647). A user-defined table can have up to 1024 columns.

There are two ways to create a new table in SQL Server:

• Using T-SQL Script
• Using Table Designer in SQL Server Management Studio

Create Table using T-SQL Script

You can execute the CREATE TABLE statement in the query editor of SSMS to create a new table in SQL Server.

CREATE TABLE [database_name.][schema_name.]table_name (
    pk_column_name data_type PRIMARY KEY,
    column_name2 data_type [NULL | NOT NULL],
    column_name3 data_type [NULL | NOT NULL],
    ...,
    [table_constraints]
);

The following CREATE TABLE statement creates the Employee table.

CREATE TABLE Employee
(  
    EmployeeID int,
    FirstName nvarchar(50) NOT NULL,  
    LastName nvarchar(50) NOT NULL, 
    EMail nvarchar(50),
    Phone varchar(15),
    HireDate date,
    Salary Money
);

The above SQL script will create a new Employee table in the default schema dbo and into a database pointed by the query editor in SSMS.

You can specify the full table name in the DatabaseName.SchemaName.TableName format. The following creates the Employee table in the default dbo schema and HR database in SQL Server.

CREATE TABLE HR.dbo.Employee
(  
    EmployeeID int IDENTITY(1,1) PRIMARY KEY,
    FirstName nvarchar(50) NOT NULL,  
    LastName nvarchar(50) NOT NULL, 
    EMail nvarchar(50),
    Phone varchar(15),
    HireDate date,
    Salary Money
);

In the above T-SQL:

• HR is the database name, dbo is the schema name in which a table will belongs to, and Employee is a table name. If the database name is not specified then it defaults to the current database that the query editor is targetting. If the schema name is not specified then the table will belongs to a default schema.
• A table name can be a maximum of 128 characters.
• Column names are specified in ColumnName DataType NOT NULL format separated by a comma. It creates EmployeeId, FirstName, LastName, Email, Phone, HireDate, Salary columns.
• Specify the data type after the column name to indicate the kind of data a column is going to be stored.
• NOT NULL specifies that the column will not allow null values. By default a column will allow null value if you don't specify NOT NULL.
• IDENTITY(1,1) make an integer column as auto-generated indentity column where (1,1) specifies that the value will start from 1 and increase by 1 on each insert operation. Mostly, the primary key columns are identity column.
• Make a column as a primary key by specifying PRIMARY KEY keyword after the column name and data type. If a table has only one primary key like above, then specify PRIMARY KEY.

The following drops the Employee table if already exists and then creates the Employee table.

USE HR

GO
DROP TABLE IF EXISTS HR.dbo.Employee; --drop table if already exists

CREATE TABLE Employee
(  
    EmployeeID int IDENTITY(1,1) PRIMARY KEY,
    FirstName nvarchar(50) NOT NULL,  
    LastName nvarchar(50) NOT NULL, 
    EMail nvarchar(50),
    Phone varchar(15),
    HireDate date,
    Salary Money
);

The following specifies the Primary Key constraints at the end.

USE HR

GO
DROP TABLE IF EXISTS HR.dbo.Employee; --drop table if already exists

CREATE TABLE Employee
(  
    EmployeeID int IDENTITY(1,1),
    FirstName nvarchar(50) NOT NULL,  
    LastName nvarchar(50) NOT NULL, 
    EMail nvarchar(50),
    Phone varchar(15),
    HireDate date,
    ManagerID int,
    Salary float,
    DepartmentID int,
    CONSTRAINT pk_Employee_EmployeeID PRIMARY KEY(EmployeeID)
);

Learn full syntax of CREATE TABLE.

Create Table using SSMS

You can design a new table using the table designer in SQL Server Management Studio.

To design a new table, open SSMS and connect to your sql server instance.

In Object Explorer, expand the HR database or the database where you want to create a new table.

Now, right-click on the Tables folder and select New Table, as shown below.

This will open a table designer where you can enter a name of a column, its data type, and check the checkbox if a column allows null, as shown below.

Column Name: Type a unique column name.

Data type: Choose a data type for the column from the dropdown list. Choose the appropriate length for the string data types.

Allow Nulls: Choose whether to allow Nulls for each column by check the check-box.

Enter columns info in a separate row for all the columns you want to take in your table. The followings are columns of the Employee table.

To set the properties for a column, such as identity specification, computed column specification, etc., select a column and set the property value in the Column Properties tab in the bottom pane.

To specify a column as a primary key column, right-click on the column row and select Set Primary Key, as shown below.

You can configure the primary key that will auto generate unique integers by setting it as identity column, as shown below.

By default, the table is created in the dbo schema. To specify a different schema for the table, right-click in the Table-Designer pane and select properties. From the Schema drop-down list, select the appropriate schema.

Now, from the file menu, choose Save to create this table. Enter Employee as a table name and click OK.

To view the new table, refresh the Tables folder in the Object Explorer. The EMPLOYEE table is now available under the Tables node.

Thus, you can design a new table using the table designer in SSMS (SQL Server Management Studio).