使用 SQL 触发器创建无限唯一 ID

引言

这个 MS SQL 存储过程解决了一个不常见的问题，除非您的数据库中有一个表，其中每一行都需要唯一标识，并且表中的行数超过了您可以使用 big int 或唯一标识符标识的数量。

例如，

如果您使用“唯一标识符”，您最多只能获得 8.6904152163272468261061026005374e+50 个唯一行。
如果您使用“Big Int”，您将被限制在 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 之间。

这种方法将允许您拥有 2.2528399544939174411840147874773e+106 个。（使用聚集索引标识字段。）
或者，4.722366482869645213696e+129 (不索引标识字段)

为什么您需要如此多的唯一值？嗯，这项发明的理由是需要跟踪应用程序发送的每一封电子邮件，以满足 Sarbanes/Oxley 法规的要求。使用此技术，发送的每封电子邮件都将在很长很长的时间内被唯一标识。

背景

此设计使用基本的计数方法，并处理 MS SQL TSQL 的限制。首先，您可以将 varchar(4000) 用作唯一 ID 列，但问题是截至 MSSQL 2000，最大的可索引字段是 900 个字符。因此，如果您需要能够通过键或聚集键快速搜索表，则需要将键列限制为 900 个字符，否则，如果您使用 varchar(4000)，请确保在搜索表时创建一个临时表，选择子集放入其中并搜索它。

使用代码

首先，将所有 TSQL 复制并粘贴到查询窗口中，并在您希望使用的数据库中编译它。

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

首先，要测试功能，请创建一个临时表。

              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 
                )
      

其次，在表上调用该过程。参数是

表名 - 用于放置触发器的表的名称。
列名 - 用作键的列的名称。
删除现有触发器 - 如果将其设置为 1，并且存在具有此存储过程生成的名称的触发器，则将删除它。（可选）

                Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey' 
       

或

                Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
      

现在，我们将测试它的工作原理。将以下代码复制并粘贴到查询分析器中。

      
declare @t int
set @t = 0

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

完成后，您可以通过从表中选择唯一 ID 来检查它

      SELECT RIGHT (MYKEY,10) FROM countertest
      

 您应该看到

                 
---------- 
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
.
.
.
.
      

 该表将保持递增键列，首先是 0-9，然后是 a-z。当它全部达到“z”时，它将回滚，但如果您的键列足够宽，则在您仍在职时，这种情况不应该发生。

此外，存储过程会自动计算出键列的宽度，并相应地调整脚本。因此，如果您加宽或缩小键列，只需重新运行该过程以更新触发器脚本即可。