December 1, 2012 at 1:49 pm
Comments posted to this topic are about the item Tip: Resetting Identity Fields
December 2, 2012 at 9:35 pm
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...
December 2, 2012 at 11:32 pm
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. 😉
December 2, 2012 at 11:51 pm
You can just obtain the max id value after toe deletion by using
select max(col_id) from table_name
Then use this to determine the reseed value.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 3, 2012 at 2:46 am
Maybe I'm missing something here but why do you have an Identity column that you can abritrarily change the value of?
December 3, 2012 at 6:49 am
My question is why do this? Why would anyone care if there are gaps in an identity field?
Thank you,
Tom
December 3, 2012 at 8:49 am
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!
December 3, 2012 at 9:01 am
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.
December 3, 2012 at 9:47 am
@ 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:
December 3, 2012 at 9:59 am
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:
December 3, 2012 at 2:02 pm
I am afraid that these two lines of code don't work when we delete some rows between Fld1=1 and Fld=10 instead of the last few rows as the example in the article.
Let's try below scripts:
......
//delete these three rows Fld1 3,4,5
delete from TestReseed where Fld1 between 3 and 5
//execute those two lines of DBCC code
dbcc checkident("TestReseed", reseed,1)
dbcc checkident("TestReseed", reseed)
//insert three rows back
insert into TestReseed(Fld2) values (3)
insert into TestReseed(Fld2) values (4)
insert into TestReseed(Fld2) values (5)
....
December 3, 2012 at 2:23 pm
Verena_Techie_59 (12/3/2012)
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!
OK. Thanks. I appreciate your answer.
December 3, 2012 at 2:25 pm
Brandon Forest (12/3/2012)
@ 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:
Who is complaining? I asked why because I truly wanted to know. I never thought to be concerned with gaps.
December 3, 2012 at 7:54 pm
I've had to deal with a 32bit identity value that starts at 1, design and business changes mean that the expected range of a few million is now several billion...and within a few years, 2^32 is exceeded. Reseed at minvalue of 32-bit int, and you still have 2 billion values ... which becomes a problem in a year because growth is exponential.
There is a use case for this - sometimes it's a bandaid approach to stave off disaster (i.e. you start with a few thousand customers and sales go crazy, suddenly you have a million customers - extended downtime is not an option). Sometimes it's just a case of data changes - remember the golden rule of SQL: "It depends." Nobody runs their database the same as the next guy, so a few of you should be careful not to say this is never an option. Sometimes it is, but there's often a tradeoff. I can tell you from experience that if because of growth you have to reseed twice or three times - you really want a solid plan to permanently fix it. I've had to clean up the fallout from others' quick fixes.
December 4, 2012 at 1:29 am
Shaking head. Moving on.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply