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