In this tutorial, I am going to explain you about how to use Create Table Statement for creating Database Table in SQL Server.
After completing this tutorial you will be able to understand:
- How to create a table in SQL Server.
- Create Table if not Exists in SQL Server.
A Table in a Relational Database Management System Organise the data into rows and columns. In SQL Server Create Table Statement is used to create a table.
The Syntax for Creating a Table in Database:
CREATE TABLE Table_Name ( Column1 Datatype [NULL | NOT NULL], Column2 Datatype [NULL | NOT NULL], )
- Table_Name specifies the name of the table.
- Column1 and Column2 specify the name of the column that we want to create. Each column must have a datatype.
- NULL | NOT NULL specifies that the column would hold null values or not.
The example of Create Table Statement:
Let’s take a look at how to use Create Table Statement for creating a Table in SQL Server.
Create Table Employee ( EmployeeID INT NOT NULL, EmployeeName VARCHAR(100) NOT NULL, EmailID VARCHAR(150) NULL, Salary Money NOT NULL, Joining DATETIME NOT NULL )
After running this script you can find your table after expanding the database in object explorer as in below snapshot.
The following Create Table script will create a table called Employee with five columns.
- The First Column called EmployeeID of datatype INT cannot contain NULL values.
- The second column called EmployeeName of datatype varchar and size 100 and can’t store NULL values.
- The third column called EmailID of datatype varchar and size 150 and can contain NULL Values.
- The fourth column called Salary will store Salary of the Employee of datatype Money and can’t store NULL values.
- The fifth column called JoiningDate will store the JoiningDate of the employee. The JoininDate is of datatype DateTime and can’t store NULL values.
Create Table if Not Exists in the database.
If you will try to create a table in the database that already exists then you will get an error message. To prevent this error you can use following IF NOT EXISTS to create a table.
The example of Creating Table that does not exist in the database.
Create Table IF NOT EXISTS Employee ( EmployeeID INT NOT NULL, EmployeeName VARCHAR(100) NOT NULL, EmailID VARCHAR(150) NULL, Salary Money NOT NULL, Joining DATETIME NOT NULL )
Alter Table in SQL Server:
Suppose you want to add one more column Address to the Employee Table. You can do this using Alter Statement.
ALTER TABLE Employee Add Address VARCHAR(100)
Hope you understand this tutorial about Create Table Statement in SQL Server. We will get more stuff on SQL Server in later tutorials.