CODEWITHSUNDEEP
sql-serversql-server-2016
LCJ
LCJ

Reputation: 22662

Unique constraint challenge with HASHBYTES

I have following table in which records should be unique based on 4 columns.

I added a HashCol using HASHBYTES. Now it is an error message saying Column 'HashCol' in table 'RCM_EM_Benchmarks' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

How to esnure uniueness in this scenario?

CREATE TABLE [dbo].[MyTable](
    [RecordID] [int] IDENTITY(1,1) NOT NULL,
    [Procedure_Code] [varchar](10) NULL,
    [Procedure_Percentage] [decimal](6, 2) NULL,
    [Claim_Count] [int] NULL,
    [Benchmark] [varchar](40) NULL,
    [Practice] [varchar](40) NULL,
    [Month_Start_Date] [date] NULL,
    [Procedure_Type] [varchar](40) NULL,
    [CreatedOnDate] [date] NULL,
    HashCol AS HASHBYTES('SHA1', [Procedure_Code] + [Benchmark]+ [Practice]+ CONVERT(VARCHAR(25),  [Month_Start_Date]) )
) ON [PRIMARY]
GO

ALTER  TABLE  [MyTable] ADD CONSTRAINT hashCol_Unique UNIQUE (HashCol)
GO

Upvotes: 0

Views: 609

Answers (2)

Ben Thul
Ben Thul

Reputation: 32717

This works for me:

use tempdb;
drop table if exists dbo.myTable;
CREATE TABLE [dbo].[MyTable](
    [RecordID] [int] IDENTITY(1,1) NOT NULL,
    [Procedure_Code] [varchar](10) NULL,
    [Procedure_Percentage] [decimal](6, 2) NULL,
    [Claim_Count] [int] NULL,
    [Benchmark] [varchar](40) NULL,
    [Practice] [varchar](40) NULL,
    [Month_Start_Date] [date] NULL,
    [Procedure_Type] [varchar](40) NULL,
    [CreatedOnDate] [date] NULL,
    HashCol AS HASHBYTES('SHA1', [Procedure_Code] + [Benchmark] + [Practice] + convert(char(10), [Month_Start_Date], 126))
) ON [PRIMARY]
GO

ALTER  TABLE  [MyTable] ADD CONSTRAINT hashCol_Unique UNIQUE (HashCol)
GO

Speaking as to why, convert() is non-deterministic in several cases (including the default style). You have to choose a style that is not labeled as non-deterministic in the documentation. Ironically, I tried style 23 (which returns just the date as yyyy-mm-dd and is not labeled as non-deterministic) and that too was a no-go. There's verbiage in the same documentation linked above saying that styles below 100 are non-deterministic, so I gave my personal favorite 126 a go and it worked. Note too I changed the data type to char(10) since converting a date to that style will always be 10 bytes.

This will still throw a warning though as hashbytes(), for whatever reason, is listed as returning "varbinary (maximum 8000 bytes)". However, in the documentation it also gives the actual output length for the different hash algorithms. You could explicitly cast the result of the hashbytes() call to that length to avoid the warning or just ignore it because I think in practical terms it'll never be over the stated length for your algorithm.

The last thing I'll say is that SHA1 is deprecated and has been for a while. For new development, you should be using one of the SHA2 variants. Since your question is tagged SQL 2016, SHA2_256 and SHA2_512 are available to you.

Upvotes: 2

user2414025
user2414025

Reputation: 101

Using the style parameter of the convert function should make it deterministic.

For example:

Instead of:

HashCol AS HASHBYTES('SHA1', [Procedure_Code] + [Benchmark]+ [Practice]+ CONVERT(VARCHAR(25),  [Month_Start_Date]) )

Use this one, which uses the 101 as a style parameter:

 HashCol AS HASHBYTES('SHA1', [Procedure_Code] + [Benchmark]+ [Practice] + CONVERT(VARCHAR(25),  [Month_Start_Date], 101 ) )

Upvotes: 2

Related Questions