sql-server - SQL Server中的唯一行约束

我有下表

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]

并且想要避免在我的表中有重复的行。我考虑过在完整表上创建UNIQUE索引，但是SQL Manager Studio告诉我这是不可能的，因为键太大了。

除了索引，我还有另一种方法可以在所有列上强制使用唯一行吗？

最佳答案

在散列值上创建UNIQUE索引:

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)