Is it Possible to renumber a Identity Column?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » T-SQL (SS2K5) » Is it Possible to renumber a Identity Column?...

Add to briefcase

22 posts, Page 1 of 3

Is it Possible to renumber a Identity Column?

Expand / Collapse

Author

Message

Posted Wednesday, December 23, 2009 7:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 08, 2012 3:12 PM
Points: 154, Visits: 131

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

Post #838527

Posted Wednesday, December 23, 2009 8:07 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:07 AM
Points: 2,802, Visits: 7,107

you can use DBCC CHECKIDENT to change the Identity value
http://technet.microsoft.com/en-us/library/ms176057.aspx

Post #838538

Posted Wednesday, December 23, 2009 8:09 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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

Post #838539

Posted Wednesday, December 23, 2009 8:10 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #838540

Posted Wednesday, December 23, 2009 8:13 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 11,648, Visits: 27,768

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #838542

Posted Wednesday, December 23, 2009 8:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 08, 2012 3:12 PM
Points: 154, Visits: 131

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?

Post #838551

Steve Jones - SSC Editor

Posted Wednesday, December 23, 2009 8:29 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:20 AM
Points: 31,437, Visits: 13,752

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?

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #838553

Posted Wednesday, December 23, 2009 8:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 08, 2012 3:12 PM
Points: 154, Visits: 131

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

Post #838559

Posted Wednesday, December 23, 2009 8:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 08, 2012 3:12 PM
Points: 154, Visits: 131

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

Post #838570

Posted Wednesday, December 23, 2009 8:52 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

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 2008, MVP
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

Post #838572

« Prev Topic | Next Topic »

Add to briefcase

22 posts, Page 1 of 3

Permissions

Expand / Collapse