CREATE TABLE in SQL

CREATE TABLE

Before you start implementing your tables in the database, you should always spend some time design your tables properly using a design tool like, e.g., ERwin, Toad Data Modeler, PowerDesigner, Visio, etc. This is called Database Modeling.

The CREATE TABLE statement is used to create a table in a database. 

Syntax:

CREATE TABLE table_name

( 

column_name1 data_type,

column_name2 data_type,

column_name3 data_type, 

....

) 

The data type specifies what type of data the column can hold.

You have special data types for numbers, text dates, etc.

 Examples:

• Numbers: int, float 
• Text/Stings: varchar(X) – where X is the length of the string 
• Dates: datetime etc.

Example: We want to create a table called “CUSTOMER” which has the following columns and data types:

Example Code:

CREATE TABLE CUSTOMER 

( 

CustomerId int IDENTITY(1,1) PRIMARY KEY, 

CustomerNumber int NOT NULL UNIQUE, 

LastName varchar(50) NOT NULL, 

FirstName varchar(50) NOT NULL, 

AreaCode int NULL, 

Address varchar(50) NULL, 

Phone varchar(50) NULL,

)

GO

 

 

Best practice:

• When creating tables you should consider following these guidelines: 
• Tables: Use upper case and singular form in table names – not plural, e.g., “STUDENT” (not students) Columns: Use Pascal notation, e.g., “StudentId” 
• Primary Key: 

1. If the table name is “COURSE”, name the Primary Key column “CourseId”, etc. 
2. “Always” use Integer and Identity(1,1) for Primary Keys. Use UNIQUE constraint for other columns that needs to be unique, e.g. RoomNumber 

• Specify Required Columns (NOT NULL) – i.e., which columns that need to have data or not
• Standardize on few/these Data Types: int, float, varchar(x), datetime, bit
• Use English for table and column names
• Avoid abbreviations! (Use RoomNumber – not RoomNo, RoomNr, ...)