12

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?

  • Out of curiosity, why are you hashing your data like this? And why MD5 in particular? (MD5 is insecure). Microsoft recommends using CHECKSUM and BINARY_CHECKSUM instead of HASHBYTES. I also recommend avoiding performing hashes in database queries because of how binary text encoding works, see here for an example: stackoverflow.com/questions/27908449/… – Dai Dec 12 '19 at 9:52
  • I'm aware of the different use cases of the different hashing algorithms, but my particular case has nothing to do with security so I think I should be ok with MD5. I appreciate the tip though. My case is a daily job that needs to sync about half a billion records down from one database to another, to and from a single table. Currently the job literally drops and recreates the entire destination table and takes about 5 hours long causing a lot of unaffordable contention on the source table. The table is pretty wide too. An indexed view with a unique clustered index on the table's... – J.D. Dec 14 '19 at 22:14
  • ...primary key and a VARCHAR representation of the MD5 hash of the entire row's data is proving to be quite efficient to determine which rows need to be updated, deleted, or inserted from the source table when joining to a similar indexed view on the source table. Every day there's only about five million rows that actually change (so roughly 1% of the data that was previously being copied in it's entirety). What used to take about five hours now takes about 45 minutes. – J.D. Dec 14 '19 at 22:16
  • You did get me thinking just now, and I guess I could've also tried filtered transactional replication to leverage something more out of the box for managing synchronizing changes between the source and destination table but replication can be quirky sometimes too. – J.D. Dec 14 '19 at 22:34
27

CAST is not deterministic because date format may change based on the server settings (i.e. under compatibility before 110 default date format is 0 => "Dec 12 2019 2:11PM" and output dependeds on language).
To make it deterministic use CONVERT and

the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113. (from documentation below)

More documentation at: Deterministic and Nondeterministic Functions

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.