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
0 comments:
Post a Comment