Changing Identity Columns

Question

Changing Identity Columns

October 22, 2012 at 2:27 am

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 29 (of 29 total)

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

john.arnott SSChampion Points: 11882 More actions · Answer 1

Koen Verbeeck (10/22/2012)
Lokesh Vij (10/22/2012)
kapil_kk (10/22/2012)
in this case the max ident value would be 101 as with first insert statement the value of ident was 100 and with another insert statement the ident value would be 101 and max from 101 and 100 is 101 so 101 is the max value..
hope its clear to you
Actually the question posted by John is little different. Execute the below code:
CREATE TABLE IdentTest
( Ident INT NOT NULL IDENTITY (1,1)
, varfield varchar(100)
)
INSERT INTO IdentTest VALUES ('xyz') -- <== Added to original
INSERT INTO IdentTest VALUES ('123') -- <== Added to original
DBCC CHECKIDENT ('IdentTest',RESEED,100)
INSERT INTO IdentTest VALUES ('abc')
SELECT Ident FROM IdentTest -- <== (done away with max)
Now the last select statement (done away with max statement intentionally), returns three values 1,2 and 101
Why value 101?? Why not 100 or 102??
If data is present in the table before DBCC CHECKIDENT, the new identity value is the reseed value + current incremenet. In this case, 100 + 1 = 101.
Anyway, nice question.

Thank you Koen for the good succinct way to state how this works. It did tickle my urge to keep playing, though...

Try predicting the result of the final SELECT in this script:

CREATE TABLE IdentTest

( --Ident INT NOT NULL IDENTITY (1,1)

varfield varchar(100)

)

INSERT INTO IdentTest VALUES ('xyz')

INSERT INTO IdentTest VALUES ('123')

Select * from IdentTest

ALTER TABLE IdentTest

Add Ident INT IDENTITY (1,1)

INSERT INTO IdentTest VALUES ('abc')

SELECT Ident FROM IdentTest

where varfield = 'abc'

TomThomson SSC Guru Points: 104773 More actions · Answer 2

TomThomson

SSC Guru

Points: 104773

October 22, 2012 at 4:52 am

#1551296

Nice easy question.

Tom

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 3

Iulian -207023

SSCertifiable

Points: 7639

October 22, 2012 at 5:10 am

#1551307

Nice one, thanks

Iulian

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 4

Good question, Thanks. Also, enjoy the discussion that followed.

Miles Neale SSChampion Points: 13147 More actions · Answer 5

Stuart Davies (10/22/2012)
Good question and thanks for an easy start to the week.

Agreed! +1

Not all gray hairs are Dinosaurs!

asifkareem Ten Centuries Points: 1089 More actions · Answer 6

asifkareem

Ten Centuries

Points: 1089

October 22, 2012 at 12:50 pm

#1551596

Interesting Question and good topic. +1

@Cassie SSC Eights! Points: 890 More actions · Answer 7

Nice tricky question.

No rows inserted, therefore the answer is 100.

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 8

john.arnott (10/21/2012)
Good topic for a follow-up QOD (hope I don't steal any thunder here):
If the code presented had one more statement amnd asked for the max(Ident)...
CREATE TABLE IdentTest
( Ident INT NOT NULL IDENTITY (1,1)
, varfield varchar(100)
)
INSERT INTO IdentTest VALUES ('xyz') -- <== Added to original
DBCC CHECKIDENT ('IdentTest',RESEED,100)
INSERT INTO IdentTest VALUES ('abc')
SELECT max(Ident) FROM IdentTest
What would be the output?
Wouldn't it be the same (100)?
If not, WHY NOT? (hard to make a QOD an essay question, but if you got this far, give it a shot).

Does anyone think I should go ahead and add this as a follow up QOTD? Or do one about reseeding causing a duplicate identity? Or not bother since it was discussed here already?

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

TomThomson SSC Guru Points: 104773 More actions · Answer 9

crussell-931424 (10/22/2012)
The effective result is different for a table that has had records from a table that has never had any records. Don't know why. It just is.

Not quite - it depends how you deleted the records. If you deleted them using DELETE, it's as you describe (the first record after reseed gets the incremented number) but if you deleted them using TRUNCATE TABLE it's as if there had never been any records (the first record after reseed gets the specified number, unincremented).

Tom

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 10

L' Eomot Inversé (10/22/2012)
crussell-931424 (10/22/2012)
The effective result is different for a table that has had records from a table that has never had any records. Don't know why. It just is.
Not quite - it depends how you deleted the records. If you deleted them using DELETE, it's as you describe (the first record after reseed gets the incremented number) but if you deleted them using TRUNCATE TABLE it's as if there had never been any records (the first record after reseed gets the specified number, unincremented).

If I had to guess an identity column actually has 3 properties .. Seed, Increment, and HaveIHadRowsBefore or something along those lines.

If the 3rd property is true then the next row is Seed+1, if the 3rd property is false then the next row is just Seed.

The DBCC just changes the Seed property. Truncate resets all of the properties back to the original table definition. Which I guess would actually require 2 additional properties to hold the original settings.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Koen Verbeeck (10/22/2012)
Lokesh Vij (10/22/2012)
kapil_kk (10/22/2012)
in this case the max ident value would be 101 as with first insert statement the value of ident was 100 and with another insert statement the ident value would be 101 and max from 101 and 100 is 101 so 101 is the max value..
hope its clear to you
Actually the question posted by John is little different. Execute the below code:
CREATE TABLE IdentTest
( Ident INT NOT NULL IDENTITY (1,1)
, varfield varchar(100)
)
INSERT INTO IdentTest VALUES ('xyz') -- <== Added to original
INSERT INTO IdentTest VALUES ('123') -- <== Added to original
DBCC CHECKIDENT ('IdentTest',RESEED,100)
INSERT INTO IdentTest VALUES ('abc')
SELECT Ident FROM IdentTest -- <== (done away with max)
Now the last select statement (done away with max statement intentionally), returns three values 1,2 and 101
Why value 101?? Why not 100 or 102??
If data is present in the table before DBCC CHECKIDENT, the new identity value is the reseed value + current incremenet. In this case, 100 + 1 = 101.
Anyway, nice question.

agreed, nice question and nice discussion.

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

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 12

john.arnott (10/22/2012)
Try predicting the result of the final SELECT in this script:
CREATE TABLE IdentTest
( --Ident INT NOT NULL IDENTITY (1,1)
varfield varchar(100)
)
INSERT INTO IdentTest VALUES ('xyz')
INSERT INTO IdentTest VALUES ('123')
Select * from IdentTest
ALTER TABLE IdentTest
Add Ident INT IDENTITY (1,1)
INSERT INTO IdentTest VALUES ('abc')
SELECT Ident FROM IdentTest
where varfield = 'abc'

Make this more fun, make varfield a primary key.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 13

Kenneth.Fisher (10/22/2012)
Does anyone think I should go ahead and add this as a follow up QOTD? Or do one about reseeding causing a duplicate identity? Or not bother since it was discussed here already?

It looks like there are a few possible follow up questions. Since not everyone reads the discussion I think it's worth submitting additional ones.

Lokesh Vij SSChampion Points: 10836 More actions · Answer 14

cfradenburg (10/25/2012)
Kenneth.Fisher (10/22/2012)
Does anyone think I should go ahead and add this as a follow up QOTD? Or do one about reseeding causing a duplicate identity? Or not bother since it was discussed here already?
It looks like there are a few possible follow up questions. Since not everyone reads the discussion I think it's worth submitting additional ones.

+1

It also makes sense to post a QOTD because there is a lead time of about 2-3 weeks if the question is posted today 🙂

~ 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