Changing Identity Columns
-
Comments posted to this topic are about the item Changing Identity Columns
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]
-
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 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[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Intersting thread:)
-
nice question to start on Monday with +1 :-):-P
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
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/ -
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/ -
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[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Execute this code and you will get duplicate values in Identity values--
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
SELECT Ident FROM IdentTest
-- Result 1,2
DBCC CHECKIDENT ('IdentTest',RESEED,100)
--Checking identity information: current identity value '2', current column value '100'.
INSERT INTO IdentTest VALUES ('abc')
SELECT Ident FROM IdentTest
--Result 1,2,101
DBCC CHECKIDENT ('IdentTest',RESEED,100)
--Checking identity information: current identity value '101', current column value '100'.
INSERT INTO IdentTest VALUES ('abc')
SELECT Ident FROM IdentTest
--Result 1,2,101,101
Its intresting to know that functionality of identity is exploiting in this way
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
kapil_kk (10/22/2012)
Execute this code and you will get duplicate values in Identity values--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
SELECT Ident FROM IdentTest
-- Result 1,2
DBCC CHECKIDENT ('IdentTest',RESEED,100)
--Checking identity information: current identity value '2', current column value '100'.
INSERT INTO IdentTest VALUES ('abc')
SELECT Ident FROM IdentTest
--Result 1,2,101
DBCC CHECKIDENT ('IdentTest',RESEED,100)
--Checking identity information: current identity value '101', current column value '100'.
INSERT INTO IdentTest VALUES ('abc')
SELECT Ident FROM IdentTest
--Result 1,2,101,101
Its intresting to know that functionality of identity is exploiting in this way
Very true..that's what I was trying to point out:-)
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Nice question.
-
Good question and thanks for an easy start to the week.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply