Facts about IDENTITY in SQL Server 2005

IDENTITY is a property and it can be used with Table object in Microsoft SQL Server. Basically it uses to maintain unique auto increment number for table.

Syntax of IDENTITY is:

IDENTITY [(seed,increment)]

SEED is a numeric value with which you want to load your first row in the table.

INCREMENT is a numeric value to identify the gap between two values of Identity column.

I have used IDENTITY column in my many past article but let us see this practically here once again.

NOTE: You must not insert value in identity column, SQL Server automatically find the value for it and INSERT it.

--CREATE demo table

CREATE TABLE IdentityCheck

(

ID INT IDENTITY(2,2),

Name VARCHAR(10)

)

--insert record in table with Identity

INSERT INTO IdentityCheck VALUES('Ritesh')

INSERT INTO IdentityCheck VALUES('Rajan')

--check what have been inserted

SELECT * FROM IdentityCheck

In IdentityCheck table we have set seed as 2, it means first row in our table will get value 2 in ID column and we have set increment as 2 so it means that every new row will add 2 in the previous row by this fact you can assume that our second row will insert value 4 in ID column.

If you delete rows from table and then will start inserting rows in table again, it won’t start ID value from 2, in our case, it will start from 6 because if you delete records, it will not reset your IDENTITY but yes if you TRUNCATE the table, IDENTITY will reset.

--check whether identity reset by deleting records.

DELETE FROM IdentityCheck

--let us insert records again.

INSERT INTO IdentityCheck VALUES('Ritesh')

INSERT INTO IdentityCheck VALUES('Rajan')

--let us check what value we got this time in ID column

--those will be 6 and 8

SELECT * FROM IdentityCheck

--let us now truncate table which will reset identity

TRUNCATE TABLE IdentityCheck

--let us insert records again.

INSERT INTO IdentityCheck VALUES('Ritesh')

INSERT INTO IdentityCheck VALUES('Rajan')

--let us check what value we got this time in ID column

--those will be 2 and 4

SELECT * FROM IdentityCheck

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com