As already stated, this is a very bad idea because of several reasons and I might be missing a couple...

1. First of all, by definition, a "column" in a table is supposed to represent one and only one attribute of the rows in the table. The method you are trying to use combines the two attributes of "location" and "row identifier".

2. If, as I've seen so often happen, the designed scope of the row identifiers (ie, range of numbers for each location) is exceeded, either you're dead in the water or additional special handling to include another range of row identifiers must be designed and deployed.

3. In general, it makes the handling of the data a huge pain because, as you're finding out, it's difficult to automatically increment such mixed numbers.

4. Despite your best efforts, you will end up with gaps in the sequence even if you don't use this method. Don't ever count on their not being gaps except in very instances where the table is never added to such as a Tally, Numbers, or Calendar table or, possibly, a predefined serial number sequence table (which is just plain ineffecient).

The best and correct way to do this would be to, indeed, have two separate columns for "location" and "row identifier". From there, you could have a "calculated" or "computed" column in the table to concatenate the two values.

However, if you are unable to convince the designers that combining location and row identifier in a single column is a woeful mistake, then do the next best thing... create a separate table for each location with a constraint on the LOCATION column. That way, you can create an updateable view known as a "partitioned view" to do Inserts, Updates, Deletes, and Selects through as if all the tables were a single table. There are actually some benefits to the maintenance of such tables and partitioned views like smaller indexes to maintain, etc.

In each of the tables belonging to the partitioned view, you would seed the IDENTITY column with the starting sequence number for the given location and, of course, location would be a separate column. The only place where you need to combine the IDENTITY column and the Location column, would be in the actual view itself because the view would, in fact, use the correct indexes on those two columns in any lookups you may play against the combination of the two.

Again, I think this is a mistake, but it is sometimes very difficult and even career threatening to convince people like designers (who are supposed to know better), that they've made a terrible mistake. 😛