SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing Identity Columns


Changing Identity Columns

Author
Message
Miles Neale
Miles Neale
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6677 Visits: 1698
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
asifkareem
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 151
Interesting Question and good topic. +1
@Cassie
@Cassie
Right there with Babe
Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)

Group: General Forum Members
Points: 757 Visits: 123
Nice tricky question.
No rows inserted, therefore the answer is 100.
Kenneth Fisher
Kenneth Fisher
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12465 Visits: 2121
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 Fisher
I 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/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51052 Visits: 13160
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
Kenneth Fisher
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12465 Visits: 2121
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 Fisher
I 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/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145607 Visits: 18652
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
Learn Extended Events

chrisfradenburg
chrisfradenburg
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5408 Visits: 2090
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
chrisfradenburg
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5408 Visits: 2090
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search