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 12:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,953, Visits: 2,397
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/
Post #1375265
Posted Monday, October 22, 2012 12:50 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
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

Post #1375268
Posted Monday, October 22, 2012 1:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 13,570, Visits: 11,383
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1375271
Posted Monday, October 22, 2012 1:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:30 AM
Points: 1,417, Visits: 806
Nice question.
Post #1375286
Posted Monday, October 22, 2012 1:53 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: Today @ 3:59 AM
Points: 3,315, Visits: 3,581
Good question and thanks for an easy start to the week.

-------------------------------
Posting Data Etiquette - Jeff Moden
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
Post #1375293
Posted Monday, October 22, 2012 2:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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'

Post #1375297
Posted Monday, October 22, 2012 2:27 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:30 AM
Points: 4,121, Visits: 5,488
Good back-to-basics question, thanks, Kenneth

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1375302
Posted Monday, October 22, 2012 4:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 7,874, Visits: 9,613
Nice easy question.

Tom
Post #1375332
Posted Monday, October 22, 2012 5:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:29 PM
Points: 1,012, Visits: 1,001
Nice one, thanks

Iulian
Post #1375344
Posted Monday, October 22, 2012 5:43 AM


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: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Good question, Thanks. Also, enjoy the discussion that followed.
Post #1375353
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse