How to Create a Table in SQL Server Management Studio Using Query?

by

In this SQL Server tutorial, you will learn how to create a table in SQL Server Management Studio using query.

You will understand the concept of a table in SQL Server and then understand how to open the query editor in SQL Server Management Studio, where you can run the query.

After that, you will understand table syntax, and you will later create a table. Finally, you will create a table and view the created table in SSMS.

Create Table in SQL Server Management Studio Using Query

Before you learn how to create a table in SQL Server Management Studio using query, you must understand ‘What is a table in SQL Server?’.

If you have used the excel, then you probably know the spreadsheets. A table is like a spreadsheet that stores information in an organized way, such as storing information in rows and columns.

In other words, the table consists of rows and columns. The row of the table represents the record or piece of data and the column in the table represents the attribute of that data.

Now you know about tables in SQL Server, let’s understand how to create them using the query in SSMS.

Open the SSMS and connect to the SQL Server instance, after connecting, press CTRL + N to open the Query Editor as shown below.

Create Table in SQL Server Management Studio Using Query Opening SSMS

To create a table in SQL Server using query follow the below syntax.

CREATE TABLE table_name (
column_name1 datatype1 constraint,
column_name2 datatype2 constraint,
...
);

Where,

  • CREATE TABLE: It is a command in SQL Server to create a table.
  • table_name: It is the name of a new table that you want to create.
  • column_name1, column_nam2: The columns of the table that represent the attribute.
  • datatype1, datatype2: The data types for each column in a table. These data types define what kind of data each column is going to store such as integer, varchar, etc.
  • constraint: You can also specify constraints on each column such as PRIMARY KEY, NOT NULL, FOREIGN KEY, etc.

Using the above syntax you can create a table in SQL Server.

For example, you have access to the retail business database and are given a task to create a table that should store the customer information.

So create a table named Customers with columns CustomerID, CustomerName, Email, and Phone as shown below.

CREATE TABLE Customers(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(40),
Email VARCHAR(60),
Phone VARCHAR(15)
);

In the above query, define a table Customers with columns CustomerID of type INT, CustomerName, Email, and Phone of type VARCHAR. Also, CustomerID is the PRIMARY KEY column so that each customer can have unique values.

Write the above in the query editor, as shown below.

To execute the query, which will create a table of Customers, click on the Execute button as shown in the below picture.

Create Table in SQL Server Management Studio Using Query Execution

At the bottom of the query editor, you can see the message ‘Commands completed successfully’ which means table Customers is created successfully.

To view the created table, expand the Databases node from the Object Explorer area. Then, expand the System Databases, which contain all the system databases on the SQL Server.

Create Table in SQL Server Management Studio Viewing Created Table

After that, within the System Databases, there are different databases related to the system, so expand the master database, and then, within the master database, expand the folder Tables.

Under the Tables folder, dbo.Customers is the table that you have created, as you can see in the above picture. Remember, by default, the table that you create is always stored in the master database of System Databases in SQL Server.

You can also view the table constraints, keys, and indexes by expanding the folders Constraints, Keys, and Indexes of Tables. This is how you can create a table in SQL Server Management Studio using query.

Conclusion

In this SQL Server tutorial, you learned how to create a table in SSMS using query. Then, we discussed the concept of a table. After that, you learned about the table syntax. In the end, you created a table for Customers and viewed the table under the Databases node in the SQL Server.

You may also like: