SQL CREATE TABLE statement is used to create a new table in a database
CREATE TABLE [Table_Name] ( Column_name1 Data_Type(Size) [NULL | NOT NULL], Column_name2 Data_Type(Size) [NULL | NOT NULL], … Column_N Data_Type(Size) [NULL | NOT NULL] );
Table Name is a name for table.
Column_Name1, Column_Name2, Column_NameN is columns that you want for table.
Data Type(Size) is a valid SQL Server Data Types with expected size of values that the column will hold.
NULL | NOT NULL It is an optional, If NULL option is specified then column will accept both normal values and NULL values. If NOT NULL options is specified for column then value for the column should not be empty.
Following SQL Create Table statement creates a new table named as Emp_Info in database .
CREATE TABLE Emp_Info (
EmpId INT NOT NULL,
EmpName VARCHAR(50) NOT NULL,
Code CHAR(10) NOT NULL,
Email VARCHAR(100) NULL
As you can see below screenshot, create table command executed successfully.
You can check whether a table is created or not , using Select Statement .
As you can in below screenshot, It returns blank data which means employee table is created in database.
SELECT * FROM Emp_Info
You can also see check the table structure such as all columns name of table their Data type, and size and other valuable information using a SQL Server inbuilt system stored procedure SP_HELP
Lets execute the SP passing a table name as parameter.
EXEC SP_HELP Emp_Info
You can see, it returns complete details of table interms of columns that table contains, column data types and size, table created date and other details.
You can also check the table in Object Explorer, Go to SQL Server Object Explorer then click on Databases folder.
Next, expand the Database under you created a table, then expand the table folder under that you will see a table that you created.
If you want to see table columns then expand the table, you will see all the columns.
Create a primary key on table column
Lets create a new table called TranHistory, In this table we will create a Primary key on column TranId.
CREATE TABLE TranHistory (
TranId INT PRIMARY KEY NOT NULL,
TransactionDesc VARCHAR(500) NOT NULL,
TranCode CHAR(10) NOT NULL,
TranDate DateTime NOT NULL
You can see, command compelete successfully.
Lets verify the table in Object Explorer. If you do not see table even after successful execution of create table statement then just refresh the Table folder and recheck.
And you can see, the table is there also a primary key is created on column TranId.
You can also check same using SP_HELP
As you can see, it returns table information also primary key created on column TranID details can be seen as shown in below screenshot.
Create a constraints while creating a new table
CREATE TABLE TranLog
TranID INT PRIMARY KEY NOT NULL,
TranCode Char (10) NOT NULL UNIQUE,
TranDesc VARCHAR(100) NULL,
TranAmount NUMERIC(9,2) CHECK (TranAmount >100),
TranDate DATETIME NOT NULL DEFAULT GETDATE()
Lets check the table in Obeject Explorer, and you can see table is created successfully also you can see all the constraints under keys and Constraints folders as shown below.
Creating a Foreign Key Constraint on table
For creating a foreign key constraint you can refere post – SQL Server Create Foregin Key on table
969 total views, 3 views today