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 1234»»»

Changing Identity Columns Expand / Collapse
Author
Message
Posted Sunday, October 21, 2012 6:30 AM


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: Friday, July 25, 2014 11:01 AM
Points: 3,461, Visits: 1,785
Comments posted to this topic are about the item Changing Identity Columns

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 #1375166
Posted Sunday, October 21, 2012 10:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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).
Post #1375246
Posted Sunday, October 21, 2012 10:55 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Very nice question indeed. Guess what sould be the output of the last select statement, If I add a TRUNCATE statement in the code as well:

CREATE TABLE IdentTest 
( Ident INT NOT NULL IDENTITY (1,1)
, varfield varchar(100)
)
INSERT INTO IdentTest VALUES ('abc')
DBCC CHECKIDENT ('IdentTest',RESEED,100)

INSERT INTO IdentTest VALUES ('abc')

TRUNCATE TABLE identTest

INSERT INTO IdentTest VALUES ('abc')

SELECT Ident FROM IdentTest



~ 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 #1375248
Posted Sunday, October 21, 2012 11:18 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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).


Very interesting. It kept me thinking....if there were more than 100 records in the table before Identity reseed command is executed...we have duplicate identity values


~ 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 #1375249
Posted Sunday, October 21, 2012 11:27 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:45 AM
Points: 616, Visits: 97
Intersting thread:)
Post #1375250
Posted Sunday, October 21, 2012 11:58 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
nice question to start on Monday with +1


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1375254
Posted Monday, October 22, 2012 12:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1375255
Posted Monday, October 22, 2012 12:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
Hi lokesh,
as you TRUNCATE the table then identity will be reset to 1 and now the ident value after a insert statement will be 1..



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1375257
Posted Monday, October 22, 2012 12:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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


Wrong!
with first insert statement, identity value will be 1 and with second insert statement it will be 101.


~ 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 #1375262
Posted Monday, October 22, 2012 12:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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??


~ 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 #1375264
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse