How to Create a Table in SQL Server

In this tutorial, you’ll see the complete steps to create a table in SQL Server. An example is also reviewed for demonstration purposes.

Steps to Create a Table in SQL Server

Step 1: Create a database

If you haven’t already done so, create a database where the table will be stored. For example, you may use the following query to create a database called the test_database:

CREATE DATABASE test_database

Step 2: Create a table

Next, create a table under your database.

For instance, let’s create a table called ‘products‘ which contains 2 columns:

• product_name
• price

The table should store the following data:

Where the data type for the ‘product_name‘ column would be nvarchar(50), while the data type for the ‘price‘ column would be int (for integers).

You can then create the table using the following CREATE TABLE query under your database:

CREATE TABLE products (
	product_name nvarchar(50),
	price int
)

Step 3: Insert values into the table

Let’s now add the following values into the ‘products’ table:

You can insert values into the table using an INSERT INTO query:

INSERT INTO products (product_name, price)

VALUES

('Desktop Computer',800),
('Laptop',1200),
('Tablet',200),
('Monitor',350),
('Printer',150)

Step 4: Verify that the values were inserted into the table

Finally, run the following SELECT query to verify that the values were inserted into the table:

SELECT * FROM products

You should then get the results below:

Create a Table in SQL Server with Primary Key and Identity Column

A Primary Key uniquely identifies each record (i.e., row) in your table, while an Identity Column ensures that an auto-increment is applied to your column whenever a new record is inserted into the table.

Let’s say that you want to create a table with a Primary Key and Identity Column.

For example, suppose that you want to recreate the ‘products‘ table with the following 3 columns:

• product_id
• product_name
• price

Where the product_id column will be selected as the Primary Key as well as the Identity Column.

This is how the new ‘products‘ table should look like:

Let’s now recreate this table with the use of queries.

To begin, drop the ‘products’ table in order to start from scratch. You can drop the ‘products’ table using the query below:

DROP TABLE products

Then, recreate the table using the CREATE TABLE query:

CREATE TABLE products (
	product_id int identity(1,1) primary key,
	product_name nvarchar(50),
	price int
)

You can then add records to the table by running this INSERT INTO query:

INSERT INTO products (product_name, price)

VALUES

('Desktop Computer',800),
('Laptop',1200),
('Tablet',200),
('Monitor',350),
('Printer',150)

5 new records will be inserted into the table (notice that it wasn’t necessary to populate the product_id column using the insert query. This was taken care by setting the product_id column as the Identity Column).

Rerun the SELECT query to see the final results:

SELECT * FROM products

As you can see, all the records are now present in the ‘products’ table (where the product_id column was added with an auto-increment):