FK to IDENTITY

  • Comments posted to this topic are about the item FK to IDENTITY

    _______________________________________________
    www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

  • 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/

  • Hi,

    Nice question! Bravo.

    Thank you

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • 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 know what ON and OFF values for IDENTITY_INSERT mean, which is not that bad.

    Best Regards

    -Nenad

    _______________________________________________
    www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

  • I thought the question was expressed well Nenad and had no problem with the clarification provided by the specified identity insert value - cheers.

  • Very interesting... thank you for the post. 🙂

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • 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

    No confusion at all, Very clear. Good work Nenad.

    But i don't think it's a common practice to ensure every time whether IDENTITY_INSERT is set to OFF, because it is the default behavior SQL server. Only when we need to insert explicit values to identity column we will use this SET option.

    --
    Dineshbabu
    Desire to learn new things..

  • Really good question, tricky and nice.

    Obviously the default value for IDENTITY_INSERT is OFF, only by changing it to ON, you wouldn't be able to insert any of the rows as values for column 1 should be provided. (That was the tricky part, setting to OFF does not do any)

    Also nice to note that the failed INSERT statements would affect the identity value for the table, though the row didn't end up there.

    Thanks

  • Very good, and very clever question. Well done! 😉


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I too found the question clear. Excellent question, and well posed.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Great QOTD today! Reminded that failed inserts still increase the identity. And, if you did NOT include the SET IDENTITY OFF statement, then people would have been complaining that you tried to trick them.

  • Interesting, thanks it was a good question.

  • Great question! Thank you for keeping the code clear and focused.

  • It would have been interesting to include exactly which value(s) would be inserted. If you weren't aware that the identity would be consumed, you might believe that the value 1 would successfully insert - which would give you the same, correct answer.

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • 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 know what ON and OFF values for IDENTITY_INSERT mean, which is not that bad.

    Best Regards

    -Nenad

    Hi,

    Thanks for your explanation...:-)

    I was not confused with your explanation instead I was thinking why this is happening that even after SET identity_insert OFF still values are getting incremented :w00t:

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply