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 8:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #838573
Posted Wednesday, December 23, 2009 8:56 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 6:59 AM
Points: 33,155, Visits: 15,292
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #838575
Posted Wednesday, December 23, 2009 9:04 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

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
Post #838580
Posted Wednesday, December 23, 2009 9:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 15,628, Visits: 28,011
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #838587
Posted Wednesday, December 23, 2009 9:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:30 AM
Points: 346, Visits: 518
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?




Probably being used as a surrogate key?


--
Sabya
Post #838593
Posted Wednesday, December 23, 2009 9:12 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #838595
Posted Wednesday, December 23, 2009 9:15 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 6:59 AM
Points: 33,155, Visits: 15,292
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)






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #838597
Posted Wednesday, December 23, 2009 9:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #838602
Posted Wednesday, December 23, 2009 9:36 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

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
Post #838621
Posted Wednesday, December 23, 2009 9:41 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 6:59 AM
Points: 33,155, Visits: 15,292
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #838625
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse