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 123»»»

Is it Possible to renumber a Identity Column? Expand / Collapse
Author
Message
Posted Wednesday, December 23, 2009 7:56 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, November 8, 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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 17,628, Visits: 15,487
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


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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 12,897, Visits: 32,105
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

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

Group: General Forum Members
Last Login: Thursday, November 8, 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
Posted Wednesday, December 23, 2009 8:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 9:42 PM
Points: 31,036, Visits: 15,466
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

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

Group: General Forum Members
Last Login: Thursday, November 8, 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

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

Group: General Forum Members
Last Login: Thursday, November 8, 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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:32 AM
Points: 39,893, Visits: 36,239
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 123»»»

Permissions Expand / Collapse