I want to share one of my experiences about IDENTITY column. When I was very small kid in SQL Server few years back and I used to do programming in PHP with MySQL back end. I have just started learning SQL Server at that time one thought pops up in my mind. There is no way to insert value in IDENTITY column (that’s what I was thinking at that time after reading IDENTITY column from book) than what happens to the table I have with just one column and that column is IDENTITY enable column?

I tried to search out the way and finally found it and got very happy at that time. I didn’t even think that I would write article on SQL Server and especially on this experience.

--CREATE DEMO TABLE

CREATE TABLE IdentityColumn

(

ID INT IDENTITY(1,1),

NAME VARCHAR(10)

)

GO

--TRYING TO INSERT VALUE

INSERT INTO IdentityColumn VALUES(1,'Ritesh')

GO

--above statement will show you below given error

--Msg 8101, Level 16, State 1, Line 1

--An explicit value for the identity column in table 'IdentityOneColumn' can only be specified

--when a column list is used and IDENTITY_INSERT is ON.

--SET IDENTITY_INSERT to ON so that we can enter value manually in IDENTITY column

SET IDENTITY_INSERT IdentityColumn ON

--INSERT record in IDENTITY column with column list in INSERT

INSERT INTO IdentityColumn(id,name) VALUES(1,'Ritesh')

--once you done, set value OFF to IDENTITY_INSERT

SET IDENTITY_INSERT IdentityColumn OFF

GO

--check the table

SELECT * FROM IdentityColumn

--again start inserting regularly

INSERT INTO IdentityColumn(name) VALUES('RAJAN')

--AGAIN check the table

SELECT * FROM IdentityColumn

--one more time manual insert

SET IDENTITY_INSERT IdentityColumn ON

INSERT INTO IdentityColumn(id,name) VALUES(5,'Alka')

SET IDENTITY_INSERT IdentityColumn OFF

GO

--AGAIN check the table

SELECT * FROM IdentityColumn

--again start inserting regularly and check table

INSERT INTO IdentityColumn(name) VALUES('RAvi')

SELECT * FROM IdentityColumn

After observing above behavior, you come to know that, if you insert big value manually in IDENTITY column, next regular value will follow you value.

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

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