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 12:17 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
Comments posted to this topic are about the item Identity puzzle
Post #667065
Posted Tuesday, March 3, 2009 1:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 2,600, Visits: 2,448
Thanks! Some times sql behave in unexpected manner.
Post #667086
Posted Tuesday, March 3, 2009 2:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 9:13 AM
Points: 1,614, Visits: 905
Nice question
Post #667116
Posted Tuesday, March 3, 2009 8:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 23, 2009 9:32 AM
Points: 109, Visits: 70
Okay, I'm lost. If RESEED sets the value back to zero and this table was RESEEDed right before both queries, then why wouldn't both queries return zero?
Post #667315
Posted Tuesday, March 3, 2009 8:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:59 PM
Points: 2,394, Visits: 18,032
The result depends on whether or not any rows have been inserted into the table since it was created. If there haven't (like in the first reseed), the next value inserted is the reseed value. If there have, then the next value inserted is the reseed + 1. Interestingly enough, this ignores the “increment” value of the identity column. If you TRUNCATE the table instead of deleting the rows, the table acts as if there have been no rows added and the reseed uses the reseed value again. It is odd behavior, but it is also documented in BOL.

Thanks,
Chad
Post #667328
Posted Tuesday, March 3, 2009 8:53 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
Am I looking at the same question you are ?
There is no TRUNCATE TABLE anywhere in the code!




* Noel
Post #667345
Posted Tuesday, March 3, 2009 8:56 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
Nevermind, I think I am in need of coffee ;)



* Noel
Post #667352
Posted Tuesday, March 3, 2009 8:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 23, 2009 9:32 AM
Points: 109, Visits: 70
Chad - got it. Thanks!
Post #667354
Posted Tuesday, March 3, 2009 9:32 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
I am glad to see so many people getting this right. And for 3 points!

It certainly does not operate according to how I expect things to work. I just wished I had posted something more challenging than a simple script.;)

Perhaps something like:

Given a table defined as

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

and

(select count(*) from given_table) = 0

Select the right action to take to ensure that the next row inserted is 1

a) DBCC CHECKIDENT(id_test, RESEED, 0)
b) DBCC CHECKIDENT(id_test, RESEED, 1)
c) it depends, a) OR b) depending on the circumstance.

Bonus question:
If you select c, you are correct. Now how would you determine which reseed statement to perform?


I am really interested in the answer to the bonus question as I have not been able to crack this.

Post #667406
Posted Tuesday, March 3, 2009 3:36 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
raymond lew (3/3/2009)
I am glad to see so many people getting this right. And for 3 points!

It certainly does not operate according to how I expect things to work. I just wished I had posted something more challenging than a simple script.;)

Perhaps something like:

Given a table defined as

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

and

(select count(*) from given_table) = 0

Select the right action to take to ensure that the next row inserted is 1

a) DBCC CHECKIDENT(id_test, RESEED, 0)
b) DBCC CHECKIDENT(id_test, RESEED, 1)
c) it depends, a) OR b) depending on the circumstance.

Bonus question:
If you select c, you are correct. Now how would you determine which reseed statement to perform?


I am really interested in the answer to the bonus question as I have not been able to crack this.



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

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

DBCC CHECKIDENT(id_test)

INSERT INTO id_test DEFAULT VALUES;
DELETE FROM id_test

DBCC CHECKIDENT(id_test)

INSERT INTO id_test DEFAULT VALUES;
DELETE FROM id_test

DROP TABLE id_test
go

Returns

Checking identity information: current identity value 'NULL', current column value 'NULL'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(1 row(s) affected)

(1 row(s) affected)
Checking identity information: current identity value '1', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(1 row(s) affected)

(1 row(s) affected)

If DBCC CHECKIDENT (NORESEED) reports NULL for the current identity value, use 1.
Otherwise, if it reports 1, use 0.

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

DBCC CHECKIDENT(id_test)

DBCC CHECKIDENT (id_test, RESEED, 1)--reseed to 1

INSERT INTO id_test DEFAULT VALUES;
select my_id FROM id_test --select number 1
DELETE FROM id_test

DBCC CHECKIDENT(id_test)

DBCC CHECKIDENT (id_test, RESEED, 0)--reseed to 0 + 1

INSERT INTO id_test DEFAULT VALUES;
select my_id FROM id_test --select number 2
DELETE FROM id_test

DROP TABLE id_test
go

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

Of course, there is also a trivial answer:

TRUNCATE TABLE id_test -- reset table to a known state
DBCC CHECKIDENT (id_test, RESEED, 1)

Post #667714
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse