SQL CREATE TABLE

SQL CREATE TABLE statement is used to create a new table in a database

SYNTAX

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 in terms 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 NU

L,
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

Lets create a Primary Key Constraint, Default Constraint, Check Constriant, and Unique Key Constraint on table named as TranLog.

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 Object 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 refer post – SQL Server Create Foregin Key on table

Also Read..

SQL Server Create Schema