Unique row constraint in SQL Server

Question

I have the following table

CREATE TABLE [dbo].[LogFiles_Warehouse](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [timestamp] [datetime] NOT NULL,
    [clientNr] [int] NOT NULL,
    [server] [nvarchar](150) COLLATE Latin1_General_CI_AS NOT NULL,
    [storeNr] [int] NOT NULL,
    [account] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [software] [nvarchar](300) COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [PK_Astoria_LogFiles_Warehouse] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And want to avoid having duplicate rows in my table. I thought about creating a UNIQUE index on the complete table, but then SQL Manager Studio tells me that this is not possible because the key would be too large.

Is there another way I could enforce unique rows over all columns, apart from indexes?

Answer 1

Create a UNIQUE index on hashed values:

CREATE TABLE [dbo].[LogFiles_Warehouse]
        (
        [id] [int] IDENTITY(1,1) NOT NULL,
        [timestamp] [datetime] NOT NULL,
        [clientNr] [int] NOT NULL,
        [server] [nvarchar](150) COLLATE Latin1_General_CI_AS NOT NULL,
        [storeNr] [int] NOT NULL,
        [account] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
        [software] [nvarchar](300) COLLATE Latin1_General_CI_AS NOT NULL,
        serverHash AS CAST(HASHBYTES('MD4', server) AS BINARY(16)),
        accountHash AS CAST(HASHBYTES('MD4', account) AS BINARY(16)),
        softwareHash AS CAST(HASHBYTES('MD4', software) AS BINARY(16))
        )

CREATE UNIQUE INDEX
        UX_LogFilesWarehouse_Server_Account_Software
ON      LogFiles_Warehouse (serverHash, accountHash, softwareHash)

Answer 2

Use triggers + a smaller non unique index over the most distinguishing ields to helop aleviate the table s can problem.

This goes down a lot into a bad database design to start with. Fields like Software, Account do not belong into that table to start with (or if account, then not client nr). Your table is only so wisde because you arelady violate database design basics to start with.

Also, to abvoid non unique fields, you have NT to have the Id field in the unique testing otherwise you ont ever have doubles to start with.