SQL Server uses Tables to store and Manage Data. SQL Table is a combination of Rows and Columns. In order to create table in SQL Server, we have to use SQL Create Table Statement
SQL Create Table Syntax
The basic syntax to create table in SQL Server is as shown below:
-- SQL Server Create Table Syntax CREATE TABLE [Table Name] ( Column_Name1 Data_Type(Size) [NULL | NOT NULL], Column_Name2 Data_Type(Size) [NULL | NOT NULL], … Column_NameN Data_Type(Size) [NULL | NOT NULL] );
Let us see the individual items from the SQL create table syntax:
- Table Name: Please provide Unique table name here. If you write the already existing table name, it will throw an error
- Column_Name: Please specify the Unique Column Names required for this table
- Data Type: Please specify the valid Data type that the column will hold. For example, Int, Money, Varchar, Nvarchar and Date
- Size: Data types like Varchar, nvarchar, Char expects the size So, Please provide valid number here
- NULL or NOT NULL: If you select the NULL option then column will accept both normal values and NULL values otherwise, it will throw an error saying Column should not be empty
SQL Create Table Example
We are going to use the below-shown code to create a new table in Sql Server called Customer inside the [SQL Tutorial] Database
-- SQL Server Create Table Example USE [SQL Tutorial] GO CREATE TABLE [Customer] ( [CustomerKey] [int] NOT NULL, [Name] [varchar](150) NULL, [DateOfBirth] [date] NULL, [EmailAddress] [nvarchar](50) NULL, [Profession] [nvarchar](100) NULL ) GO
NOTE: Before you start creating a TABLE, It is always advisable to check if a Table exists or not.
From the above Sql Server Create Table code you can observe that We declared 5 Columns:
- Our first column is CustomerKey of Integer data type and it will not allow NULL values.
- Name column belongs to Varchar data type and it allows NULL values. We also assigned the size to 150, it means both the columns will accept up to 150 characters
- The third column is DateOfBirth of Date data type and allows NULLs. This will allow us to enter Date values only
- EmailAddress and Profession columns belong to NVarchar data type and it will allow NULL values.
From the below screenshot you can observe that Command is executed successfully and you can see the Newly created table in Sql Server object explorer.
Let me show you the newly created table in Sql Server Object Explorer. Please expand the Columns folder to see the available columns
TIP: If you didn’t find the newly created table in Sql Server, Please click on the refresh button in object explorer
SQL Create Table with Identity Column
In this example, we defined the Customer Key column as an Identity column. This will auto-generate numbers starting with 1 and incremented by 1. (This is optional If you want You can remove IDENTITY (1, 1) portion completely)
-- SQL Server Create Table Example CREATE TABLE [Customer11] ( [CustomerKey] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) NULL, [LastName] [varchar](50) NULL, [BirthDate] [date] NULL, [EmailAddress] [nvarchar](50) NULL, [Yearly Income] [money] NULL, [Profession] [nvarchar](100) NULL ) GO
OUTPUT
From the below screenshot you can see the newly created table in Sql Server
SQL Create Table with Primary Key
In this Sql Server create table example, we will show you, How to create a Table with a Primary Key column. I suggest you refer to a Primary Key article to understand this constraint.
As you can see from the below code snippet, we just added the PRIMARY KEY word to the Customer key. This will create a primary key on Customer key column.
-- SQL Server Create Table Example CREATE TABLE [Customer111] ( [ID] [int] IDENTITY(1,1) NOT NULL, [CustomerKey] [int] NOT NULL PRIMARY KEY, [FirstName] [varchar](50) NOT NULL , [LastName] [varchar](50) NULL, [BirthDate] [date] NULL, [EmailAddress] [nvarchar](50) NULL, [Yearly Income] [money] NULL, [Profession] [nvarchar](100) NULL ) GO
OUTPUT
SQL Create Table with All Constraints
In this sql create table example, we will show you, How to create Sql Server Table with Identity Column, Primary Key column, Unique Key, and Check Constraint. I suggest you refer to Unique Key, Default Constraint, and Check Constraint articles to understand these constraints.
As you can see from the below Sql create table code snippet,
- The last Name will accept Unique values.
- If the user doesn’t provide a value to the Profession column then SQL writes Software Developer as the default value
- Check constraint check whether the Age value is between 0 and 100
-- SQL Server Create Table Example CREATE TABLE [Customer11111] ( [ID] [int] IDENTITY(1,1) NOT NULL, [CustomerKey] [int] NOT NULL PRIMARY KEY, [FirstName] [varchar](50) NOT NULL , [LastName] [varchar](50) NOT NULL UNIQUE, [Age] [int] NULL, [EmailAddress] [nvarchar](50) NULL, [Yearly Income] [money] NULL, [Profession] [nvarchar](100) NOT NULL DEFAULT ('Software Developer'), CONSTRAINT CK_Customer11111_Age CHECK([Age] > 0 AND [Age] <= 100) ) GO
OUTPUT
If you expand the table definition in the Object Explorer, you can see all these constraints
SQL Create Table using Another Table
The SQL Server provides Select into Statement to create a table using an existing table along with data (if any).
Data that we are going to use
Below query create new Sql Server table Customer1111111 using Employee table definition, and insert all the records present in the Employee
-- SQL Server Create Table Example SELECT [EmpID], [FirstName], [LastName], [Education] ,[Occupation], [YearlyIncome], [Sales], [HireDate] INTO [SQL Tutorial].[dbo].[Customer1111111] FROM [SQL Tutorial].[dbo].[Employee]
OUTPUT
Let me show you the data inside this table
SELECT [EmpID], [FirstName], [LastName], [Education] ,[Occupation], [YearlyIncome], [Sales], [HireDate] FROM [SQL Tutorial].[dbo].[Customer1111111]
OUTPUT
SQL Create Table in Management Studio
In order to Create Sql Server Table, within the Management Studio object explorer, Expand the Database folder in which you want to create table in Sql Server. Please select the Tables folder and Right click on it will open the context menu.
Select the New option from the context menu and then select the Table.. option as shown below
Once you select the Table.. option, the following window will be opened to type the Column Name, Data Type and Checkbox to decide whether the column allows NULL values or Not
From the below screenshot you can observe that We added 7 columns of different data types
Add Identity Column to SQL Table using Management Studio
First, select the column and Go to the Column Properties tab.
Please change the IsIdentity option from default No to Yes in Identity Specification property as shown below.
Once you finish create table in Sql Server, Please click on the save button to save the table. Once you click on the save button following window will be appeared to rename the table name.
Click OK to finish saving. Write the following Select statement to check whether the table holds all the column names or not.
TIP: You can use INSERT Statement, or insert into to insert data into this newly created table
Create Local and Global Temp Table in SQL Server
In this example, we will show you, how to create local and global temporary tables in SQL. Please refer Temp Tables article to understand the code.
-- SQL Create Local Temp Table CREATE TABLE #LocalTemp ( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](255) NULL, [Occupation] [nvarchar](255) NULL, [Sales] [float] NULL ) GO -- SQL Create Global Temp Table CREATE TABLE ##GlobalTemp ( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](255) NULL, [Occupation] [nvarchar](255) NULL, [YearlyIncome] [float] NULL, [Sales] [float] NULL ) GO
OUTPUT