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)