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


Changing Identity Columns


Changing Identity Columns

Author
Message
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5268 Visits: 2767
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/
Lokesh Vij
Lokesh Vij
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3292 Visits: 1599
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
Guidelines for answers on Performance questions

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

Follow me @Twitter


Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61637 Visits: 13297
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
paul s-306273
paul s-306273
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3478 Visits: 1169
Nice question.
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7375 Visits: 4817
Good question and thanks for an easy start to the week.

-------------------------------Posting Data Etiquette - Jeff Moden Smart way to ask a questionThere 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
john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3562 Visits: 3059
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'


Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16938 Visits: 7413
Good back-to-basics question, thanks, Kenneth

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25799 Visits: 12494
Nice easy question.

Tom

Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2449 Visits: 1248
Nice one, thanks

Iulian
(Bob Brown)
(Bob Brown)
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 1145
Good question, Thanks. Also, enjoy the discussion that followed.
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