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 12»»

Tip: Resetting Identity Fields Expand / Collapse
Author
Message
Posted Saturday, December 01, 2012 1:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:02 PM
Points: 64, Visits: 360
Comments posted to this topic are about the item Tip: Resetting Identity Fields
Post #1391670
Posted Sunday, December 02, 2012 9:35 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 1,244, Visits: 1,592
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
Posted Sunday, December 02, 2012 11:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:02 PM
Points: 64, Visits: 360
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.
Post #1391779
Posted Sunday, December 02, 2012 11:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 5,951, Visits: 12,829
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"
Post #1391785
Posted Monday, December 03, 2012 2:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 14, 2013 1:05 AM
Points: 96, Visits: 277
Maybe I'm missing something here but why do you have an Identity column that you can abritrarily change the value of?
Post #1391818
Posted Monday, December 03, 2012 6:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 2,410, Visits: 3,436
My question is why do this? Why would anyone care if there are gaps in an identity field?

Thank you,
Tom
Post #1391914
Posted Monday, December 03, 2012 8:49 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, December 03, 2012 9:01 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
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
Posted Monday, December 03, 2012 9:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:02 PM
Points: 64, Visits: 360
@ 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.
Post #1392039
Posted Monday, December 03, 2012 9:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:41 PM
Points: 14, Visits: 131
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...
Post #1392047
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse