Tip: Resetting Identity Fields

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Brandon Forest » Tip: Resetting Identity Fields

16 posts, Page 1 of 2

1 2 »»

Tip: Resetting Identity Fields

Rate Topic

Display Mode

Topic Options

Author

Message

Brandon Forest

Posted Saturday, December 01, 2012 1:49 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 2:52 PM
Points: 60, Visits: 310

Comments posted to this topic are about the item Tip: Resetting Identity Fields

Post #1391670

sknox

Posted Sunday, December 02, 2012 9:35 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:31 AM
Points: 1,041, Visits: 1,356

So you've essentially saved having to declare and set a value in a variable. But to do so, you've had to run an extra DBCC command.

It would be interesting to see how the two different approaches compare:
a) performance-wise against a large table with lots of gaps, and
b) stability-wise against an active table (i.e, one with many connections).

Perhaps if I have a few minutes tomorrow I might test...

Post #1391767

Brandon Forest

Posted Sunday, December 02, 2012 11:32 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 2:52 PM
Points: 60, Visits: 310

My intent was code simplicity. There is no "One Right Way" to code, but some ways are better than others. I will be interested in seeing your results. Wink

Post #1391779

Perry Whittle

Posted Sunday, December 02, 2012 11:51 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204, Visits: 11,165

You can just obtain the max id value after toe deletion by using

[code="sql']select max(col_id) from table_name[/code]

Then use this to determine the reseed value.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1391785

Pete Cox

Posted Monday, December 03, 2012 2:46 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 4:28 AM
Points: 88, Visits: 244

Maybe I'm missing something here but why do you have an Identity column that you can abritrarily change the value of?

Post #1391818

OCTom

Posted Monday, December 03, 2012 6:49 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 2,018, Visits: 2,852

My question is why do this? Why would anyone care if there are gaps in an identity field?

Thank you,
Tom

Post #1391914

Verena_Techie_59

Posted Monday, December 03, 2012 8:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 03, 2012 10:04 AM
Points: 1, Visits: 7

There are several reasons for congiguous identity keys.

1) It's much simpler to keep ID keys in sync between production and testing systems.

2) Coding can be simpler and more reliable. In testing I can add the rows, then delete where ID > (the max key before additions) then reset the ID key again. This is probably the best reason. When I am adding rows to a table. Of course no one else better be adding rows at the same time or it won't work anyway and I'll have to code to find the records I just added and then select the max key to reset back to.

3) It's clean.

4) What if your id keys don't go up by 1? You could run out of ID keys fast, having very large gaps and an application may not be able to handle keys that are larger than x.

5) Some designs depend on certain values for identity fields, probably a poor design mind you....

6) Some gaps are better left. Especially when cleaning up some data that has been live. There may be history attached to it that will be kept. I don't like to reuse keys in case someone coded against the ID's and some programmers have....

FYI: to do this for Sybase:

Select max(id) key from table (either before inserts or after deletions) and then use sp_chgattribute to reset the identity key back.

select max(table1_id) from database..table1
-- example 300

use database1
sp_chgattribute table1, 'identity_burn_max', 0, '300'

7) Reason 7 is for us OCD types!

Post #1392000

DiverKas

Posted Monday, December 03, 2012 9:01 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:24 AM
Points: 237, Visits: 413

Sigh... ID's like this should NOT be intelligent, by their very nature they should tell NOTHING of the data. It serves to uniquely identify a row. Thats it.

Second, any code like this that changes the keys (never should be done, but since we are showing how) needs to be wrapped in a transaction. Otherwise, the risk of assigning the seed to a key that has been used is pretty high.

Third, this only works if they key isnt a FK somewhere else. Otherwise you face the unenviable task of updating all those rows as well.

This is on my list of things you should never contemplate doing.

Post #1392012

Brandon Forest

Posted Monday, December 03, 2012 9:47 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 2:52 PM
Points: 60, Visits: 310

@ those who question why to do this. It's very simple, if you don't see the need to do it, don't do it. Questioning why someone else chooses to do something is their business not yours. If you don't agree or approve, simply shake your head and say "Bullshit" and move on. No one is interested in hearing you complain. Focus on the technique, not the why. Wrapping it in a transaction is a good idea. Why we do it is our business. Wow

Post #1392039

Vladko

Posted Monday, December 03, 2012 9:59 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 14, 2013 1:27 PM
Points: 14, Visits: 129

This is my approach - first coping all records to destination table and using the script below to clean the source and preserve identity so the next batch run will not create duplication's at the destination. The source table holds auditing data and it's growing fast and getting big (10gb per week if I do nothing). This way I can offload the table and export the records to remote database for further investigations.

USE [Mydatabase]

declare @ident bigint;
select @ident = IDENT_CURRENT('dbo.mytable')
set @ident = @ident + 1

truncate table dbo.mytable

DBCC CHECKIDENT ("dbo.mytable", RESEED, @ident)

============================================
Life is SQLized... Hehe

Post #1392047

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions