Search code examples
sql-serverviewunique-constraintudf

How to index a calculated hash column

In SQL Server, I have created a view that contains two columns. a normal column and a calculated hash column. I need to create a unique constraint on these two columns. Trying to add a constraint or index causes an error because of the GetHash UDF. 

CREATE VIEW HashView
WITH SCHEMABINDING    
AS
SELECT p.ItemId, [dbo].[GetHash](p.Id) as PriceHash from dbo.price p

Is there a simple way to solve this or do I need to resort to using a trigger?

Solution

  • SQL Server is not willing to trust you that your CLR function is deterministic. And so only allows CLR computed columns to be indexed if persisted.

    see: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/indexes-on-computed-columns