Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs
    • SQL FAQ’s

SQL Create Table

by suresh

As we all know, SQL uses Tables to store and Manage Data. Table in Sql Server is a combination of Rows and Columns. In order to create table, we have to use SQL Create Table Statement

The basic syntax for SQL Server create table 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 Server 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
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 in Sql Server, 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.

SQL Create Table Example 1

Let me show you the newly created table in Sql Server Object Explorer. Please expand the Columns folder to see the available columns

SQL Create Table Example 2

TIP: If you didn’t find the newly created table, Please click on the refresh button in object explorer

Create Table with Identity Column

In this example, we create a table with identity column in Sql Server. For this, 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

SQL Create Table Example 3

From the below screenshot you can see the newly created table

SQL Create Table Example 4

SQL Create Table with Primary Key

In this example, we will show you, How to create Table in Sql Server with 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 Example 5

Create Table with All Constraints

In this example, we will show you, How to create Table in Sql Server 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 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

SQL Create Table Example 6

If you expand the table definition in the Object Explorer, you can see all these constraints

SQL Create Table Example 7

Sql Create Table from 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

SQL Create Table Example 8

Below query creates new 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

SQL Create Table Example 9

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 Example 10

Create Table in Management Studio

In order to Create a 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 to create Table in Sql Server

SQL Create Table Example 11

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

SQL Create Table Example 12

Add Identity Column using Management Studio

Let me show you, how to add an identity column to already created table using Sql Server Management Studio. First, select the column and Go to the Column Properties tab.

SQL Create Table Example 13

Please change the IsIdentity option from default No to Yes in Identity Specification property as shown below.

SQL Create Table Example 14

Once you finish create table, 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.

SQL Create Table Example 15

Click OK to finish saving. Write the following Select statement to check whether the table holds all the column names or not.

SQL Create Table Example 16

TIP: You can use INSERT Statement, or insert into to insert data into this newly created table

Create Local and Global Temp Table

In this example, we will show you, how to create local and global temporary tables or temp tables in Sql Server. 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

SQL Create Table Example 17

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy