get next max problem with A999 > A1000

Question

Some product serial numbers are of type varchar and have a letter indicating the type followed by a number. The numbers are 0 padded up to 5 digits and we are approaching 'A100000'.

For the letters that have less than 100000 items they still want 0 padding up to 5 digits, and the letters that have more that 100000 to have more digits up to whatever number it is.

It currently finds the current max serial number something like this:

SELECT MAX(SerialNumber) FROM Table WHERE LEFT(SerialNumber, 1) = @leadingChar

However, using this method 'A99999' is selected over 'A100000' because 9>1. (Same reason Zoo is after Apple in a dictionary, more chars but less value in a leading char.)

So the problem is when it increments the current max serial number to get the next serial number after 'A99999' every time you get the new serial number you will get 'A100000'.

Splitting them to be a char column and int column in the database would be cumbersome since it is used in several tables and there are a few million total serial numbers.

It is written as a web app in vbscript/classic asp database is SQL Server 7.

If there are better tags/title feel free to edit/let me know.

Thanks for any help.

Answer 1

You could try this to get the largest number for the leading character:

SELECT MAX(CAST(SUBSTRING(SerialNumber, 2, 99) AS INT))
FROM Table1
WHERE LEFT(SerialNumber, 1) = @leadingChar

It will be slow though as it won't be able to use an index to find the maximum number. It would be better to split it up into two columns as you mentioned in the question. It might be awkward to make the change but it will help you in the future.

Answer 2

You might consider adopting the approach described by Matt Gibson in the comments on a Coding Horror posting about natural sort order.

As a data warehouse developer, I'm no stranger to the way business people expect to see things sorted, and it's never ASCII... One thing I do is to add extra columns to my databases and put in "corrected" versions of the keys I'm using, so my table might look like:

Customer Number|Customer|SortKey1
MS948|Fred Smith|MS00000948
MS9215|John Star|MS00009215

..which means I get to use SQL's standard ASCII sorting (indexed for speed, if need be) rather than muck around with anything in client applications. The client presents the real key to the user, but sorts by the pre-calculated ASCII-sort-safe key, and I only have to do the hard work once, up front.

That would imply adding an extra field to the current Table, which would only be used for key generation. The 'get next key' code would refer to the new field, but also populate the existing field in order that most of the code, and foreign keyed data, can be left as-is.

You might also consider using the SQL LIKE operator to find the MAX if there is an index on the SerialNumber field. The code you posted may not be able to use the index, as you are applying a function to the field in the WHERE clause (I'm not familiar with the details of SQL Server 7 query optimisation, so can't say for sure).

Answer 3

If you find that your solution is not sufficiently performant, and you want a faster one that requires minimal application changes, you might try this:

Create two columns in your table, SerialLetter and SerialNumber.

Then populate them like this:

UPDATE Table SET 
SerialLetter = SUBSTRING(Serial, 0, 1), 
SerialNumber = SUBSTRING(Serial, 1, 99)

These may be some very long running queries if your table is big, so you may want to do it when load is low, and you may have to adjust your timeout values.

Then create update and insert triggers so that any serials in the table will be reflected in your new columns.

Then create an index on SerialLetter and SerialNumber in that order. (one index on two columns)

Then you can

SELECT MAX(SerialNumber) WHERE SerialLetter = 'X'

in very little time since it will only use the index, and never hit the table.