Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Changing Identity Columns Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 9:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 21, 2014 10:15 AM
Points: 2,464, Visits: 1,551
Stuart Davies (10/22/2012)
Good question and thanks for an easy start to the week.


Agreed! +1


Not all gray hairs are Dinosaurs!
Post #1375560
Posted Monday, October 22, 2012 12:50 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 8:54 PM
Points: 606, Visits: 151
Interesting Question and good topic. +1
Post #1375678
Posted Monday, October 22, 2012 1:11 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, November 11, 2013 2:35 PM
Points: 673, Visits: 123
Nice tricky question.
No rows inserted, therefore the answer is 100.
Post #1375691
Posted Monday, October 22, 2012 1:15 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:05 AM
Points: 3,467, Visits: 1,831
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 strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
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
Post #1375692
Posted Monday, October 22, 2012 1:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:47 AM
Points: 7,855, Visits: 9,603
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
Post #1375700
Posted Monday, October 22, 2012 3:26 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:05 AM
Points: 3,467, Visits: 1,831
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 strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
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
Post #1375751
Posted Monday, October 22, 2012 10:06 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:18 PM
Points: 17,964, Visits: 15,968
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
Post #1375815
Posted Thursday, October 25, 2012 7:05 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 8:55 AM
Points: 1,639, Visits: 1,985
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.
Post #1376968
Posted Thursday, October 25, 2012 7:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 8:55 AM
Points: 1,639, Visits: 1,985
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.
Post #1376971
Posted Thursday, October 25, 2012 9:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1377058
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse