I need to have a identity/sequence/counter of some sort reset annually. What is the best way of doing this automatically (i.e. I don't want the front end application calling a stored procedure or executing SQL to do this).

If it matters the database will be at least SQL Sever 2012.

I will probably be using an identity column, but if there is a better way to do it with sequences instead, I am certainly open to that.

  • Is this for only 1 table?? And why can't you make a job with DBCC CHECKIDENT (Your_Table, RESEED, 0) to do it??
    – Racer SQL
    Mar 20 '15 at 17:59
  • Either can be reset easily, but check Sequence vs Identity for some reasons why a sequence is more flexible overall. Mar 20 '15 at 18:42
  • @RafaelPiccinelli: I find a job somewhat clumsy and am afraid of errors such as the job not being ran because the server happens to be down at the wrong time. If that's the best that can be done, that is the best that can be done, but I am hoping a better solution exists.
    – jmoreno
    Mar 21 '15 at 1:30
  • @jmoreno Something has to execute to cause the sequence to reset. Since you want this to happen at a particular time it has to be on a schedule. The cannonical way to achieve a scheduled action within SQL Server is an Agent job. OK, so the agent server may be down at that time. So may your database. What do you do then? You could log when a reset occurs, run the job daily and only actually reset when the previous reset was more than 365 days ago (or whatever's appropriate). Then the process heal when the system has recovered. Mar 23 '15 at 10:07
  • @MichaelGreen: with a day where all of the values are wrong.
    – jmoreno
    Mar 23 '15 at 17:29

You could make a SQL Agent Job to use DBCC CHECKIDENT with the RESEED option at the right moment.

  • 2
    One sentence answer? You might want to flush that out a bit with an example...
    – Hannah Vernon
    Mar 20 '15 at 18:45

As an alternative to the identity/sequence column, a column with a default value (say -1) can be used to identify records to update inside a trigger. This allows full control of the value, with the draw back that it will be slower and has to be carefully written if you want to allow multiple inserts at the same time.

Unlike identity columns (but like sequence columns) the value can be changed after the fact with a simple update, which is both what allows this and a potential drawback.

I thought of using a default constraint with a user defined function, and while I believe that would have worked, using a trigger allows me to satisfy an additional end user wish without really writing any extra code. To bad the function in a default constraint can't take a parameter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.