FK to IDENTITY

Question

Post reply

FK to IDENTITY

nenad-zivkovic

Default port

Points: 1448
More actions
April 23, 2013 at 10:55 pm

#276818

Comments posted to this topic are about the item FK to IDENTITY
_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

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

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

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/

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

Hi,

Nice question! Bravo.

Thank you

IgorMi

Igor Micev,My blog: www.igormicev.com

nenad-zivkovic Default port Points: 1448 More actions · Answer 3

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)

call.copse SSCoach Points: 18048 More actions · Answer 4

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

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 5

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.

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 6

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..

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 7

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

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/

Thomas Abraham SSChampion Points: 10761 More actions · Answer 9

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

Victor Kirkpatrick Hall of Fame Points: 3672 More actions · Answer 10

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.

Mike Hays SSCommitted Points: 1871 More actions · Answer 11

Mike Hays

SSCommitted

Points: 1871

April 24, 2013 at 5:58 am

#1609428

Interesting, thanks it was a good question.

sestell1 SSChampion Points: 10230 More actions · Answer 12

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

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 13

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 14

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/