Reseed Max value

Question

Reseed Max value

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 1

I think the discussion went a bit off the OP's topic. Yes, he mentioned performance in his last sentence, but basically, the answer has been provided early on. Change the column type to bigint. Which of course doesn't solve the issue once one hits the max(bigint) limit.

As to re-using IDENTITY values, it would mean that you would have to set IDENTITY_INSERT to ON for one. But now, if you decide to go for that, how do you know which IDENTITY values you can reuse? Surely this by itself would be a performance killer? Then one might as well stay with a normal integer column and figure out the next number and make sure there are no concurrency issues.

SEQUENCE doesn't improve the situation in this case, in fact it even worsens it, since a SEQUENCE can be shared across multiple objects. How would you ever reuse those? Check across all tables that use that SEQUENCE object? And again make sure you have no concurrency issues? No, thanks.

Regards,

Jan

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Sushil Dwivedi SSC Eights! Points: 973 More actions · Answer 2

Thanks all. It was nice discussion.

I had few gaps on my knowledge which i have filled after this discussion. If you get time, please read below article, it has practicle example

http://byobi.com/blog/2012/09/sequence-vs-identity-performance-comparison/

bdkdavid Ten Centuries Points: 1220 More actions · Answer 3

Hi All,

My problem is exactly this post.

Running SQL Server 2012E

I have an table which I am loading with new data

I am entering value 0 and value 999999 two separate insert statements

I run:

DBCC CHECKIDENT ('TableName', RESEED, 0);

message

Checking identity information: current identity value '1001001'. Current id would be 999999

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC CHECKIDENT ('TableName');

message

Checking identity information: current identity value '0', current column value '1001001'. Current id would be 999999

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When I enter new data the next value is either 100000 0r 100999

I am trying to get start at identity of 1 "One"

Any ideas?

bdkdavid Ten Centuries Points: 1220 More actions · Answer 4

I just wanted to add more information.

DBCC CHECKIDENT ( table_name, RESEED )

message

Checking identity information: current identity value '0', current column value '1001003'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

increments to next highest value current column value

DBCC CHECKIDENT ( table_name, NORESEED )

insert new value it does not insert

message

Checking identity information: current identity value '1', current column value '1001004'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I have records inserted in the first 5000 ID's.

I want to start at the 5001.

I thought if you reseed the value it will search for the first open spot etc.

bdkdavid Ten Centuries Points: 1220 More actions · Answer 5

OK! Doing a little more research.

My identity column is a primary key column.

So I guess my question is!

Is there a way to insert data into the column that has a higher value?

Example:

ID:

1

2

5

10

Would it be possible to RESEED at 1 and have SQL fill in

3

4

6

7

8

9

11

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please post new questions in a new thread. Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass