Hi,
I was not aware of the fact that after making
SET IDENTITY_INSERT dbo.Test OFF;
identity value gets incremented. I am confused now as what is the purpose of making Identity_Insert OFF then?
Lets check this scenario:
--Scenario 1
TRUNCATE TABLE dbo.test --- All data gets removed now.
SELECT IDENT_CURRENT('test') -- return 1
SET IDENTITY_INSERT dbo.Test OFF;
INSERT INTO dbo.Test (Col_2) VALUES (1); -- 1 rows get affected
INSERT INTO dbo.Test (Col_2) VALUES (2); -- 1 rows get affected
INSERT INTO dbo.Test (Col_2) VALUES (3); -- 1 rows get affected
Select * from dbo.test (Now it will return 3 rows)
--Scenario 2
TRUNCATE TABLE dbo.test --- All data gets removed now.
SELECT IDENT_CURRENT('test') -- return 1
SET IDENTITY_INSERT dbo.Test OFF;
INSERT INTO dbo.Test (Col_2) VALUES (2); -- Error return
INSERT INTO dbo.Test (Col_2) VALUES (2); -- 1 rows get affected
INSERT INTO dbo.Test (Col_2) VALUES (3); -- 1 rows get affected
Select * from dbo.test (Now it will return 2 rows)
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/