Is it Possible to renumber a Identity Column?

Question

Post reply

Is it Possible to renumber a Identity Column?

leroy-1092048

Ten Centuries

Points: 1054
More actions
December 23, 2009 at 7:56 am

#215056

hello all,
we have a table that is fairly large and get many inserts and deletes.. and basically the identity column (INT) has maxed out at 2.7 billion and change. (but with only 250 million current rows)
we have checked into changing this to 'bigINT' and this is our final solution.. but currently the application is not set to accept a 64bit number vs. the current 32 bit number. So the programmer of the application have to re-tool their code for this. i do not know how long this will take.
but in the mean time i was thinking...
the identity column is not used for anything special on this table only to make dealing with individual rows easy and to avoid any duplicates. it is not used to create a relationship between other tables.
so the question is... can i reorder the identity column to reuse the numbers of the identity column that were once used? if so, how can this be done?
do i need to drop the identity column and create a new one? or make a new table and migrate the data to the new table? or is their some T-SQL to "re-order' the identity column?
Thanks for your ideas
Leroy L

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you can use DBCC CHECKIDENT to change the Identity value

http://technet.microsoft.com/en-us/library/ms176057.aspx

GSquared SSC Guru Points: 260824 More actions · Answer 2

If the column really isn't used for much, you could probably drop it, re-create it, and seed the start value at -2,147,483,648. That gives you twice the range for the number.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

In addition to the checkident suggestion, when you reseed - you could reseed to -2 billion and change. This will give you that many more values for your table.

EDIT: Just posted and immediately saw that Gus had posted the same thing.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 4

a key question is are their foreign keys tied to the current identitiy?

if there are, it makes it a lot harder, since you'd have to update those child foreign keys to a new number if you changed one key to a new value.

if there is not, you could simply create a new table(ie tablenameNEW with the layout;then insert into that table(INSERT INTO tablenameNEW(columnList) SELECT (columnlist from tablename), rename the original table (sp_rename t'ablename','tablename_bak')and maintain it as a backup, and rename the "new" table to the original tablename.(sp_rename 'tablenameNEW','tablename') you'd then have 250M records with room for 2,2 billion more.

-edit- just realized that everyone suggesting to use the GUI to drop the column and re-add it is the faster, easier way to do the same thing i suggestioed....i need more coffee today, since i said exactly that on a similar post yesterday.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

leroy-1092048 Ten Centuries Points: 1054 More actions · Answer 5

thanks for your replies..

i ran the following query:

select min(stats_ID) as min_id, max(stats_id) as max_id, Count(stats_ID) as total from The_maxed_out_table

currently the table is like this..

min ID = 308,673,131

max ID = 2,146,014,757 (a few rows can be inserted, but no bulk inserts)

current row count = 151,551,037

so i guess i can re-seed from 1 again...

DBCC CHECKIDENT ("The_maxed_out_table", RESEED, 1);

would this work?

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 6

We had an anti virus program that used identities to note a new scan of a machine. Since we had over 10k machines and this was scanning every 10 minutes or so, we would run out of identities every few months and have to reseed the values to 0. We would also delete everything but the last million or so rows.

If you can delete the rows that have identity values, Jason's or Gus' suggestion will work. If not, how will you identify duplicates or make things easy to read?

leroy-1092048 Ten Centuries Points: 1054 More actions · Answer 7

Lowell,

Thanks for the reply,

There are no foreign keys to other tables from this identity column.

But, there are foreign keys to other tables on other columns in this table like User_id, Time_id.

so i would like to avoid dropping the table and creating a new table and possibly have things go wrong and damage any relationships with other tables from the other columns.

Thanks

Leroy

leroy-1092048 Ten Centuries Points: 1054 More actions · Answer 8

Steve,

so what your saying is that the DBCC Checkidentity command only resets what the ID will be of the next column that is inserted ... but does not actually change the ID on the columns that already exist?

if my understanding is correct, then if i drop and recreate the identity column with it assign an ID to the rows that already exist?

basically it seems like if i could "reorder" the identity column from zero again.

thanks

Leroy

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

leroy-1092048 (12/23/2009)
There are no foreign keys to other tables from this identity column.

So what's the column used for?

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

GSquared SSC Guru Points: 260824 More actions · Answer 10

Resetting the ID with checkident can cause problems. Take a look at checkident in Books Online for the details.

Dropping and re-creating the column allows you to reset the starting value to whatever you need. On re-creating it, it will assign values to all existing rows.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 11

There are two things to understand about identity.

1. It involves the "next" assigned value for a column.

2. It has nothing to do with existing values. It doesn't ensure uniqueness, it doesn't change them, check them, anything.

Try this:

CREATE TABLE MyTable

( id INT IDENTITY(1,1)

)

GO

INSERT mytable DEFAULT VALUES

GO

SELECT * FROM mytable

GO

DBCC CHECKIDENT( mytable, RESEED, 0)

GO

INSERT mytable DEFAULT VALUES

GO

SELECT * FROM mytable

GO

DROP TABLE dbo.MyTable

You can repeat the reseed and insert over and over and you'll get a table of 1s.

If you reseed your current table at 0, you'll start inserting the next rows with an identity of 1, then 2, then 3. If you have rows in those tables with those identity values, then you will have duplicates. If you have an index that makes the identity unique, then you won't be able to insert duplicate rows and you'll get errors.

leroy-1092048 Ten Centuries Points: 1054 More actions · Answer 12

Gail,

honestly from what i can tell, from the app programmers, its only used for delete operations.

As the data in other rows could be very similar.

I hate to pass the buck and say that this is what i have inherited.. but its true.

we are working on long term solutions to fix these issues. but in the mean time i am trying to just get this table back to "normal operations" so we can get some more inserts done.

Leroy

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 13

If there are only a few thousand rows in the table what the heck is causing it to dump millions of ID values? That's fairly odd behavior.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 14

GilaMonster (12/23/2009)
leroy-1092048 (12/23/2009)
There are no foreign keys to other tables from this identity column.
So what's the column used for?

[font="Comic Sans MS"]

Probably being used as a surrogate key?

[/font]

[font="Comic Sans MS"]--
Sabya[/font]