IDENTITY RESEED

Question

Viewing 6 posts - 16 through 20 (of 20 total)

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

Anipaul SSC-Insane Points: 24681 More actions · Answer 1

SSC-Insane

Points: 24681

January 27, 2014 at 5:37 am

Very detailed explanation. Thank you.

Revenant SSC-Forever Points: 42467 More actions · Answer 2

Carlo Romagnano (1/24/2014)
Koen Verbeeck (1/24/2014)
Great question and a very nice explanation.
+1

+1 =- thanks, Lokesh!

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 3

SSCarpal Tunnel

Points: 4965

January 28, 2014 at 9:15 am

great question..

thanks Lokesh.

sqlnaive SSCoach Points: 17437 More actions · Answer 4

Very good QOTD. The answer here clarifies few thigns about the identity functionality in simplest way. Thanks Lokesh. 🙂

~nano SSC Eights! Points: 804 More actions · Answer 5

Long time past I know ... still great question and even greater explanation.

One small thing though that I'm tring to get my head around ... in the explanation , is this row correct? :

Statement: DBCC CHECKIDENT ('dbo.test',RESEED);

Current Identity:5

Next Row Identity: 5

MAX(ID): 6

Remarks: As Current Identity < MAX(id), this statement reseeds identity to MAX(id)

Seems to me as if Next Row Identity and MAX(ID) are swapped?

If MAX(ID) were 6, then the first ID after DBCC CHECKIDENT ('dbo.test',RESEED); would have been 7 - or am I going wrong somewhere?

But if the values in the row are swapped, then the msdn Documentation is a bit unclear ...

It only handles the conditions

Current Identity < MAX(id)

and

Current Identity > MAX(id)

What about Current Identity = MAX(id) as in this case?

If the RESEED hadn't taken place, then the next Insert should also have thrown an Error,

so I guess it is handled just like Current Identity < MAX(id)

Or am I completely wrong?

nano

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

I'm doing some identity research. Thanks for your question.