How to Add identity Column to Table by TSQL and GUI in SQL Server - SQL Server / T-SQL Tutorial Part 40

Scenario:

You are working as SQL Server Developer and you have created a dbo.Customer table by using below Script.

CREATE TABLE Customer (
    FirstName VARCHAR(50)
    ,LastName VARCHAR(50)
    ,Age SMALLINT
    ,PhoneNumber CHAR(9)
    ,DOB DATE
    ,Gender CHAR(1)
    )

You need to add a column CustomerId that should start from 1 and every time new row inserts into table, it should increment by 1.

Solution:

SQL Server provide us the facility to add column like that. It is call Identity. You can provide the initial value(seed) and provide Increment value. 

Let's say if we would like to Add CustomerId to Customer Table with Starting Value=1 and Incremental value=1, Our script will be like below

CREATE TABLE Customer (
    CustomerId INT Identity(1, 1)
    ,FirstName VARCHAR(50)
    ,LastName VARCHAR(50)
    ,Age SMALLINT
    ,PhoneNumber CHAR(9)
    ,DOB DATE
    ,Gender CHAR(1)
    )

Add Identity from Design Windows:

Let's say if you are creating table by using Design windows, you can add Identity from there as well as shown below.

Go to Database, Go to Tables and then use Right Mouse click and hit New. A window will open and you have to provide Column Name, Data Types and If Column allows Null or not. 

Once done, then click on CustomerId column that we want to make identity column. In Column Properties windows, Enable Identity and provide the Seed and Incremental value as shown below.

Once all done. hit save or Generate Change Script to run on any SQL Server Instances later.

How to Add Identity Column to Table in SQL Server - SQL Server / T-SQL Tutorial

Video Demo :  How to create table with Identity Column in SQL Server