Resetting 'Identity Specification' column in Sql Table
-
hello guys... I have this numeric column whose 'Identity Specification' I have set to Yes . But sometimes problem occurs when I use this technique due to the fact that, when I delete all the records from this table, this column's ID does not reset to 1. Instead, it retains the last ID (the ID before deleting all the records). SO the question is: how can I reset this column to 1, after I delete all the records from this table? thnx for any help.
You need to Reseed the Column using the DBCC command of MS SQL Server. dbcc CHECKIDENT("TableName1",RESEED,0) DBCC CHECKIDENT for Microsoft SQL Server I hope this help with your Problem _____________________________________________________________________ Hey guys. Just to defend my answer from the birds and stones :) . The Reason for me using the Delete/DBCC CHECKIDENT Function is that you can still use Auditing on the tables if needed (The Triggers are still called). With a truncate table call the triggers will not be called Truncate Table Because TRUNCATE TABLE is not logged, it cannot activate a trigger. Cheers
-
hello guys... I have this numeric column whose 'Identity Specification' I have set to Yes . But sometimes problem occurs when I use this technique due to the fact that, when I delete all the records from this table, this column's ID does not reset to 1. Instead, it retains the last ID (the ID before deleting all the records). SO the question is: how can I reset this column to 1, after I delete all the records from this table? thnx for any help.
-
If there are no foreign keys on the table I guess you can always use: truncate table to kill 2 birds with one stone... :-)
-
Don't let the animal welfare people hear you talk of "killing two birds with one stone". :laugh:
-
If there are no foreign keys on the table I guess you can always use: truncate table to kill 2 birds with one stone... :-)
Nah this is so obvious that Ellen's answer had to be the relevant one surely! Besides I'd use a shotgun and get the whole flock (or at least more than 2).
Never underestimate the power of human stupidity RAH
-
Nah this is so obvious that Ellen's answer had to be the relevant one surely! Besides I'd use a shotgun and get the whole flock (or at least more than 2).
Never underestimate the power of human stupidity RAH
Hmm, I guess it all depends where you need to use it. I prefer my code to be able to be used in as many situations as possible, therefore to me issuing DML is _always_ preferable to DDL. Besides which, the obvious solution to deleting all rows in a table is to use truncate, the fact that it resets the seed is an added benefit, why make life more difficult for yourself than you need to ? ;)
-
Hmm, I guess it all depends where you need to use it. I prefer my code to be able to be used in as many situations as possible, therefore to me issuing DML is _always_ preferable to DDL. Besides which, the obvious solution to deleting all rows in a table is to use truncate, the fact that it resets the seed is an added benefit, why make life more difficult for yourself than you need to ? ;)
unclejimbob wrote:
I prefer my code to be able to be used in as many situations as possible
I don't ever recall actually coding a truncate/reseed into a stored proc, in 20+ years. These live under the snippets/template folder in my IDE!
Never underestimate the power of human stupidity RAH
-
unclejimbob wrote:
I prefer my code to be able to be used in as many situations as possible
I don't ever recall actually coding a truncate/reseed into a stored proc, in 20+ years. These live under the snippets/template folder in my IDE!
Never underestimate the power of human stupidity RAH
"I don't ever recall actually coding a truncate/reseed into a stored proc, in 20+ years." Hey, good for you, I use truncate in sps for several - admittedly specialist - purposes. I too have a large number of tsql patterns/idioms as templates. I can't ever recall anyone suggesting that 2 lines of code (delete/reseed) is somehow better than 1 (truncate) and yes, I've been at this game at least as long as you have. You will note that I never suggested my solution was any better or worse than Ellen's I just looked at it from a slightly different angle, taking into account the full content of the question - an approach you might like to try and emulate rather than turning a useful discussion into a point-scoring exercise with you as the self-anointed games master. At 20+ years experience I shouldn't have to be pointing out to you self-evident things such as the fact that the first/most obvious solution isn't necessarily the most appropriate. Oh yeah the bird thing ? Leave it, it started out as witty repartee and you've come in late in the piece and successfully tortured it to death. ___ Updated/final: A few observations on this thread before I go. I have deliberately not read either the most recent post or my email as I assume it contains the same pointless drivel I've been exposed to up to now. 1) Anyone who includes the clause 'I've never needed to do this in x years' is, of course, demeaning your contribution because they mistakenly equate length of service with quality of work; you rarely see this from published, world-class contributors, they tend to be pretty diffident - you don't need to put up with it 2) Indidivuals who come in after the fact and attempt to rate your contribution based upon their own close-enough-is-good-enough two-second evaluation are, of course, putting you down - don't accept it 3) Argumentative postings which don't add value to the discussion are a sign of insecurity over one's position - don't accept it 4) Zero-value postings which are used to simply boost an already over-inflated points-based rating system are, of course to be accepted for what they are 5) Crass, infantile, witless and off-colour postings unrelated to an original throw-away post are a sign that the person is humour-challenged, accept the fact that these people cannot be helped Read the posts in this thread, there are 2 valid answers, 2 witty (albeit off-topic) observations and a host of pointless, unsubstantiated nitpicking - I leave you to decide which of these posts has actual
-
"I don't ever recall actually coding a truncate/reseed into a stored proc, in 20+ years." Hey, good for you, I use truncate in sps for several - admittedly specialist - purposes. I too have a large number of tsql patterns/idioms as templates. I can't ever recall anyone suggesting that 2 lines of code (delete/reseed) is somehow better than 1 (truncate) and yes, I've been at this game at least as long as you have. You will note that I never suggested my solution was any better or worse than Ellen's I just looked at it from a slightly different angle, taking into account the full content of the question - an approach you might like to try and emulate rather than turning a useful discussion into a point-scoring exercise with you as the self-anointed games master. At 20+ years experience I shouldn't have to be pointing out to you self-evident things such as the fact that the first/most obvious solution isn't necessarily the most appropriate. Oh yeah the bird thing ? Leave it, it started out as witty repartee and you've come in late in the piece and successfully tortured it to death. ___ Updated/final: A few observations on this thread before I go. I have deliberately not read either the most recent post or my email as I assume it contains the same pointless drivel I've been exposed to up to now. 1) Anyone who includes the clause 'I've never needed to do this in x years' is, of course, demeaning your contribution because they mistakenly equate length of service with quality of work; you rarely see this from published, world-class contributors, they tend to be pretty diffident - you don't need to put up with it 2) Indidivuals who come in after the fact and attempt to rate your contribution based upon their own close-enough-is-good-enough two-second evaluation are, of course, putting you down - don't accept it 3) Argumentative postings which don't add value to the discussion are a sign of insecurity over one's position - don't accept it 4) Zero-value postings which are used to simply boost an already over-inflated points-based rating system are, of course to be accepted for what they are 5) Crass, infantile, witless and off-colour postings unrelated to an original throw-away post are a sign that the person is humour-challenged, accept the fact that these people cannot be helped Read the posts in this thread, there are 2 valid answers, 2 witty (albeit off-topic) observations and a host of pointless, unsubstantiated nitpicking - I leave you to decide which of these posts has actual
unclejimbob wrote:
I use truncate in sps for several - admittedly specialist - purposes.
As you say , specialised requirements, thankfully I have never actually had to use them.
unclejimbob wrote:
Taking into account the full content of the question - an approach you might like to try and emulate
What and actually give a balanced and reasonable response - Nah. I actually was not denigrating your response as it was a good one (and got an upvote from me) more to the point that the OP probably was not aware of the limitation of the truncate solution.
unclejimbob wrote:
Oh yeah the bird thing
Never, I say never, have I come in late to an obscure discussion and got the wrong handle on on the discussion while trying to be amusing!
Never underestimate the power of human stupidity RAH
-
You need to Reseed the Column using the DBCC command of MS SQL Server. dbcc CHECKIDENT("TableName1",RESEED,0) DBCC CHECKIDENT for Microsoft SQL Server I hope this help with your Problem _____________________________________________________________________ Hey guys. Just to defend my answer from the birds and stones :) . The Reason for me using the Delete/DBCC CHECKIDENT Function is that you can still use Auditing on the tables if needed (The Triggers are still called). With a truncate table call the triggers will not be called Truncate Table Because TRUNCATE TABLE is not logged, it cannot activate a trigger. Cheers
Thnx for your answer. It perfectly solves my problem :)
-
"I don't ever recall actually coding a truncate/reseed into a stored proc, in 20+ years." Hey, good for you, I use truncate in sps for several - admittedly specialist - purposes. I too have a large number of tsql patterns/idioms as templates. I can't ever recall anyone suggesting that 2 lines of code (delete/reseed) is somehow better than 1 (truncate) and yes, I've been at this game at least as long as you have. You will note that I never suggested my solution was any better or worse than Ellen's I just looked at it from a slightly different angle, taking into account the full content of the question - an approach you might like to try and emulate rather than turning a useful discussion into a point-scoring exercise with you as the self-anointed games master. At 20+ years experience I shouldn't have to be pointing out to you self-evident things such as the fact that the first/most obvious solution isn't necessarily the most appropriate. Oh yeah the bird thing ? Leave it, it started out as witty repartee and you've come in late in the piece and successfully tortured it to death. ___ Updated/final: A few observations on this thread before I go. I have deliberately not read either the most recent post or my email as I assume it contains the same pointless drivel I've been exposed to up to now. 1) Anyone who includes the clause 'I've never needed to do this in x years' is, of course, demeaning your contribution because they mistakenly equate length of service with quality of work; you rarely see this from published, world-class contributors, they tend to be pretty diffident - you don't need to put up with it 2) Indidivuals who come in after the fact and attempt to rate your contribution based upon their own close-enough-is-good-enough two-second evaluation are, of course, putting you down - don't accept it 3) Argumentative postings which don't add value to the discussion are a sign of insecurity over one's position - don't accept it 4) Zero-value postings which are used to simply boost an already over-inflated points-based rating system are, of course to be accepted for what they are 5) Crass, infantile, witless and off-colour postings unrelated to an original throw-away post are a sign that the person is humour-challenged, accept the fact that these people cannot be helped Read the posts in this thread, there are 2 valid answers, 2 witty (albeit off-topic) observations and a host of pointless, unsubstantiated nitpicking - I leave you to decide which of these posts has actual
unclejimbob wrote:
I can't ever recall anyone suggesting that 2 lines of code (delete/reseed) is somehow better than 1 (truncate) and yes, I've been at this game at least as long as you have.
Hopefully that statement is flippant. There are in fact differences between using delete and truncate, especially in SQL Server, which have nothing to do with this discussion. And those differences are the reasons is should be used with care and why it is seldom needed. Choosing the correct one has nothing to do with the number of lines. And there is very little in programmng that should be based solely on line count. (It isn't even a good developer productivity metric.)
unclejimbob wrote:
Indidivuals who come in after the fact and attempt to rate your contribution based upon their own close-enough-is-good-enough two-second evaluation are, of course, putting you down - don't accept it
This site is set up specifically to allow rating answers. Especially on technical questions. That is what the "Rate this message" is for. And one need not even explain why they give it a good or bad rating. Moreover I think that any technical site that doesn't allow and expect discourse on the correctness of answers is probably worthless.