Author: Yaheya Quazi

I am sure you have visited website where you submit an order and they give you an alphanumeric order confirmation number such as FDF245. Have you ever wondered how this is done? Well, I have. And I built a small system (although I have not tested it thoroughly) that I think should work.

Let's Begin. We will Use SQL server and trigger to accomplish what we are trying to do. As you may know if you use a field in SQL server and its type is identity it can only generate numeric sequence. Such as 1, 2, 3 etc. Given a seed value (a start value) you can however start your identity field at any numeric value and increment it as you wish for example, I could have an identity field that I want to start at 100 and I want to increment 10 each time a new row is inserted. Keep in mind, identity field is a small integer type therefor there is limitation as how large this number can be.

Ok, so Identity field is not serving the purpose here. Because Identity field will generate sequential integer values only. But not alphanumeric such as the example stated above. We therefor will trick SQL server to generate an alphanumeric unique key that simulates the order number that we usually get at various web site.

First we create a table, below is the script to create this table

[Code Block]

CREATE TABLE [dbo].[tbl_custom_identity] (
 [Increment] [int] IDENTITY (1, 1) NOT NULL ,
 [Custom_Key] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [field_value] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_custom_identity] WITH NOCHECK ADD
  PRIMARY KEY  CLUSTERED
 (
  [Increment]
 )  ON [PRIMARY]
GO

The above script creates a table that has the following field items

Increment (identity)
Custom_key(char)
field_value(char)

Increment Field: This is the field that will carry our identity field and the identity numeric value. For our purposes we added a seed value of 1 and increment of 1 for the identity field

Custom_key: This field will be updated by a trigger which we will see shortly.

Field_value: is just a field where you can store information, you can add more fields to store (for example name, address)

Now that the table is created, we would create an insert trigger. Here is the code for the insert trigger -

[Code Block]

create trigger i_customidentity on tbl_custom_identity for INSERT
as
update tbl_custom_identity
  set Custom_Key = CHAR (ASCII ('A') + ((Increment - 1)/ (26 * 26)) % (26 * 26 * 26)) +
                                CHAR (ASCII ('A') + ((Increment - 1) / 26) % (26 * 26)) +
                                CHAR (ASCII ('A') + (Increment - 1) % 26) +
                                RIGHT('00' + cast(110 + Increment as varchar), 3)
where Increment = @@IDENTITY
go

The above trigger upon inserting a new record will update the custom_key field with values as AAA111, AAA112 and so on!

To test this script open your table in SQL enterprise manager and type something in the field_value field. Move your cursor to the next row and you will see the custom_key value field has the value AAA111 (if you done everything properly)

I do not recommend this for SQL server novices. Only experienced SQL server users or DBAs should use it or experiment with it more. If you find a bug and/or have suggestion please write to me (Yaheya Quazi) 

Hope you liked the trick!

Have a nice evening.