Custom Sequence Generator

Transact-SQL
1

Hi Guys,

I have some questions that are all related and I am hoping someone can help.

Firstly, is it possible to create a sequence generator using parameters in the create statement? I would like to be able pass the sequence name start value increment value and max values as parameters.

Secondly is it possible to customize the sequence generator to use letters as well as numbers.

The reasons for these questions are because the application I am writing uses a sequence generator that I created and I would like to see if I can modify it to use a sequence generator instead of keeping track of these counters in a table.

The scenario is this, the key field in my primary table is a four position text field. The first character is the first letter of a card name, the remainder of the key is a sequence number. The start value is 1 and the max value is 999. The name of the sequence is TableName_Letter. As an example. Card Name starts with A and Table Name is tblCards. Sequence Name is tblCards_A and key looks like A001.

When the counter portion of the sequence maxes out the prefix changes to AA and max value becomes 99. This process continues until the key eventually reaches AZZZ and at that point the sequence has been maxed out.

I see the possibility for this to work using sequences if I can just work out a few details. The first being creating the sequence using parameters The second being keeping track of the rolling prefixes. I'm thinking that can be done in the name of the sequence itself because every time the prefix gets modified it would require dropping the current sequence and creating a new one with new starting value and new max value. For instance the first roll over for a sequence starting with A would be AA it's start value would be 1 and it's max value would be 99 so it's name could be tblCards_AA.

I hope my explanation is clear enough. Any assistance you can provide is appreciated.

2

I don't have all the answers but I may have a few.

There's a SEQUENCE object in SQL Server that may do some of what you want. But I don't think that's the right solution.

Here's an article from the site on building a custom sequence generator: http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server. That has some clever stuff in it.

I'm guessing your thinking of some type of table to hold the various sequences and next values. I would think that could be easily accomplished if you can get one working. Just store the various components of them and then have a computed column which is the last value generated or some such. A simple UPDATE statement gives the next value.

3

Thanks for your reply. Yes I already have a working set of code that accomplishes everything. My goal was to use the built in sequences available in Server to accomplish what my procedure does in conjunction with a set of stored procedures to implement my Key generator. My hope was to be able to eliminate my Counters Table from my database and gain some much needed experience using the various aspects of what Server has to offer. I'm a fairly accomplished database designer and have used Access and Oracle in the past but my experience with Server is basically book knowledge. The key gen code for my project world be a nice test case for using Stored Procedures and Sequences and would eliminate a large piece of client-side code. I will certainly look at the link you provided and thanks again.

4

You could also just use a single id "code" value and store the actual pre-generated key values in another table. That is:

key_id_value = 1, key_value = 'A001'
key_id_value = 2, key_value = 'A002'
key_id_value = 999, key_value = 'A999'
key_id_value = 1000, key_value = 'AA01'
...

5

Thanks for the suggestion but that doesn't accomplish the goal of removing the Counters Table from the database. As a matter of fact it adds another layer of complexity, I appreciate the suggestion though

6

I understand what you're saying, but your current method is going to be a much bigger nightmare. Because at some point you're going to realize that you don't want to use letters like "I" and "O" because they look so much like numbers (thus why those letters don't appear in a VIN, for example). Then you'll have to really complicate the code to skip those.

7

Tangent:

Some time ago we had to generate a random passcode, comprising letters and numbers, and to avoid Number/Letter misreadings we just changed I->J, O->P, S->T, 0->2, 1->3, 5->6 ... (it only had to be moderately random, and not unique) but we then checked for the possibility of rude words ... that caused us to remove all vowels (including Y) ... but probably letter-number-letter-number-... would be "safe" (with look-alike Number/Letters substituted)

8

Excellent point as well -- I had overlooked the possibility of "rude" words.

9

My use of letters is intentional. As I stated in my post the first position of the key is the first letter of a card name. That is what I want because the key maintains a relationship to the record it represents. I was taught in college not to use "magic numbers" as keys. That a key should represent the data in some way. I'm happy with my current process. I'm simply trying to find a way to make the key generation process a server-side process instead of a client-side process. Thanks for the suggestion though..

10

what is tblCards_AA, tblCards_A etc, are you actually creating new tables dynamically?

We had to do custom sequence generator for some manufacturing data and what we ended up doing was pre-populating a Sequence table with all possible values

11

Read my original post everything is explained there.. My app currently uses a table to mimic sequences with a few customized behaviors.