I'm trying to create an indexed view where the index is on a field in the view which is a hash of a bunch of the columns of the underlying table in the view.
Here's an example:
CREATE VIEW CoolHashedView WITH SCHEMABINDING AS SELECT KeyId, CONVERT ( VARCHAR(34), HASHBYTES('MD5', TextColumn1 + '||' + TextColumn2 + '||' + CAST(DateColumn1 AS VARCHAR(50)), 2 ) AS HashedData FROM dbo.BoringTable; CREATE UNIQUE CLUSTERED INDEX IX_CoolHashedView_KeyId_HashedData ON CoolHashedView (KeyId, HashedData);
When I try to create the above index, it fails and the following error is thrown:
Msg 2729, Level 16, State 1, Line 26 Column 'HashedData' in view 'CoolHashedView' cannot be used in an index or statistics or as a partition key because it is non-deterministic.
When I remove the date field from the HASHBYTEs function, the clustered index then successfully creates.
My guess is it has something to do with the different ways to format a date or different timezones?...am I getting warmer?