CODEWITHSUNDEEP
sql-serverviewunique-constraintudf
earlxtr
earlxtr

Reputation: 370

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?

Upvotes: 1

Views: 1635

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

make your view like this,

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

then create unique composite constraint on itemid and id as usual

CREATE UNIQUE INDEX IX_HashView ON dbo.HashView(itemid,id)
GO

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89266

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

Upvotes: 1

Related Questions