T-SQL

Question

Post reply

T-SQL

Sagari

Say Hey Kid

Points: 702
More actions
October 10, 2012 at 8:59 pm

#282999

Comments posted to this topic are about the item T-SQL
October 11, 2012 at 2:38 am

This was removed by the editor as SPAM

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Nice simple question on the basics. Every one should get this right (I hope)

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Whoopps clicked too often ...

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Lokesh Vij SSChampion Points: 10836 More actions · Answer 3

Very simple and easy question 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

bitbucket-25253 (10/10/2012)
Nice simple question on the basics. Every one should get this right (I hope)

I was hoping this too....Not every one got it correct!

Correct answers: 75% (9)

Incorrect answers: 25% (3)

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

demonfox SSCertifiable Points: 6289 More actions · Answer 5

Lokesh Vij (10/10/2012)
bitbucket-25253 (10/10/2012)
Nice simple question on the basics. Every one should get this right (I hope)
I was hoping this too....Not every one got it correct!
Correct answers: 75% (9)
Incorrect answers: 25% (3)

The % of correct should be increased 😀

nice and easy ..

thanks for the question

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

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

Easy question for the Thursday...

+1 :-):-P

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

Divine Flame SSCoach Points: 15943 More actions · Answer 7

Divine Flame

SSCoach

Points: 15943

October 11, 2012 at 2:29 am

#1547940

Good One!

Sujeet Singh

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 8

palotaiarpad

SSCertifiable

Points: 5617

October 11, 2012 at 2:32 am

#1547943

Good and easy!

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

demonfox (10/10/2012)
Lokesh Vij (10/10/2012)
bitbucket-25253 (10/10/2012)
Nice simple question on the basics. Every one should get this right (I hope)
I was hoping this too....Not every one got it correct!
Correct answers: 75% (9)
Incorrect answers: 25% (3)
The % of correct should be increased 😀

It should be ... but it isn't!

As of now: ...

Correct answers: 75% (152)

Incorrect answers: 25% (52)

Total attempts: 204

I am absolutely stunned that so many people get this wrong!

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/

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Hugo Kornelis (10/11/2012)
demonfox (10/10/2012)
Lokesh Vij (10/10/2012)
bitbucket-25253 (10/10/2012)
Nice simple question on the basics. Every one should get this right (I hope)
I was hoping this too....Not every one got it correct!
Correct answers: 75% (9)
Incorrect answers: 25% (3)
The % of correct should be increased 😀
It should be ... but it isn't!
As of now: ...
Correct answers: 75% (152)
Incorrect answers: 25% (52)
Total attempts: 204
I am absolutely stunned that so many people get this wrong!

And it's actually getting worse:

Correct answers: 74% (177)

Incorrect answers: 26% (63)

It's amazing that so many people can get this wrong. Like Hugo, I'm stunned.

Even if someone doesn't know the answer they could use just a little thinking and application of common sense to get the correct answer, because the consequences for concurrency and performance of requiring the seed to be rolled back would be intolerable.

Tom

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

L' Eomot Inversé (10/11/2012)
the consequences for concurrency and performance of requiring the seed to be rolled back would be intolerable.

That's not even the biggest issue. Consider this scenario:

Transaction 1 starts, inserts new row. Identity value used = 12345.

Transaction 2 starts, inserts new row. Identity value used = 12346.

Transaction 1 rolls back.

Transaction 2 inserts two more rows. What identity values to expect?

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 12

Great question. Thanks for posting. The question should be easy, but apparently isn't for some.

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

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Hugo Kornelis (10/11/2012)
L' Eomot Inversé (10/11/2012)
the consequences for concurrency and performance of requiring the seed to be rolled back would be intolerable.
That's not even the biggest issue.

It's big enough to kill the idea of restoring seeds, though.

Consider this scenario:
Transaction 1 starts, inserts new row. Identity value used = 12345.
Transaction 2 starts, inserts new row. Identity value used = 12346.
Transaction 1 rolls back.
Transaction 2 inserts two more rows. What identity values to expect?

That's an illustration of why we would have to do something quite unpleasant to resolve the issues with restoring the seed. Actually, it's an overcomplicated illustration: no need to consider further work in Transaction 2, it's bad enough if it just commits its single update. I don't believe there's a reasonable solution. There are at least two unreasonable solutions (which I'm sure would never be implemented):

(i) Transaction 2 is blocked when it attempts to insert, and remains blocked until Transaction 1 commits or rolls back;

(ii) Transaction 2 is aborted immediately when transaction 1 rolls back; If Transaction 1 doesn't roll back, Transaction 2 is not committed until Transaction 1 commits; if Transaction 2 tries to commit it blocks, waiting for transaction 1.; if Transaction 1 rolls back Transaction 2 is rolled back.

In either case, we don't need an answer to your question - with (i) because Transaction 2's insert doesn't happen until after Transaction 1 commits or rolls back, with (ii) because Transaction 2 never gets to its second insert (it rolls back when Transaction 1 rolls back).

Of course we would have to do some things with dbcc checkident too.

(i) would be reasonably simple to implement (although a lock that blocks inserts but not updates would be new), (ii) rather less so (one can end up with an arbitrarily large group of transactions all of which must commit or roll back together).

The consequences of implementing either (i) or (ii) in order to allow the seed to be restored are what I consider intolerable.

Fortunately we don't have any of this nonsense - the seed is not part of the state that has to be restored by rollback.

Tom