How to create a table in SQL Server

Overview:

In SQL Server, tables are used to store data in the database. Table consists of columns, data type and constraints which store the data in different format based on data type defined for each column. Table name must be unique and never be duplicated in database. Data type define for each columns to store what type of data we can store like TEXT, NUMBERS, DECIMAL, DATETIME, BOOLEAN etc...

In this tutorial, we will learn how to create a table in SQL Server. We will use same database “OnlineStore” which we have created in earlier tutorial how to create a database in SQL Server. There are two ways to create a new table in SQL Server.

  • Create a table using SQL Statement(T-SQL) "CREATE"
  • Create a table using SQL Management Studio

Create a table using SQL Statement:

By using SQL statement, we will write some sql statements which help us to CREATE A NEW TABLE.

CREATE TABLE [dbo].[Table_Name](
[ColumnName_1] [DataType] IDENTITY(1,1) PRIMARY KEY,
[ColumnName_2] [DataType](Size) NULL,
[ColumnName_3] [DataType](Size) NULL,
---,
---,
[ColumnName_N] [DataType](Size) NULL);

Let’s create a Customer table as example with some basic columns which will store customer details in DATABASE.

CREATE TABLE dbo.Customers (
[Id] [bigint] IDENTITY(1,1) PRIMARY KEY,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Address] [nvarchar](100) NULL,
[City] [nvarchar](30) NULL,
[Zipcode] [nvarchar](6) NULL,
[State] [nvarchar](30) NULL,
[Country] [nvarchar](50) NULL, [MobileNumber] [nvarchar](15) NULL,
[EmailAddress] [nvarchar](100) NULL,
[Birthdate] [date] NULL,
[Active] [bit] NULL,
[CreatedOn] [date] NULL);

Above SQL statement will CREATE A TABLE in SQL Server under “OnlineStore” database which will store some basic details for customers. This table will store basic details for customer like FIRST NAME, LAST NAME and contact details like ADDRESS, EMAIL ADDRESS and PHONE NUMBER. We have also taken few more fields with different datatype like DATE, BOOLEAN, INT etc...

Primary Key: PRIMARY KEY is used to identify each record with unique numbers. In customer table, we have set primary key with Id INT field and also set Identity true which will start records by 1 and every time it will increase by one when new record will be added into the table. We will learn more about what is primary key in SQL server in coming tutorial.

We will create few more tables in “OnlineStore” database in coming tutorial “sample database in sql server”.

Create a table using interface

Now, SQL Server Management Studio also provides a way to CREATE A TABLE via interface which we will learn with following steps.

Step-1: Open SQL Server Management Studio with windows authentication or SQL Server authentication mode.

create a table in sql server database

Step-2: Expand left side Database Engine panel -> Expand “OnlineStore” database

create a table in sql server database

Step-3: Expand “Tables” and right click on it, then choose New -> Table... as shown in below screen and it will open interface to create a new table as shown in step 4.

create a table in sql server database

Step-4: Following screen will open to create a new table in SQL Server database.

create a table in sql server database

As you can see in above screen, we have created same Customers table with all required fields with different data type as needed.

Now you should be familiar about how to create a table in SQL Server database to store the data and also familiar with fields and data types in table.