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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: