SQL Server unique set of columns without using a constraint - getting bit by the 900 byte limit

Question

Hello,

I have a table which contains 3 nvarchar(255) columns and a combination of these 3 columns must be unique. Normally I would create a Unique constraint, but in this case, I am hitting the 900 byte limit. Since I have to support SQL Server 2000, I can not use Include columns to get around this situation.

Thanks!

Answer 1

255 seems pretty arbitrary. With that in mind, any chance that nvarchar(150)'s would be adequate? Or some combination that results in <= 900 bytes?

Answer 2

If I understand you correctly, you could use a trigger and on the Insert or Update check to see if the combination of the values is unique prior to making the change. You could also do the same via a stored procedure.

Answer 3

You could hash the 3 columns, using MD5 or SHA1, and create a unqiue constraint on the hashed value.

How easy it is to implement depends on where you INSERTs/UPDATEs come from.

If they only come from your application, then it should be relatively easy to implement.

If they come from multiple sources you can look into implementing the hash in T-SQL. A quick Google search turned up the following implementation of MD5 in T-SQL: http://binaryworld.net/Main/CodeDetail.aspx?CodeId=3600. On SQL2005, you can use the built-in hash function: http://msdn.microsoft.com/en-us/library/ms174415.aspx.

Note that MD5 is no longer considered "secure", though it should be sufficient for this scenario. It runs quicker and is easier to implement than SHA1.

Answer 4

Another idea: could you add a column that stores a hash of those values cat'd together? SQL 2000 supports an extended stored procedure for md5 sums that you could call from a trigger to create the hash.

Answer 5

Was working on this in parallel, but looks like it's an extension of some other ideas already proposed... You should be able to create an indexed view using CHECKSUM to handle this.

As an example:

CREATE TABLE dbo.Test_Unique_Limit (
    string_1 NVARCHAR(255) NOT NULL,
    string_2 NVARCHAR(255) NOT NULL,
    string_3 NVARCHAR(255) NOT NULL )
GO
CREATE VIEW dbo.Test_Unique_Limit_View
WITH SCHEMABINDING
AS
    SELECT string_1, string_2, string_3, CHECKSUM(string_1, string_2, string_3) AS CHKSUM
    FROM dbo.Test_Unique_Limit
GO
CREATE UNIQUE CLUSTERED INDEX Test_Unique_Limit_IDX ON dbo.Test_Unique_Limit_View (CHKSUM)
GO

I haven't used this method in production, so I can't vouch for either its performance or complete accuracy. You'll need to do your own testing there.

One thing that I will point out though... the fact that you're running into this issue should make you take a step back and consider whether or not the model itself is really as good as it should be. A unique index on the columns as you've described seems a bit odd.

Good luck!

Answer 6

I don't have access to SQL 2000 Server so I couldn't test this there, but assuming that it supports scalar functions it ought to work. You could make it faster by adding non-unique indices on each column so that it doesn't have to do a table scan.

CREATE FUNCTION [dbo].[fn_count_rows_for_ids]
(
    @id1 nvarchar(255),
    @id2 nvarchar(255),
    @id3 nvarchar(255)
)
RETURNS int
AS
BEGIN
    DECLARE @count int

    SELECT @count = count(*) FROM tbl_test WHERE (string_1 = @id1 AND string_2 = @id2 AND string_3 = @id3)

    RETURN @count

END

CREATE TABLE [dbo].[tbl_test](
    [string_1] [nvarchar](255) NOT NULL,
    [string_2] [nvarchar](255) NOT NULL,
    [string_3] [nvarchar](255) NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[tbl_test]  WITH CHECK ADD  CONSTRAINT [CK_tbl_test] CHECK  (([dbo].[fn_count_rows_for_ids]([string_1],[string_2],[string_3])<=(1)))

ALTER TABLE [dbo].[tbl_test] CHECK CONSTRAINT [CK_tbl_test]