I’m Working to create a simple repair log serial number. I would like to create a unique code for each repair but I don’t want the numbers continuing on forever, I would like to use Zone(department), Year and Number to identify each repair. I’m Using SQL server 17.
I know I can concatenate the fields together to create the number but I’m not sure how to reset the number at the start of the year?
(picture to help describe)
Thanks!
Advertisement
Answer
If you want a unique number for each row in the table, then use an identity primary key. It will not reset at each year. It might have gaps — say when a row is deleted. But it is the most efficient way to accomplish what you want.
If you try to create your own value on input, then essentially you have to lock the entire table while you calculate the next number. This locks out other updates and inserts into the table, so it is an expensive operation. If you do not do this, you will be subject to race conditions, where two different threads might be assigned the same number.
An identity column may not be aesthetically pleasing, but it is really the best solution for identifying rows in a table.