Click here to Skip to main content
Click here to Skip to main content
Go to top

Using SQL trigger to create unlimited unique id's

, 29 Sep 2005
Rate this:
Please Sign up or sign in to vote.
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' 
       
or
                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:

                 
---------- 
0000000001
0000000002
0000000003
0000000004
0000000005
0000000006
0000000007
0000000008
0000000009
000000000a
000000000b
000000000c
000000000d
000000000e
000000000f
000000000g
000000000h
000000000i
000000000j
000000000k
000000000l
000000000m
000000000n
000000000o
000000000p
000000000q
000000000r
000000000s
000000000t
000000000u
000000000v
000000000w
000000000x
000000000y
000000000z
0000000010
0000000011
0000000012
0000000013
0000000014
0000000015
0000000016
0000000017
0000000018
0000000019
000000001a
000000001b
000000001c
000000001d
000000001e
.
.
.
.
      

 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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Vincegee

United States United States
No Biography provided

Comments and Discussions

 
GeneralMy vote of 1 PinmemberAntony M Kancidrowski12-Apr-10 4:18 
QuestionWhy? Why? Why? PinsussAnonymous5-Oct-05 10:00 
AnswerRe: Why? Why? Why? PinmemberVincegee6-Oct-05 1:07 
GeneralRe: Why? Why? Why? PinsussAnonymous7-Oct-05 4:41 
GeneralRe: Why? Why? Why? PinmemberVincegee7-Oct-05 5:03 
GeneralNever said you had to use it... PinmemberVincegee29-Sep-05 6:18 
GeneralThat's a lot of email PinsussAnonymous29-Sep-05 6:10 
GeneralRe: That's a lot of email PinsussAnonymous29-Sep-05 8:11 
GeneralRe: That's a lot of email PinmemberPedroMC4-Oct-05 22:40 
GeneralRe: That's a lot of email Pinmembericymint317-Oct-05 11:30 
GeneralRe: That's a lot of email Pinmembericymint317-Oct-05 11:33 
GeneralRe: That's a lot of email PinmemberVincegee18-Oct-05 2:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 29 Sep 2005
Article Copyright 2005 by Vincegee
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid