We're currently building an application where customers need to have 1 unique barcode. Each time a new customer has been added to the system, a new unique barcode needs to be generated for that customer.
We're using 12 digits for this barcode, but the first 6 digits are fixed, leaving me with only 6 digits left to generate a unique number.
At first I wanted to do this based on the primary key of the table customer, which is an identity column. And this would have worked prior to SQL Server 2012 because apparently, whenever the SQL Server 2012 service gets (forcefully) restarted, the identity column jumps by 1000 or 10000 (depending on the datatype of your column), which would skyrocket the amount of digits being used, risking to exceed the 6 digit limit. Same with the Sequence functionality.
Can anyone point me out how you would do it? I've been thinking of storing a number myself in a column with a unique constraint and add 1 to it each time a customer has been added but this makes me feel very uncomfortable.
Thanks for reading this far, any advice is appreciated.
Zeep
Best Answer
Why do you feel uncomfortable storing the id yourself? This is a valid approach for this problem.
You could also use -T272 as a startup parameter to prevent the gaps during id generation. I read that this could have impact on the performance of the id generation, but I never had no problems in my tests. See this link for further information: http://www.dfarber.com/computer-consulting-blog/2014/2/13/how-to-solve-identity-problem-in-sql-2012.aspx
Please consider that there are many different types of barcodes. If you use the common GS1-128 for example, you can create alpha-numeric codes to increase the possible combinations and prevent an overflow.