I would like to create a unique index, that checks if a combination of text in my table is already there or not. In PostgreSQL I did this with a simple CREATE INDEX:
CREATE UNIQUE INDEX table_unique
ON cd.hdealerproductdata USING btree
(md5((((svId::text || manufacturer::text) || manufacturerreference::text) || path::text) || treetype::text) COLLATE pg_catalog."default")
TABLESPACE pg_default;
How can I do this in SQL Server (2016)? I tried to create a computed column (and add a unique index to that) like this (using SSMS, table designer->Properties->Computed Column Specification):
ISNULL(HashBytes('MD5',CONVERT(VARCHAR(512),CONCAT(Manufacturer,ManufacturerReference))), 'null')
But I got an error saying it cannot be validated.
Edit.: I could even use SHA-2 with HashBytes: https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql
Edit2.: The HashBytes returns varbinary types but I cannot specify a datatype for a computed column.
After:
'Document' table
- Unable to modify table.
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.
Edit3.: I ended up creating a Scalar function for this, and calling it while inserting and creating the computed column (which I persisted and created a unique index on).
CREATE FUNCTION [dbo].[DocumentUniqueHash]
(
@DocumentTreeId bigint,
@Manufacturer nvarchar(255),
@ManufacturerReference nvarchar(255)
)
RETURNS varbinary(20)
WITH SCHEMABINDING
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varbinary(20)
SELECT @Result = (hashbytes('SHA1',(CONVERT([nvarchar](max),@DocumentTreeId)+@Manufacturer)+@ManufacturerReference))
-- Return the result of the function
RETURN @Result
END
And called the Compute column: ([dbo].[DocumentUniqueHash]([DocumentTreeId],[Manufacturer],[ManufacturerReference]))
Also, did the insert stored procedure like this:
CREATE PROCEDURE DocumentInsert
@DocumentTreeId bigint,
@Manufacturer nvarchar(255),
@ManufacturerReference nvarchar(255),
@NewId bigint OUTPUT
AS
BEGIN
SELECT @NewId = Id FROM Document (NOLOCK)
WHERE UniqueHash = [dbo].[DocumentUniqueHash](@DocumentTreeId, @Manufacturer, @ManufacturerReference)
IF @NewId IS NULL
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @NewId = Id FROM Document (NOLOCK)
WHERE UniqueHash = [dbo].[DocumentUniqueHash](@DocumentTreeId, @Manufacturer, @ManufacturerReference)
IF @NewId IS NULL
BEGIN
INSERT INTO Document (DocumentTreeId, Manufacturer, ManufacturerReference)
VALUES (@DocumentTreeId, @Manufacturer, @ManufacturerReference)
SELECT @NewId = SCOPE_IDENTITY()
END
COMMIT TRANSACTION
END
SELECT @NewId
END
GO
