Create Table in SQL Server Examples

Description

In this article, I am going to write T-SQL query to Create Table in MS SQL Server in different methods.

Summary

Sql Query to Create Table in SQL Server

Use [MS_SQLServer_DB]
GO
CREATE TABLE [TestSQLTable](
ID int NOT NULL,
UserName nvarchar(100) NOT NULL,
MailID nvarchar(100) NULL)

Create Table with Identity/Auto Increment column in MS SQL Server

Use [MS_SQLServer_DB]
GO
CREATE TABLE [TestSQLTable](
ID int IDENTITY NOT NULL,
UserName nvarchar(100) NOT NULL,
MailID nvarchar(100) NULL)

Create Table with Primary Key in MS SQL Server

Use [MS_SQLServer_DB]
GO
CREATE TABLE [TestSQLTable](
ID int PRIMARY KEY NOT NULL,
UserName nvarchar(100) NOT NULL,
MailID nvarchar(100) NULL)

T-Sql Query to Create Table with Primary Key and Identity Column in SQL Server

Use [MS_SQLServer_DB]
GO
CREATE TABLE [TestSQLTable](
ID int IDENTITY PRIMARY KEY NOT NULL,
UserName nvarchar(100) NOT NULL,
MailID nvarchar(100) NULL)

Create Table with Primary Key and Custom Primary Key Name in SQL Server

Use [MS_SQLServer_DB]
GO
CREATE TABLE [TestSQLTable](
ID int NOT NULL,
UserName nvarchar(100) NOT NULL,
MailID nvarchar(100) NULL,
CONSTRAINT [PK_ID_AATable] PRIMARY KEY CLUSTERED(ID))

Create Table with Date Time column in SQL Server

Use [MS_SQLServer_DB]
GO
CREATE TABLE [TestSQLTable](
ID int NOT NULL,
UserName nvarchar(100) NOT NULL,
LoginTime datetime NULL)

Create Table with Foreign Key in SQL Server

--Relative Table(Foreign Key Table)

CREATE TABLE [RTable](
CityID int PRIMARY KEY NOT NULL,
CityName nvarchar(1000) NOT NULL)

--Primary Key Table

CREATE TABLE [PTable](
ID int IDENTITY PRIMARY KEY NOT NULL,
UserName nvarchar(100) NOT NULL,
MailID nvarchar(100) NULL,
City int NULL,
FOREIGN KEY (City) REFERENCES [RTable] (CityID))


Create Table with Sparse Column in SQL Server

The Sparse column feature exists only from SQL Server 2008 and later versions.

Use [MS_SQLServer_DB]
GO

CREATE TABLE [TestSQLTable](
ID int IDENTITY PRIMARY KEY NOT NULL,
UserName nvarchar(100) NOT NULL,
MailID nvarchar(100) SPARSE NULL,SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS)

Create Table with NonClustered Index Column in SQL Server

Use [MS_SQLServer_DB]
GO

CREATE TABLE [TestSQLTable](
ID int IDENTITY PRIMARY KEY NOT NULL,
UserName nvarchar(100) NOT NULL,
JoinDate datetime NOT NULL,
MailID nvarchar(100) NULL)

CREATE NonClustered  Index Index_JoinDate
ON [TestSQLTable] (JoinDate)

Create Table with Unique Index Column in SQL Server

Use [MS_SQLServer_DB]
GO

CREATE TABLE [TestSQLTable](
ID int IDENTITY PRIMARY KEY NOT NULL,
UserName nvarchar(100) NOT NULL,
JoinDate datetime NOT NULL,
MailID nvarchar(100) NULL)

CREATE Unique INDEX Index_JoinDate
ON [TestSQLTable] (JoinDate)

Thanks,
Morgan
Software Developer

Advertisement

Leave a Comment