AUTO INCREMENT or IDENTITY in SQL

 

AUTO INCREMENT or IDENTITY

Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.

Example:

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

As shown below, we use the IDENTITY() for this. IDENTITY(1,1) means the first value will be 1 and then it will increment by 1. 

Setting identity(1,1) in the Designer Tools: 

We can use the designer tools to specify that a Primary Key should be an identity column that is automatically generated by the system when we insert data in to the table.

Click on the column in the designer and go into the Column Properties window: