How to Create a Table in SQL Server Management Studio

In this tutorial, you’ll see the complete steps to create a table in SQL Server Management Studio.

You’ll also learn how to create a table using only queries.

Steps to 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 TestDB was created:

Database sql server

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 (for example TestDB), right-click on the Tables folder, and then select Table… from the drop-down list:

How to Create a Table in SQL Server Management Studio

You’ll now be able to create your table. For example, let’s say that you want to create a table called Person. This table will have 4 columns:

  • FirstName
  • LastName
  • City
  • Age

Next, fill those column names and their data types as follows:

Data Type SQL Server

Once you’re done, click on Save:

Save table

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

Table Name

You should now see the new table (dbo.Person) 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:

Refresh tables

Step 3: Add values to the table

Let’s add the following values to our table:

FirstNameLastNameCityAge
BillSmithLondon22
MaryDavisNew York53
MartinGreenLondon27
RobLeeGeneva35
MariaWilsonParis42

You can add values into the table using an insert query:

INSERT INTO TestDB.dbo.Person (FirstName,LastName,City,Age)

VALUES

('Bill','Smith','London',22),
('Mary','Davis','New York',53),
('Martin','Green','London',27),
('Rob','Lee','Geneva',35),
('Maria','Wilson','Paris',42)

You’ll notice that 5 new rows were inserted into the table:
Insert records into a table

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

You can verify that the values got inserted into the table by running the following SELECT query:

SELECT * FROM TestDB.dbo.Person

You should then get the results below:

Table SQL Server

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

Let’s say that you want to create a new table (under the same TestDB database) called Product.

To create the new table, right-click on the Tables folder, and then select Table… from the drop-down list:

Create table SQL Server

You’ll now be able to add the following 3 columns to the table:

  • ProductID
  • ProductName
  • Price

The column names and their data types should be as follows:

Create a Table in SQL Server Management Studio

Next, set the ProductID column as the Primary Key, which uniquely identifies each record (i.e., row) in your table. To do so, right-click on the ProductID column, and then select the ‘Set Primary Key’ option:

Set Primary Key

You’ll now notice the small yellow key which is associated with the ProductID column:

Let’s also select the ProductID as the Identity Column. This will ensure that an auto-increment is applied to the ProductID column whenever a new record is inserted into the table. First, right-click on the ProductID column, and then select the ‘Properties’ option:

Properties

On the ‘Properties’ menu that will appear on the right-hand-side of your screen, select the ProductID as the Identity Column:

Save the table by clicking on Save:

Save a table

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

Table name

After you refresh the Tables folder, you should see the new Product table (with a ‘dbo’ schema):

Table in SQL Server Management Studio

You can then add the following values to the Product table:

ProductNamePrice
Desktop Computer800
Laptop1200
Tablet200
Monitor350
Printer150

Here is the INSERT INTO query that you can run to insert the values into the table:

INSERT INTO TestDB.dbo.Product (ProductName,Price)

VALUES

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

You’ll notice that 5 new records were inserted into the table:

Insert Into records

To verify that the values got inserted into the table, run the following SELECT query:

SELECT * FROM TestDB.dbo.Product

You should then get the results below:

How to Create a Table in SQL Server Management Studio

Notice that it wasn’t necessary to populate the ProductID column using the insert query. This was taken care of by setting the ProductID column as the Identity Column.

How to Create a Table in SQL Server using only Queries

You can create a table in SQL Server Management Studio using only queries.

Let’s recreate the same Product table with the use of queries.

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

DROP TABLE TestDB.dbo.Product

Then, create the Product table using the CREATE TABLE query:

CREATE TABLE TestDB.dbo.Product (
	ProductID int identity(1,1) primary key,
	ProductName nvarchar(50),
	Price int
)

Notice that the ProductID column is set to be the Primary Key with an auto-increment by adding identity(1,1) primary key to the above query.

You can check if the table was created by running this SELECT query:

SELECT * FROM TestDB.dbo.Product

You’ll notice that the table is currently empty:

Empty table

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

INSERT INTO TestDB.dbo.Product (ProductName,Price)

VALUES

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

You’ll notice that 5 new records were inserted into the table:

Insert new records to a table

Rerun the SELECT query:

SELECT * FROM TestDB.dbo.Product

As you can see, all the records are now present in the Product table:

How to Create a Table in SQL Server Management Studio