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


Identity puzzle


Identity puzzle

Author
Message
raymond lew
raymond lew
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 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
Right there with Babe
Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)

Group: General Forum Members
Points: 775 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-Addicted
SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 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 Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 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
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

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

SQL DBA.
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

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



Tom Garth
Tom Garth
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2039 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
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5474 Visits: 2768
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