2

I need to have some how 2 identity columns in one table. One that is normal identity and another one that will reset yearly.

I have done the reset part with the help of DBCC CHECKIDENT ('TestTable', RESEED, 0) in the insert procedure.

But I don't know how to have a normal identity besides this one. Solution may be using two tables but I prefer to have both of them in a single table.

6

It looks like the comments may have answered this for you, but so there's an actual answer:

You cannot have two columns in one table that both have an IDENTITY(n,m) specification.

The most sensible approach for your requirement is probably:

  • your normal always-incrementing identity column should have the IDENTITY specification
  • use a SEQUENCE (see here for reference documentation and here for more general guidance) to control the other column that should reset each year.

You can restart a sequence on demand using

ALTER SEQUENCE <sequence_name> RESTART WITH <new_start_value>;

This command could be issued in a number of ways, for example from a job scheduled to run yearly on your desired reset date.

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.