How to create a table in SQL Server

Overview:

What is a table in relational database?

This is a very common question that every developer ask when they work with any SQL database management system. Let's understand what it is and how to create a table in SQL Server database.

Definition:
Tables are the database object which store the data in the relational database management system. Tables are very essential component of database to hold the data, display the data in different ways. Normally, tables consist of rows and columns. Columns define the name of the column and row define the actual data to be store in database.

Here, we will learn the concept of SQL tables and then work on how we can create tables with different techniques in Microsoft SQL Server with the help of SQL Management Studio interface.

A database contains one or more tables and these tables can be modeled as relational. The tables come into existence from the columns and every column must have a name and a data type. These columns store data according to the defined data types and these data records are called rows. Let's have a look on following illustration which show the structure of tables in database with columns and rows.

In above illustration, we can see Customer table with Id, FirstName, LastName, Address, etc... are columns which hold the different type of the data in each rows with data type defined for each columns.

Now, we will understand how to create a table with different options. Here, we will use SQL Server database, a product from Microsoft and SQL Management Studio interface.

1. Create Using Script (T-SQL)

In this technique, "CREATE TABLE" statement is used to create a new table in SQL Server. Let's understand the syntax and example to create table.

Syntax:

In above syntax, we have to define schema, table name, columns and data type for each column. Additionally, we have also one column with primary key which define unique records for each rows in table.

Example:

In above example, we have created a table Customer which hold the details for customers. We have defined different columns like Id, FirstName, LastName, Address,... and so on with primary key on Id column.

2. Using SQL Server Management Interface

One of the easiest method to create a table in SQL Server. We need to use SQL Management Studio interface to create a table. Lets follow a few steps to create a table in SQL Server database.

Step:1 Connect SQL Management Studio software with Window or SQL Server authentication based on following screen.

Step:2 Choose database from right panel, select Tables, right click on it and choose New -> Table as seen in below screen.

Step:3 Here, it will appear an interface where you can define different columns, data type for each columns, set primary key on Id column, set the size for each columns to hold data and provide a meaningful name for the table "Customers".

Once you setup all required columns and save it, it will be shown as the following screen.

3. Create from existing table

In this technique, We should be able to create a new table from the existing table. We can use "SELECT INTO" statement to create a new table and same time it will inserts the set of result using the SELECT query to the new table.

If you want to insert a specific records from the given table, you can write a WHERE condition at the end of the statement and it will insert the records based on your specific criteria.

Now, in this article, we have learned how to create a table in SQL Server database with the help of different techniques used to create tables. We can use any method from above technique which is easiest and convenient for us.