Create a Table

 

Create a Table in SQL Server Management Studio

Step 1: Create a database

If you haven’t already done so, create a database in SQL Server Management Studio. For illustration purposes, a database called Sales was created:

 

Note: To avoid any permission issues, you may want to run SQL Server Management Studio as an administrator.

 

Step 2: Create a table

Under your database, right-click on the Tables folder, and then select New, then select Table… from the drop-down list

 

 

 

You’ll now be able to create your table. Next, fill those column names and their data types

 

 

Once you’re done, click on Save:

 

 

Type ‘CUSTOMER’ for your table name, and then click OK:

 

 

You should now see the new table (dbo. CUSTOMER’) under the Tables folder associated with your database:

 

 

Note: you may need to refresh the Tables folder first. You can do that by right-clicking on the Tables folder and then selecting Refresh from the drop-down list:

 

  

Create a Table in SQL Server using Queries

 

To create a new table, you use the CREATE TABLE statement as follows:

 

CREATE TABLE [database_name].[schema_name].table_name (

    pk_column data_type PRIMARY KEY,

    column_1 data_type NOT NULL,

    column_2 data_type,

    ...,

    table_constraints

);

 

In this syntax: 

• Specify the name of the database in which the table is created. The database_name must be the name of an existing database. If you don’t specify it, the database_name defaults to the current database.
• Specify the schema to which the new table belongs.
• Specify the name of the new table.
• Each table should have a primary key which consists of one or more columns. Typically, you list the primary key columns first and then other columns. Each column has an associated data type specified after its name in the statement. A column may have one or more column constraints such as NOT NULL and UNIQUE.
• A table may have some constraints specified in the table constraints section such as FOREIGN KEY, PRIMARY KEY, UNIQUE and CHECK.

Note: that CREATE TABLE is complex and has more options than the syntax above.

 

The following statement creates a new table named CUSTOMER:

CREATE TABLE [Sales].[dbo].[CUSTOMER](

       [CUSTOMER_ID] [int] PRIMARY KEY IDENTITY (1, 1),

       [FIRST_NAME] [varchar](50) NOT NULL,

       [LAST_NAME] [varchar](50) NOT NULL,

       [DATE_OF_BIRTH] [date] NULL,

       [CITY_NAME] [varchar](100) NULL

)