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


Identity puzzle


Identity puzzle

Author
Message
raymond lew
raymond lew
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 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
Mr or Mrs. 500
Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)

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

Group: General Forum Members
Points: 279 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 Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 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
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

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

SQL DBA.
Anipaul
Anipaul
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: 7373 Visits: 1407
Nice one...



Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1259 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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3105 Visits: 2766
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