InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 09:57:11 GMT20http://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxOne solution would be to change the DATATYPE of the IDENTITY column to BIGINT.You would have to reload the table, and you would have to change foreign key columns in referencing tables to BIGINT.You would also have to change stored procedures or application code where there are temp tables or parameters for the identity column to BIGINT.Thu, 28 Mar 2013 10:09:46 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxI wrote this script to do exactly what you are asking on a project i inherited. Hope this helps...k--Drop temporary tables (if they exists) If you run this more than once.Use [myDB]Drop table tmpReIdTableGo--Create the temporary tableUse [myDB]CREATE TABLE tmpReIdTable( [newID] [int] IDENTITY(1,1) NOT NULL, oldID [int] NULL,CONSTRAINT [newID] PRIMARY KEY CLUSTERED ( [newID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GO--Insert into temp table from sourceTable using existing IDsUse [myDB]Insert into tmpReIdTable(oldID)select ID from sourceTable Order by WhateverYouWant--Update sourceTable table using newID from temp tableUse [myDB]Update sourceTableset sourceTable.ID=tmpReIdTable.newIDfrom sourceTable inner join tmpReIdTableon sourceTable.ID=tmpReIdTable.oldIDGOThu, 28 Mar 2013 09:54:11 GMTcooperkhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxYou don't need to drop the column and add it back. Reseed it instead, it does the same thing. Just set your seed at a place it won't conflict with existing rows.Wed, 23 Dec 2009 09:41:09 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxto all,thanks for all the replies on this issue.this database actually keeps some statistical data about the application. this table is the data the detailed data for the current week. at the end of the week the data is recalculated into month stats and moved to another table. so basically all the data is inserted for 1 week then removed.I think from all the input i will drop the column and recreate it as an identity column, (as a few suggested) this will restart the count from 1 and assign a value to the existing rows. this will start us over from scratch until the programmers can come up with a more permanent solution.Thanks everyone.Leroy LWed, 23 Dec 2009 09:36:08 GMTleroy-1092048http://www.sqlservercentral.com/Forums/Topic838527-338-1.aspx[quote][b]Steve Jones - Editor (12/23/2009)[/b][hr]Lots of adds and deletes can do it. Our AV program would add in 10k+ every time, and I think it trimmed out values older than a week once a day. So we could see the identity value jump by over 10mm a week, but we'd only have about that many rows in the table at any time. So despite getting to 1B over months, we'd still see 10mm rows in the table (roughly)[/quote]That sounds like a good argument for using a GUID instead of an identity.Wed, 23 Dec 2009 09:19:50 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxLots of adds and deletes can do it. Our AV program would add in 10k+ every time, and I think it trimmed out values older than a week once a day. So we could see the identity value jump by over 10mm a week, but we'd only have about that many rows in the table at any time. So despite getting to 1B over months, we'd still see 10mm rows in the table (roughly)Wed, 23 Dec 2009 09:15:13 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxTry a script like this in your dev/test environment:[code="sql"]alter table dbo.MyTabledrop column ID;alter table dbo.MyTableadd ID int identity (-2147483648, 1);[/code]It will give you errors if there are constraints or indexes referencing that column. If so, you'll need to review those and, most likely, add the necessary drop and create scripts to this in order to modify them accordingly.Wed, 23 Dec 2009 09:12:55 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspx[quote][b]GilaMonster (12/23/2009)[/b][hr][quote][b]leroy-1092048 (12/23/2009)[/b][hr]There are no foreign keys to other tables from this identity column.[/quote]So what's the column used for?[/quote][font="Comic Sans MS"]Probably being used as a surrogate key?[/font]Wed, 23 Dec 2009 09:12:36 GMTsabyasmhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxIf 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.Wed, 23 Dec 2009 09:08:32 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxGail,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.LeroyWed, 23 Dec 2009 09:04:18 GMTleroy-1092048http://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxThere 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:[code="sql"]CREATE TABLE MyTable( id INT IDENTITY(1,1))GOINSERT mytable DEFAULT VALUESGOSELECT * FROM mytableGODBCC CHECKIDENT( mytable, RESEED, 0)GOINSERT mytable DEFAULT VALUESGOSELECT * FROM mytableGODROP TABLE dbo.MyTable[/code]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.Wed, 23 Dec 2009 08:56:26 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxResetting 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.Wed, 23 Dec 2009 08:53:50 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspx[quote][b]leroy-1092048 (12/23/2009)[/b][hr]There are no foreign keys to other tables from this identity column.[/quote]So what's the column used for?Wed, 23 Dec 2009 08:52:25 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxSteve,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.thanksLeroyWed, 23 Dec 2009 08:48:44 GMTleroy-1092048http://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxLowell,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.ThanksLeroyWed, 23 Dec 2009 08:39:03 GMTleroy-1092048http://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxWe 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?Wed, 23 Dec 2009 08:29:31 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxthanks 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_tablecurrently the table is like this..min ID = 308,673,131max ID = 2,146,014,757 (a few rows can be inserted, but no bulk inserts)current row count = 151,551,037so i guess i can re-seed from 1 again...DBCC CHECKIDENT ("The_maxed_out_table", RESEED, 1);would this work?Wed, 23 Dec 2009 08:27:21 GMTleroy-1092048http://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxa 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.Wed, 23 Dec 2009 08:13:15 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxIn 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.Wed, 23 Dec 2009 08:10:24 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxIf 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.Wed, 23 Dec 2009 08:09:31 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxyou can use DBCC CHECKIDENT to change the Identity value[url]http://technet.microsoft.com/en-us/library/ms176057.aspx [/url]Wed, 23 Dec 2009 08:07:28 GMTsteveb. http://www.sqlservercentral.com/Forums/Topic838527-338-1.aspxhello 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 ideasLeroy LWed, 23 Dec 2009 07:56:17 GMTleroy-1092048