October 11, 2012 at 5:22 am
Looking at why folks might be getting it wrong...
... Not everyone who uses SQL Server is a developer. I can imagine a lot of accidental dbas / windows admins who haven't created a table, and are just responsible for keeping the engine running and windows clean. They come over here, 'cause they want to learn a little more.
... My understanding is the ACTUAL identity seed value doesn't change.... except in very, very specific situations. (c.f. http://msdn.microsoft.com/en-us/library/ms189834.aspx) Roll back the transaction, and the seed value on the table is probably gonna been the same seed value as before... and it's the same seed value it had as yesterday, and last week. I (I think correctly) understood what the question author intended by the question, but I certainly wouldn't give someone a hard time if they missed this question. (And if my understanding of the actual, real Identity SEED value is also flawed, someone let me know. I.e. The seed is the first value in the Identity... everything else is incremented from that seed. The value is never referred to as the seed after it's been incremented...) (And one of the most wonderful things about SQLServerCentral, and especially the QotD is that it gives us an excuse to 'sharpen the saw' for a few minutes, five times a week. 😉 For Free!)
October 11, 2012 at 5:28 am
Rich,
what do you mean by the seed value? I can't understand your post, think you must mean something very different from what I think seed means.
Tom
October 11, 2012 at 5:47 am
The seed value for an identity column is defined (usually) when the table is created, and is the first value for the Identity. Every value after that is incremented from the seed value.
(You can find the seed value from IDENT_SEED('tablename'). It doesn't normally change.)
October 11, 2012 at 5:52 am
Nice easy question. Thanks.
October 11, 2012 at 5:55 am
I have to agree with Rich. I almost got this one wrong because of the wording of the question. When I think of the Seed value, I am thinking of the initial value for the column when it is created. It could throw someone off.
October 11, 2012 at 6:01 am
That is exactly what the seed value is. The first value planted in the column to initialize it. To rollback to the seed value would be to start the values from the initial value.
October 11, 2012 at 7:00 am
First, let me reiterate, I'm not arguing the correct answer for the question. I'm responding to why might a quarter of the folks answering might be getting the question wrong. I think it's in the choice of word for 'seed'.
My interpretation is still that you can't actually change the seed value... short of dropping the identity column, and recreating. You can RESEED the value, which only changes the current identity value for the table. I began to wonder if the RESEED would roll back.
SET NOCOUNT ON
GO
if OBJECT_ID('seeds') IS NOT NULL DROP TABLE seeds
GO
create table seeds (
SeedIDINTNOT NULL IDENTITY(0,1) PRIMARY KEY,
DescrVARCHAR(25)NOT NULL )
GO
PRINT 'Initial values, new empty table'
PRINT IDENT_SEED('seeds');-- value = 0
PRINT IDENT_CURRENT('seeds');-- value = 0
insert into seeds(Descr)
values ('celery'), ('mint'), ('artichoke')
PRINT 'Added three values to the table'
PRINT IDENT_SEED('seeds');-- value = 0
PRINT IDENT_CURRENT('seeds');-- value = 2
BEGIN TRAN
insert into seeds(Descr)
values ('basil'), ('IDENTITY')
ROLLBACK TRAN;
PRINT 'Added two more values, but rolled them back'
PRINT IDENT_SEED('seeds');-- value = 0
PRINT IDENT_CURRENT('seeds');-- value = 4
PRINT 'This is what I believe the QotD is asking about.'
GO
-- Changing the seed value
DBCC CHECKIDENT('seeds',RESEED,10)
GO
PRINT 'RESEED the Identity value to 10.'
PRINT IDENT_SEED('seeds');-- value = 0
PRINT IDENT_CURRENT('seeds');-- value = 10
BEGIN TRAN
DBCC CHECKIDENT('seeds',RESEED,100)
PRINT 'Inside the transaction where we RESEED to 100'
PRINT 'We are attempting to change the seed value... '
PRINT ' ... of course we are only changing the CURRENT Identity value'
PRINT IDENT_SEED('seeds');-- value = 0
PRINT IDENT_CURRENT('seeds');-- value = 100
ROLLBACK TRAN;
GO
PRINT 'Rolled back the previous transaction'
PRINT IDENT_SEED('seeds');-- value = 0
PRINT IDENT_CURRENT('seeds');-- value = 10
PRINT ' ... and the current identity value did reset to the previous value.'
PRINT ' ... and of course the SEED value never changed.'[/CODE]
And let me say again, I don't disagree with the QotD answer, I just wanted to say that the use of the word seed may be throwing folks off.
October 11, 2012 at 7:04 am
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)
Without knowing the actual behavior beforehand, it would make sense that everything would be rolled back and the incorrect answers are actually to be expected without experience with the actual issue. I would think that most people when introduced to new computing subjects try to build consistent models of the software they use. If I didn't actually have knowledge of the actual behavior, I would have the assumption that processes wanting another identity would wait until the current one finishes (block), after all, the identity can be seen as a single resource shared by all inserters to the table.
October 11, 2012 at 7:26 am
I for one am not too surprised about the approximately 25% failure rate on the question. As earlier posters have noted, it would be easy for an occasional user of SQL to respond incorrectly (ignoring the debate on SEED vs. next value in the Identity column for now). Put yourselves in their shoes - before you knew for sure, what would you have expected given that in general "ROLLBACK backs out everything you did since a BEGIN TRAN statement" when one is first exposed to it?
Yep, we would have thought the Identity column got rolled back, too. It's not one of those things one thinks about till it is experienced.
So, it's a great question: it makes sure more veteran users remember what we already know, and less veteran users learn something new. Which is the assumed goal of the QoTD.
October 11, 2012 at 7:36 am
Good question, at least I didn't have to think too hard about this one and got it right.
Then I started reading the discussion on Seeds and Hugo and Tom's discussion on transactions and I realized I'm just hoping for the weekend. 🙂
It's been a long week for me.
October 11, 2012 at 8:04 am
Rich Weissler (10/11/2012)
The seed value for an identity column is defined (usually) when the table is created, and is the first value for the Identity. Every value after that is incremented from the seed value.(You can find the seed value from IDENT_SEED('tablename'). It doesn't normally change.)
That looks like the correct definition, but "doesn't normally change" is a gross understatement - it can't be changed (but it can be destroyed, by dropping the column). If a transaction which dropped a column is rolled back, the seed is restored along with the column. So all the right answers are wrong and all the wrong answers are right!
But the term seed is commonly misused in conection with identity columns. This is, I think, caused by the use of the keywords RESEED and NORESEED for DBCC CHECKIDENT, which certainly suggest reseeding is a possibility; what RESEED actually does is set the value to be next used in inserting a new row (without IDENTITY INSERT, of course), and as a result people call this value the seed. I think that is the meaning most people have based their answers on. That includes me, and I was being really stupid. :blush: I forgot that DBCC CHECKIDENT with RESEED parameter uses a schema modification lock, which prevents access to the table by anyone else, and the RESEED operation can be rolled back. So even with that definition of seed the correct answer is that rollback will sometimes restore it. Also, the updates to the value which are caused by row insertions can sometimes be rolled back (I need to do some experiments to see what the rules really are - has someone implemented what I thought would cause an intolerable overhead without causing the overhead?).
Tom
October 11, 2012 at 9:48 am
Good And easy for today +1
October 11, 2012 at 10:12 am
Nice to have a freebie +1
Not all gray hairs are Dinosaurs!
October 11, 2012 at 10:45 am
good question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 11, 2012 at 11:42 am
Great question, thanks!
Can you imagine the blocking issues if every other insert trans to that table had to wait to see if the IDENTITY seed was going to be reset?
I can't think of any reason for business logic to be tied to the table's PK / IDENTITY column.
Andre
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply