nenad-zivkovic (4/24/2013)

---

kapil_kk (4/23/2013)

---

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?

When IDENTITY_INSERT is set to OFF, values for identity columns gets incremented automatically, that is a default setting. That line makes no difference in the query - it's only purpose was to make sure it's set to OFF and remove possible confusion whenever IDENTITY_INSERT was ON or OFF. If IDENTITY_INSERT was set to ON, all inserts would fail for not providing value for Col_1.

I apologize If it brought additional confusion instead. It was not meant in that way.

Now, that I think of it, it is kind of a additional test to see if people now what ON and OFF values for IDENTITY_INSERT mean, which is not that bad.

Best Regards

-Nenad