Reseed Max value

Question

Reseed Max value

yuvipoy

SSChampion

Points: 10469
More actions
March 6, 2014 at 8:34 pm

#304907

Hi,
I have created a Int column with Identity
It has almost reached max size of Int.
i need to again start from the first
Few older records are deleted , the id which starts with 1 to 10000000 (3 year old record)
can i have some mechanism like this inside the stored procedure
DECLARE @MaxID INT
SELECT @MaxID = MAX(ID)
FROM TableID
DBCC CHECKIDENT('TableID', RESEED, @MaxID)
if i add this command in my existing stored procedure will it affect the performance?

Viewing 15 posts - 1 through 15 (of 20 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

If you change your identity column to BIGINT, you won't have to deal with duplicate ID's. The point of an identity column is to uniquely identify each row, with the help of a unique index on that column. You defeat that point somewhat if you introduce duplicates. Does your table really have more than two thousand million rows or have you reseeded before/have lots of gaps in the sequence?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

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

i would suggest to use Sequence in place of identity

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

yuvipoy (3/6/2014)
Hi,
I have created a Int column with Identity
It has almost reached max size of Int.
i need to again start from the first
Few older records are deleted , the id which starts with 1 to 10000000 (3 year old record)
can i have some mechanism like this inside the stored procedure
DECLARE @MaxID INT
SELECT @MaxID = MAX(ID)
FROM TableID
DBCC CHECKIDENT('TableID', RESEED, @MaxID)
if i add this command in my existing stored procedure will it affect the performance?

Unless your current identity value is lower than the maximum ID present in the table, what is the point of reseeding with the maximum value? You'll quickly run against the same integer limit.

As Chris said, it is better to avoid duplicates and just change the datatype.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 4

Sushil Dwivedi (3/7/2014)
i would suggest to use Sequence in place of identity

Because of which reasons?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

I misread the question.I thought he is asking alternate of identity so I suggested Sequence because performance wise, Sequence is better than Identity and we don’t have limitation in sequence like we have in identity.

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

Sushil Dwivedi (3/7/2014)
performance wise, Sequence is better than Identity

Can you show your tests and benchmarks which show this performance improvement from sequence?

we don’t have limitation in sequence like we have in identity.

Limitations such as?

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

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

Are you testing me ...?

Limitation

1.Identity:- It's table dependent, you can apply identity on single table at a time. It is not a separate database object like Sequence.

Identity column is specific to a column of a table, where as sequences will an another DB object which can be associated with column of an n number of tables

If you inserting value into table and your transaction got failed,identity assigned value to failed transaction as well

like

1,2,3,4...failed... than it will not start from 5.. it will start from 6 when you will start insert again.

These limitation we don't have in Sequence.it will have start value,increment and end value

Performance :-

Tests with just three or four concurrent requests (used as a default and manually fetching values), they are faster than an identity column with default caching, and (unlike identity based columns) you have control over the caching, so you can tune to a higher number of pre-calculated values

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 8

Sushil Dwivedi (3/7/2014)
Are you testing me ...?
Limitation
1.Identity:- It's table dependent, you can apply identity on single table at a time. It is not a separate database object like Sequence.
Identity column is specific to a column of a table, where as sequences will an another DB object which can be associated with column of an n number of tables
If you inserting value into table and your transaction got failed,identity assigned value to failed transaction as well
like
1,2,3,4...failed... than it will not start from 5.. it will start from 6 when you will start insert again.
These limitation we don't have in Sequence.it will have start value,increment and end value

If you are using IDENTITY just to have a unique ID field in your table (for example a surrogate key in a dimension table), I don't consider these limitations at all. IDENTITY is table dependent yes, but that doesn't hurt my purpose at all. Gaps can occur with identity, but as long as they are unique (being enforced with an index/constraint), I do not mind at all.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

I agree with you Koen.. it depends on your requirement and interest.

🙂

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

Sushil Dwivedi (3/7/2014)
If you inserting value into table and your transaction got failed,identity assigned value to failed transaction as well
like
1,2,3,4...failed... than it will not start from 5.. it will start from 6 when you will start insert again.
These limitation we don't have in Sequence.it will have start value,increment and end value

Sequence has gaps too, once a sequence value has been selected from the sequence (for an insert or other usage), it's not reused. It behaves much the same as identity in that regard, plus there's larger gaps possible if the number of cached values is larger

CREATE SEQUENCE Test AS int start with 1 increment BY 1;

CREATE TABLE t1 (

id int,

SomeArbColumn int

)

INSERT INTO t1 (id, SomeArbColumn)-- inserts 1