December 23, 2009 at 9:12 am
Try a script like this in your dev/test environment:
alter table dbo.MyTable
drop column ID;
alter table dbo.MyTable
add ID int identity (-2147483648, 1);
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.
- 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
December 23, 2009 at 9:15 am
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)
December 23, 2009 at 9:19 am
Steve Jones - Editor (12/23/2009)
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)
That sounds like a good argument for using a GUID instead of an identity.
- 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
December 23, 2009 at 9:36 am
to 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 L
December 23, 2009 at 9:41 am
You 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.
March 28, 2013 at 9:54 am
I 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 tmpReIdTable
Go
--Create the temporary table
Use [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 IDs
Use [myDB]
Insert into tmpReIdTable
(oldID)
select ID from sourceTable Order by WhateverYouWant
--Update sourceTable table using newID from temp table
Use [myDB]
Update sourceTable
set sourceTable.ID=tmpReIdTable.newID
from sourceTable inner join tmpReIdTable
on sourceTable.ID=tmpReIdTable.oldID
GO
March 28, 2013 at 10:09 am
One 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.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply