Using SQL trigger to create unlimited unique id's

VincentGee1

2.56/5 (15 votes)

Sep 29, 2005

2 min read

87619

Using SQL trigger to create unlimited unique id's

Introduction

This MS SQL Store Procedure solves a problem which is not common except when you have a table in a database which each row needs to be uniquely identified and their are more rows in the table than you can identfy with a big int or Unique Identifier.

So for example,

if you used a "unique identifier" you would be limited to 8.6904152163272468261061026005374e+50 unique rows at best.
If you used a "Big Int" you would be limited to -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).

This method will allow you to have 2.2528399544939174411840147874773e+106. (With cluster indexing the identity field.)
or, 4.722366482869645213696e+129 (Without indexing the identity field)

Why would you need that many unique values? Well, the reason for this invention is due to the need to track every email that an application sends for Sarbanes/Oxley Requirements. With this technique, every email sent out will be uniquely identified for a very very very long time.

Background

This design uses basic counting methods and handles the limitations of MS SQL TSQL. First, you can use a varchar(4000) as the unique id column but the issue with this is that as of MSSQL 2000, the largest indexable field is 900 character. So if you need to be able to quickly search the table by key, or clustered keys, you need to limit your key column with to 900 characters, otherwise if you use a varchar(4000) make sure when searching the table you create a temporary table, select the subset into it and search that.

Using the code

First, copy and paste all the TSQL into a Query Window and compile it in the database you wish to use it in.

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


/**********************************************************************************
Program: ALTER  Unlimited Unique ID's (Auto Increment)
Programmer: Vince Gee
Date:        9/28/2005
Parameters:
                @TABLE_NAME - The name of the table to establish the auto incrementing field in
                @COLUMN_NAME - The column name in the table to establish the auto incrementing field in
                @DROP_EXISTING_TRIGGER - Whether or not on running to drop an existing trigger with
                                             the same name.
Theory:
            A varchar 900 field will be able to have 2.2528399544939174411840147874773e+106 unique identifiers in it.

            A uniqueID only has 8.6904152163272468261061026005374e+50 unique identifiers in it.

Description:
            The purpose of the sql procedure is to automate the creation of 
            auto updating identities on a sql table without the trouble of
            writing the trigger each time.  

            So what does this do?  Well for example lets say we have the following 
            table which you will have many many many rows in.

            ALTER  TABLE [Countertest] 
                (
                [myKey] [varchar] (900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [anyvalue1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [anyvalue2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [AA] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
                )
            
            myKey is the unique identifier for each row.  We can set it's size really 
                    to anything, This proc will look for the column specified and determine it's
                    size.  The column should be nvarchar of type

            All the other columns don't matter, the only issue is if all the column names concated 
            together exceed the storage compacity w/ the trigger code of 4000 characters.  If this
            is the case your gonna have to write the trigger manually.

            So to set the auto incrementing field up you would call this proc:
                Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey' 
            or
                Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
            
Output:
            When data is inserted into the table, the auto incrementing field will look like
                                    0000000001
                                    0000000002
                                    0000000003
                                    0000000004
                                    0000000005
                                    0000000006
                                    0000000007
                                    0000000008
                                    0000000009
                                    000000000A
                                    000000000B
                                    000000000C
                                    000000000D
                                    000000000E
                                    000000000F
                                    000000000G
                                    000000000H
                                    000000000I
                                    000000000J
                                    000000000K
                                    000000000L
            with how many 0's set up etc.  It goes 0-9, then A-Z


***********************************************************************************/

ALTER PROC SP_SET_UNIQUE_FIELD
            @TABLE_NAME VARCHAR(255),
            @COLUMN_NAME VARCHAR(255),
            @DROP_EXISTING_TRIGGER BIT =0
AS

DECLARE 
    @EXECSQLSTRING nvarchar (4000),
    @counter int,
    @COLUMN_NAMES varchar(4000),
    @tCOLUMN_NAME varchar(4000),
    @MAXORDINAL int,
    @KEYLENGTH int

--If the trigger 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[' + 'IO_Trig_INS_' + @COLUMN_NAME + ']') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
    begin
        IF @DROP_EXISTING_TRIGGER = 0
            BEGIN
                -- USER DOESN'T WANT US TO AUTODROP THE TRIGGER, BY DEFAULT AUTODROP TRIGGER IS OFF
                PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
                PRINT 'STOP ERROR :: PLEASE DROP THE EXISTING TRIGGER BEFORE RUNNING THIS PROC'
                PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
                RETURN
            END
        ELSE
            BEGIN
                --CREATE A SQL STRING TO DROP THE TRIGGER SO WE CAN RECREATE IT.
                set @EXECSQLSTRING = 'drop trigger IO_Trig_INS_' + @COLUMN_NAME
                --EXECUTE THE SQL
                EXEC SP_EXECUTESQL  @EXECSQLSTRING
            END
    end


--CREATE A TABLE TO HOLD THE RESULTS FOR THE SP_COLUMNS
create table #temp
    (
    TABLE_QUALIFIER varchar(255),
    TABLE_OWNER varchar(255),
    TABLE_NAME varchar(255),
    COLUMN_NAME varchar(255),
    DATA_TYPE int,
    [TYPE_NAME] varchar(255),
    [PRECISION] int,
    LENGTH    int,
    SCALE int,
    RADIX int,
    NULLABLE int,
    REMARKS  varchar(255),
    COLUMN_DEF  varchar(255),
    SQL_DATA_TYPE int,
    SQL_DATETIME_SUB varchar(255),
    CHAR_OCTET_LENGTH int,
    ORDINAL_POSITION    int,
    IS_NULLABLE  varchar(255),
    SS_DATA_TYPE int
    )
--POPULATE THE TEMP TABLE W/ A SP_COLUMNS ON THE TARGET TABLE
insert into #temp
exec sp_columns @TABLE_NAME

--CYCLE THROUGH ALL THE COLUMN NAMES AND BUILD OUR COLUMN NAME STRING 
--FOR INSERTS.  THE LAST COLUMN NAME IS ALWAYS THE IDENTITY FIELD.
SELECT @MAXORDINAL = MAX(ORDINAL_POSITION) FROM #TEMP
SET @COUNTER = 1
SET @COLUMN_NAMES = ''
WHILE  @COUNTER <= @MAXORDINAL
    BEGIN
        select @tCOLUMN_NAME = COLUMN_NAME FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER
        if (@tCOLUMN_NAME <> @COLUMN_NAME)
            begin
                SET @COLUMN_NAMES = @COLUMN_NAMES  + @tCOLUMN_NAME+ ','
            end
        else
            begin
                select @KEYLENGTH = LENGTH FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER
            end
    SET @COUNTER = @COUNTER +1
    END
--CLEAN UP
drop table #temp


IF @KEYLENGTH > 900    
Begin
    PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
    PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!WARNING:: YOU WILL NOT BE ABLE TO INDEX THIS TABLE BY YOUR CHOSEN COLUMN,!!!!!!!!!!!!!!!!!!!!!'
    PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!BECAUSE THE COLUMN IS OVER 900 CHARACTERS.  900 CHARS ARE THE MAX THAT  !!!!!!!!!!!!!!!!!!!!!'
    PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!THAT CAN BE INDEXED                                                     !!!!!!!!!!!!!!!!!!!!!'
    PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
    END
SET @EXECSQLSTRING = '
CREATE TRIGGER IO_Trig_INS_' + @COLUMN_NAME + ' ON ' + @TABLE_NAME + '

INSTEAD OF INSERT

AS

BEGIN

DECLARE 
        @VALUE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
        @REVERSED_VALUE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
        @COUNTER INT,
        @LEFT_SIDE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
        @RIGHT_SIDE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
        @CHAR_VALUE CHAR


select @VALUE = ISNULL(MAX(' + @COLUMN_NAME + '),REPLICATE (' + "'0'" + ',' + CONVERT(VARCHAR(900),@KEYLENGTH) + ') ) from ' + @TABLE_NAME + '

