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 2 of 3

Is it Possible to renumber a Identity Column?

Expand / Collapse

Author

Message

Posted Wednesday, December 23, 2009 8:53 AM

SSCoach

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

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

Steve Jones - SSC Editor

Posted Wednesday, December 23, 2009 8:56 AM

SSC-Dedicated

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

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

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

SSChampion

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383, Visits: 25,189

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

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 3:14 AM
Points: 345, Visits: 503

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

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

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

Steve Jones - SSC Editor

Posted Wednesday, December 23, 2009 9:15 AM

SSC-Dedicated

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

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

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

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

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

Steve Jones - SSC Editor

Posted Wednesday, December 23, 2009 9:41 AM

SSC-Dedicated

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

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

22 posts, Page 2 of 3

Permissions

Expand / Collapse