2

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

3
  • How often do you get new customers?
    – Vérace
    Aug 5 '15 at 8:10
  • @Vérace unfortunately I cannot say how often new customers will be added to the system. I'm expecting a lot of insertions during the first months of usage and then it will probably drop to a relatively lower amount. Are you asking this to determine the chance of having a conflict?
    – Zeep
    Aug 5 '15 at 8:59
  • Yes - for some people, 10 customers a day would be huge (car dealer), but not for Walmart! :-). Try Googling "sql server gapless sequences" - I saw a few things which might be of interest to you there.
    – Vérace
    Aug 5 '15 at 11:16
1

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.

1
  • I won't be using the -T272 startup parameter because I don't want to mess around too much with the SQL server. But you're right, I'll just store the ID myself. Thanks.
    – Zeep
    Aug 11 '15 at 11:44

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.