SET @REVERSED_VALUE = REVERSE(@VALUE)

SET @COUNTER = 1
 
 WHILE @COUNTER <= LEN(@REVERSED_VALUE)

     BEGIN

        SET @CHAR_VALUE = SUBSTRING(@REVERSED_VALUE,@COUNTER,1)

        IF ASCII(@CHAR_VALUE) <> 122

                BEGIN

                    IF @COUNTER = 1

                        SET @LEFT_SIDE = ''''

                    ELSE
                        SET @LEFT_SIDE = LEFT (@REVERSED_VALUE,@COUNTER - 1)

                    IF @COUNTER = LEN(@VALUE) 

                        SET @RIGHT_SIDE = ''''

                    ELSE 
                        SET @RIGHT_SIDE = RIGHT (@REVERSED_VALUE,LEN(@REVERSED_VALUE)- @COUNTER)

                    IF ASCII(@CHAR_VALUE) +  1 = 58

                        SET @CHAR_VALUE = CHAR(97)

                    ELSE

                        SET @CHAR_VALUE = CHAR(ASCII(@CHAR_VALUE) + 1)

                    SET @REVERSED_VALUE = ISNULL(@LEFT_SIDE,"") + ISNULL(@CHAR_VALUE,"") + ISNULL(@RIGHT_SIDE,"")

                    BREAK
                END
            ELSE
                BEGIN
                    IF @COUNTER = 1

                        SET @LEFT_SIDE = ''''

                    ELSE 

                        SET @LEFT_SIDE = LEFT (@REVERSED_VALUE,@COUNTER - 1)

                    IF @COUNTER = LEN(@VALUE) 

                        SET @RIGHT_SIDE = ''''

                    ELSE 

                        SET @RIGHT_SIDE = RIGHT (@REVERSED_VALUE,LEN(@REVERSED_VALUE)- @COUNTER)
            
                    SET @CHAR_VALUE = CHAR(48) --SET THE CURRENT POSITION TO ZERO AND WE INCREMENT THE NEXT DIGIT.

                    SET @REVERSED_VALUE = ISNULL(@LEFT_SIDE,"") + ISNULL(@CHAR_VALUE,"") + ISNULL(@RIGHT_SIDE,"")
                END

        SET @COUNTER = @COUNTER +1
     END 


SET @VALUE = REVERSE (@REVERSED_VALUE)

    INSERT INTO ' + @TABLE_NAME + ' (' + @COLUMN_NAMES + @COLUMN_NAME + ')

        SELECT 

                ' + @COLUMN_NAMES + '@VALUE

        FROM

            inserted

END'
if len(@EXECSQLSTRING) <4000
    begin
        EXEC SP_EXECUTESQL  @EXECSQLSTRING
    end
else
    begin
        print 'STOP ERROR:: BUFFER OVERFLOW.  THE GENERATED TRIGGER TEXT > 4000, Trigger must be hand written.'
    end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

First, to test the functionality create a temp table.

              Create  TABLE [Countertest] 
                (
                [myKey] [varchar] (900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [anyvalue1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [anyvalue2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [AA] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
                )

Second, call the proc on the table. Parameters are:

Table Name - Name of the table to put the trigger on.
Column Name - Name of the column to use as the key.
Drop Existing Trigger - If this is set to 1 and a trigger with the name generated by this stored procedure exists it will drop it. (OPTIONAL)

                Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey'

                Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1

Now, we are going to test how this works. Copy and paste the following code into a query analyzer.

      
declare @t int
set @t = 0

while @t <= 40000
begin
insert into countertest 
select '','s','s','s'
set @t = @t + 1
end
GO

Once this completes, you can inspect the unique id by selecting it from the table

      SELECT RIGHT (MYKEY,10) FROM countertest

you should see:

The table will keep incrementing the key column first 0-9 then a-z. When it reaches all 'z' it will roll over, but if your key column is wide enough this shouldn't happen while your still employeed.

Also, the stored procedure figures out how wide the key column is automatically and adjusts the script accordingly. So if you widen or shrink the key column, just rerun the proc to update the trigger script.