SQL Server does not really have an auto-incrementing field that ensures sequence. IDENTITY is an auto-incrementing column that assures uniqueness but does not assure full sequence. If you delete a row in a table, the value of the IDENTITY column will not be reused.
If you don't mind some gaps in your sequence, use an IDENTITY column to have your unique number and use a COMPUTED column to store whatever other pre/post-fix string you need attached to the counter.
However, if you need it to be fully sequential, you have to create a code to enforce the sequence. I would suggest a transaction processing inside a trigger.
Happy coding!
-- CK
|