Create a Unique ID With MS SQL Server Using A Base33 Stored Procedure

Ever want to create a unique ID for a record in Microsoft’s SQL Server? I do all the time but I hate using boring sequential numbers. And I believe that using auto-numbers can place some security risks just because people can ‘try’ numbers around any number they have.

Why Do This?

Oh sure, you obviously put other security measures in place but this method can easily add a little more difficulty for anyone trying to ‘guess’ a transaction id. And every little bit helps.

So to spice things up a bit and come up with something a bit more difficult to guess, I use a “Base 33″ code that creates a unique ID using the current ‘timestamp’ down to the milliseconds. It uses the numerals 0-9 and capital letters from A-Z *EXCEPT* the letters “I”,”O”, and “X”.

(I don’t use “I” and “O” due to the possiblility of mistaking an “I” for a “1″ or a “O” for a “0″ (zero) and I don’t use “X” just because I frequently ‘reserve’ “X” for other things in my code elsewhere.)

And you can easily adapt this for a “Base 36″ ID and include them if you wish but for this article, we will work with “Base 33″.

The Concepts

The concept is based on starting with a very large number created by combining the year (4 digits), month (2 digits), day (2 digits), hour (2 digits), minute (2 digits), second (2 digits), and millisecond (3 digits) that the request is made.

So unless you have an extremely busy site (or you encounter the extremely unlikely chance that 2 transactions occur at the **exact same millisecond) we end up with a 17-digit number that should be unique. That is a good place to start when looking for a unique ID.

(**OK, OK. I know purists will argue that Microsoft SQL Server does not get the exact millisecond but rather the last digit of the milliseconds will be either ’0′, ’3′ or ’7′ but this should be fine for all but the most demanding situations.)

The Code

So without further delay, let’s see some code. Here is the ‘meat’ of the ID creator – an MS SQL function. The function can be created like this:

And it can be used in an SQL query like this:

The ‘funcGetBase33′ is the actual function that converts the big integer into the “Base 33″ code and in this example, I am creating the big integer using ‘funcGetNowAsInt’.

**NOTE: ‘funcGetNowAsInt’ is shown further down the page but essentially grabs all the ‘parts’ of the timestamp (year to millisecond) and ‘pads’ them with 0′s if necessary to get the 17-digit number representing the timestamp down to the millisecond.

Operation Overview

So the overview of what this function does is essentially this:

1. First, figure out the remainder of the bigint ‘timestamp’ when dividing by 33.

2. Then get the character in the ‘remainder position’ of the string ’0123456789ABCDEFGHJKLMNPQRSTUVWYZ’.

3. Then subtract the remainder from the bigint and then divide by 33.

4. Repeat with the result until you only have a remainder.

The Detailed Walk-Thru

Now let’s walk thru the stored procedure using a real value. We’ll use the SQL query “SELECT dbo.funcGetBase33(dbo.funcGetNowAsInt()) AS MyCodeString” and for this example we’ll run this shortly after 7 PM on Nov 27th, 2011 – 29.437 seconds after 7 PM to be exact.

First we get the bigint that represents the timestamp using dbo.funcGetNowAsInt (see additional stored procedure below to see that function) and we get a bigint of 20111127190029437. That represents the timestamp Nov 27, 2011 7:00:29.437 PM in a ‘modified’ bigint.

We pass that bigint to our funcGetBase33 function. After declaring our variables in the procedure, we start the looping using:

This checks to see if we have reached the last item yet (if our @intNumber is less than 0). If we reached the last item, then get the string character and end the function.

If we are greater than 33, then we find the remainder after dividing by 33 using:

We then find the string in the ‘remainder + 1′ position of our @strBase and put that character in the FRONT of our result string using:

So for our first iteration the calculation looks like:

20111127190029437 / 33 = 609428096667558 remainder 23

Our first (which is actually the last) character of our result is in the 24th position of @strBase (’0123456789ABCDEFGHJKLMNPQRSTUVWYZ’) which is ‘P’.

Then we subtract the remainder from our number and divide by 33 to get our new number using @intNumber = (@intNumber – @intRemainder) / 33. This gives us the new number to use for our second iteration – 609428096667558.

The second iteration calculation looks like this:

609428096667558 / 33 = 18467518080835 r 3

and the 4th (3 + 1) character of @strBase is ’3′. So we put ’3′ in the front of our @strResult. Now our @strResult looks like ’3P’.

We repeat loops until our new number is less than 33, then grab that character and then exit the loop returning @strResult.

Make sense? The full calculation loops look like this:

20111127190029437 / 33 = 609428096667558 r 23 (@strBase pos 24 is ‘P’ – @strResult = ‘P’)
609428096667558 / 33 = 18467518080835 r 3 (@strBase pos 4 is ’3′ – @strResult = ’3P’)
18467518080835 / 33 = 559621760025 r 10 (@strBase pos 11 is ‘A’ – @strResult = ‘A3P’)
559621760025 / 33 = 16958235152 r 9 (@strBase pos 10 is ’9′ – @strResult = ’9A3P’)
16958235152 / 33 = 513885913 r 23 (@strBase pos 24 is ‘P’ – @strResult = ‘P9A3P’)
513885913 / 33 = 15572300 r 13 (@strBase pos 14 is ‘D’ – @strResult = ‘DP9A3P’)
15572300 / 33 = 471887 r 29 (@strBase pos 30 is ‘V’ – @strResult = ‘VDP9A3P’)
471887 / 33 = 14299 r 20 (@strBase pos 21 is ‘L’ – @strResult = ‘LVDP9A3P’)
14299 / 33 = 433 r 10 (@strBase pos 11 is ‘A’ – @strResult = ‘ALVDP9A3P’)
433 / 33 = 13 r 4 (@strBase pos 5 is ’4′ – @strResult = ’4ALVDP9A3P’)
13 / 33 = 0 r 13 (@strBase pos 14 is ‘D’ – @strResult = ‘D4ALVDP9A3P’)

So for this particular timestamp that returns a bigint of 20111127190029437 , we get the string ‘D4ALVDP9A3P’ for our Unique ID.

Conclusions

I find I use this function quite often for a variety of reasons and it is really not that difficult once you plop this into your MS SQL Server instance. And if you think about it for a minute, I am sure you can come up with a few places to use it yourself.

If nothing else, it will get you thinking a bit more creatively when using Unique ID’s in your SQL Server applications and stored procedures.

The Additional Info

Here is the function to convert the ‘timestamp’ to a 17-digit bigint based on the year (4 digits), month (2 digits), day (2 digits), hour (2 digits), minute (2 digits), second (2 digits), and millisecond (3 digits).

Reversing It

And, of course, here is a function to reverse the whole thing. For instance, if for some reason we want to find out when this ID was created we can call the below function using a query like this:

SELECT dbo.funcGetBase33Rev(‘D4ALVDP9A3P’) AS MyCodeString

and it should return our original big int of 20111127190029437.