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 ««12

Identity puzzle Expand / Collapse
Author
Message
Posted Tuesday, March 3, 2009 4:34 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 27, 2009 10:01 AM
Points: 48, 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
Post #667754
Posted Tuesday, March 3, 2009 9:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 3:37 AM
Points: 488, Visits: 405
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?
Post #667831
Posted Tuesday, March 3, 2009 10:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 5:40 PM
Points: 209, 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...
Post #667840
Posted Wednesday, March 4, 2009 8:56 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 27, 2009 10:01 AM
Points: 48, 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
Post #668349
Posted Wednesday, March 4, 2009 9:20 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: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Got 3 points ! ! ! What a fluke. But I still was not able to understand reseed issue.

SQL DBA.
Post #668386
Posted Wednesday, March 4, 2009 11:31 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,406, Visits: 1,400
Nice one...


Post #668539
Posted Wednesday, March 4, 2009 12:44 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #668605
Posted Tuesday, February 12, 2013 10:33 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 4:54 AM
Points: 1,921, Visits: 2,345
Really Gud Question....
Alas, I confused so first I ran the code and then answer it



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1419294
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse