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


Identity puzzle


Identity puzzle

Author
Message
raymond lew
raymond lew
SSC-Addicted
SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)

Group: General Forum Members
Points: 478 Visits: 123
Hmmm, that bonus question has made it kind of quiet around here until Tim came through.


Tim Wilson-Brown

The answer to the bonus question is to use DBCC CHECKIDENT without reseeding



This will work if you can catch the dbcc output. The bonus bonus question: how do we interrogate the 'dbcc checkindent' output in tsql.

Raymond
brewmanz
brewmanz
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: 1072 Visits: 406
Tim Wilson-Brown (3/3/2009)

I'll leave you with the challenge of parsing the output from DBCC CHECKIDENT.

SQL Server should act like a Finite State Machine, to be able to predict its behaviour when a known input is applied, such as DBCC CHECKIDENT (#a, RESEED, 0) and then a new record inserted.
Clearly, DBCC CHECKIDENT without RESEED is accessing some information that is most useful to us trying to predict the behaviour when a new record is inserted, but I for one cannot see how to do it (other than the clumsy parse of DBCC CHECKIDENT output).

Does anyone know how to directly get this information?
Tim Wilson-Brown
Tim Wilson-Brown
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 302
brewmanz (3/3/2009)
Tim Wilson-Brown (3/3/2009)

I'll leave you with the challenge of parsing the output from DBCC CHECKIDENT.

SQL Server should act like a Finite State Machine, to be able to predict its behaviour when a known input is applied, such as DBCC CHECKIDENT (#a, RESEED, 0) and then a new record inserted.
Clearly, DBCC CHECKIDENT without RESEED is accessing some information that is most useful to us trying to predict the behaviour when a new record is inserted, but I for one cannot see how to do it (other than the clumsy parse of DBCC CHECKIDENT output).

Does anyone know how to directly get this information?



CREATE TABLE id_test ( my_id INT IDENTITY(1,1) NOT NULL)

SELECT i.last_value
FROM sys.identity_columns i INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'id_test'

DBCC CHECKIDENT(id_test)

INSERT INTO id_test DEFAULT VALUES;
DELETE FROM id_test

SELECT i.last_value
FROM sys.identity_columns i INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'id_test'

DBCC CHECKIDENT(id_test)

INSERT INTO id_test DEFAULT VALUES;
DELETE FROM id_test

DROP TABLE id_test
go



The fact that 'NULL' is returned by DBCC CHECKIDENT is a big clue...
raymond lew
raymond lew
SSC-Addicted
SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)

Group: General Forum Members
Points: 478 Visits: 123
Thank you, Tim. You get the bonus points and the bonus bonus points.

Here's my quick tsql for wrapping this up. This is still more work than I want to do and test, so your option b of always truncating before reseed is straight forward and just as consistent.


CREATE TABLE id_test ( my_id INT IDENTITY(1,1) NOT NULL)

INSERT INTO id_test DEFAULT VALUES;
INSERT INTO id_test DEFAULT VALUES;

-- uncomment delete or truncate to test the two scenarios
DELETE FROM id_test

--truncate TABLE id_test

IF EXISTS( SELECT 1
FROM sys.identity_columns i INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'id_test' AND i.last_value IS not NULL )
DBCC CHECKIDENT(id_test, RESEED, 0)
ELSE
DBCC CHECKIDENT(id_test, RESEED, 1)

INSERT INTO id_test DEFAULT VALUES;

SELECT * FROM id_test

DROP TABLE id_test

go


mondo
SanjayAttray
SanjayAttray
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8399 Visits: 1619
Got 3 points ! ! ! What a fluke. But I still was not able to understand reseed issue.

SQL DBA.
Anipaul
Anipaul
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14581 Visits: 1407
Nice one...



Tom Garth
Tom Garth
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: 3477 Visits: 1499
This falls under the use-it or lose-it category. I got this wrong even though I knew the answer.

It's a good QOD though.

I would like to know the reason behind this behavior. I believe the SS Devs did this by design, and I wish that I knew why.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

kapil_kk
kapil_kk
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10617 Visits: 2778
Really Gud Question....
Alas, I confused so first I ran the code and then answer it Crying

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
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