Sql-server – Use a scalar hashing function in a computed column – non-deterministic

computed-columnhashingsql server

I have a scalar function

select [dbo].[fn_get_hash_fields] ('blah','dbo')

to which is passed a table and a schema
and which returns an expression representing a concatenation of all column values in each row. For example, if the table had the 4 columns below, the function would return (CR's added for readability):

ISNULL(RTRIM([string1]),'')
+ISNULL(RTRIM(CONVERT(varchar(10),[number1])),'')
+ISNULL(RTRIM(CONVERT(varchar(10),[number2])),'')
+ISNULL(RTRIM(CONVERT(varchar(10),number3])),'')

Using this function, I then want to add a computed column that will store a 32 byte hash value representing all the values in each row:

alter table dbo.blah add [checksum] as 
(substring(lower(convert(varchar(32), HASHBYTES('MD5',
[dbo].[fn_get_hash_fields] ('blah','dbo')
),1)),3,32))

The column is added, but the hash value is exactly the same for each row.

How do I convince SQLS that the function is non-deterministic and make the function evaluated for each row, computing different hash values?

I know that in the computed column I can simply use the expression returned by the function, rather than a call to the function, and that works. But its not dynamic. If I add a column to the table, it won't be represented in the hash until I drop the computed column and add an updated one that takes the new column into account.

Is there a way around this?
Is there a better approach to store a hash in each row built from all the columnar values in that row?

Here is the function:

CREATE function [dbo].[fn_get_hash_fields] (@p_table_name 
VARCHAR(100),@p_schema_name VARCHAR(20))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @szSqlString as varchar(max)
SET @szSqlString = ''    
      SELECT @szSqlString = @szSqlString +
            CASE DATA_TYPE 
                    WHEN 'int'           THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'tinyint'       THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'smallint'      THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'bigint'        THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '],112)),'''')'  
                    WHEN 'datetime2'     THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'datetime'      THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '],112)),'''')'
                    WHEN 'smalldatetime' THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '],112)),'''')'
                    WHEN 'date'          THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '],112)),'''')' 
                    WHEN 'bit'           THEN 'ISNULL(RTRIM(CONVERT(varchar(1),[' + COLUMN_NAME + '])),'''')'  
                    WHEN 'decimal'       THEN 'ISNULL(RTRIM(CONVERT(varchar('+ CONVERT(varchar(2),NUMERIC_PRECISION) +'),[' + COLUMN_NAME + '])),'''')' 
                    WHEN 'numeric'       THEN 'ISNULL(RTRIM(CONVERT(varchar('+ CONVERT(varchar(2),NUMERIC_PRECISION) +'),[' + COLUMN_NAME + '])),'''')' 
                    ELSE                      'ISNULL(RTRIM([' + COLUMN_NAME + ']),'''')'
                 END + '+'
            FROM   INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = @p_schema_name 
            and TABLE_NAME = @p_table_name                
            AND (COLUMN_NAME != 'HASHKEYID')
            AND [DATA_TYPE] NOT IN ( 'TEXT', 'IMAGE')

    RETURN LEFT(ISNULL(@szSqlString, ''),LEN(@szSqlString)-1)
END

Best Answer

How do I convince SQL Server that the function is nondeterministic and make the function evaluate for each row, computing different hash values?

SQL Server marks the function nondeterministic, because it accesses data in system tables:

SELECT 
    UserDataAccess = OBJECTPROPERTYEX(FN.ObjectID, 'UserDataAccess'),
    SystemDataAccess = OBJECTPROPERTYEX(FN.ObjectID, 'SystemDataAccess'),
    IsDeterministic = OBJECTPROPERTYEX(FN.ObjectID, 'IsDeterministic')
FROM (VALUES(OBJECT_ID(N'[dbo].[fn_get_hash_fields]', 'FN'))) AS FN (ObjectID);

Nevertheless, the function actually does return the same value for each row in the table, because nothing in the function's query depends on anything in the source row. SQL Server does execute the function for each row (something to be avoided, in general for performance reasons), but each execution returns the same result.

The function returns a string, which would need to be executed (with SELECT and FROM <table_name> added in the right places) to return a result. Even then, it would return a value for every row of the table, because there is nothing in the constructed SQL to link the generated text to a particular row.

More to the point, it is not even possible to execute dynamic SQL in the context of a function (unsafe hacks aside).

Is there a way around this? Is there a better approach to store a hash in each row built from all the columnar values in that row?

This isn't as straightforward as one might think at first glance.

For example, given:

CREATE TABLE dbo.Test
(
    c1 integer PRIMARY KEY,
    c2 varchar(10) NOT NULL,
    c3 datetime NOT NULL
);
GO
INSERT dbo.Test 
    (c1, c2, c3)
VALUES 
    (1, 'ABCDE', GETDATE()),
    (2, 'FGHIJ', GETUTCDATE());

The following query returns the broad sort of thing needed:

SELECT 
    T.c1,
    T.c2,
    T.c3, 
    Chk = BINARY_CHECKSUM(*) -- or CHECKSUM
FROM dbo.Test AS T;

Unfortunately, the Chk column cannot be added as a computed column in the table:

Msg 1789, Level 16, State 1, Procedure ComputeChecksum, Line 5
Cannot use CHECKSUM(*) in a computed column, constraint, or default definition.

It cannot be used directly in a function either, for a variety of reasons depending on exactly how you try it, but one entertaining result is the CHECKSUM(*) becomes recursive (referencing itself) and execution terminates when the depth limit of 32 is reached.

A trigger implementation of an all-columns checksum/hash is probably possible, but would need to contain dynamic SQL (or some XML trickery) because CHECKSUM(*) would include the checksum/hash column itself. Aside from DML triggers, a DDL trigger would also be needed to populate the values when a new column is added. This might be an interesting exercise, but it is not trivial.

If the restrictions are relaxed so the checksum/hash does not need to be in the same table, more options become available. For example, a view:

CREATE VIEW dbo.TestChk
AS
SELECT T.*, Chk = BINARY_CHECKSUM(*) 
FROM dbo.Test AS T;

Though this looks like it would dynamically adapt to changes in the base table, in fact it does not. You will get wrong results if a column is added to the table, for example. When the table schema is changed, the view would have to be refreshed:

-- New column
ALTER TABLE dbo.Test 
ADD c4 decimal(5,2) NOT NULL DEFAULT 0.0;

-- Refresh required
EXECUTE sys.sp_refreshsqlmodule 
    @name = N'dbo.TestChk', 
    @namespace = DEFAULT;

Note the view also cannot be materialized, because it cannot be schema-bound:

Msg 1054, Level 15, State 7, Procedure TestChk, Line 4
Syntax '*' is not allowed in schema-bound objects.

I haven't tried every possible implementation option (including a SQLCLR function), because even if it can be made to work as you want, it is very likely to be horribly inefficient, and not the 'best' solution to the problem you really need to solve.

We would need to know the reasons for wanting this column, as it is quite likely there is a better way to achieve the end result you need via a different implementation option, possibly a standard feature in the product. I would suggest asking a new question for that.

Explanation

The real question is why the optimizer felt the need to retrieve A, B, and C for the index seek at all. We would expect it to read the Comp column using a nonclustered index scan, and then perform a seek on the same index (alias T2) to locate the Top 1 record.

The query optimizer expands computed column references before optimization begins, to give it a chance to assess the costs of various query plans. For some queries, expanding the definition of a computed column allows the optimizer to find more efficient plans.

When the optimizer encounters a correlated subquery, it attempts to 'unroll it' to a form it finds easier to reason about. If it cannot find a more effective simplification, it resorts to rewriting the correlated subquery as an apply (a correlated join):

Apply rewrite

It just so happens that this apply unrolling puts the logical query tree into a form that does not work well with project normalization (a later stage that looks to match general expressions to computed columns, among other things).

In your case, the way the query is written interacts with internal details of the optimizer such that the expanded expression definition is not matched back to the computed column, and you end up with a seek that references columns A, B, and C instead of the computed column, Comp. This is the root cause.

Workaround

One idea to workaround this side-effect is to write the query as an apply manually:

SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
CROSS APPLY
(  
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Unfortunately, this query will not use the filtered index as we would hope either. The inequality test on column D inside the apply rejects NULLs, so the apparently redundant predicate WHERE T1.D IS NOT NULL is optimized away.

Without that explicit predicate, the filtered index matching logic decides it cannot use the filtered index. There are a number of ways to work around this second side-effect, but the easiest is probably to change the cross apply to an outer apply (mirroring the logic of the rewrite the optimizer performed earlier on the correlated subquery):

SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
OUTER APPLY
(  
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Now the optimizer does not need to use the apply rewrite itself (so the computed column matching works as expected) and the predicate is not optimized away either, so the filtered index can be used for both data access operations, and the seek uses the Comp column on both sides:

Outer Apply Plan

This would generally be preferred over adding A, B, and C as INCLUDEd columns in the filtered index, because it addresses the root cause of the problem, and does not require widening the index unnecessarily.

Persisted computed columns

As a side note, it is not necessary to mark the computed column as PERSISTED, if you don't mind repeating its definition in a CHECK constraint:

CREATE TABLE dbo.T 
(   
    ID integer IDENTITY(1, 1) NOT NULL,
    A varchar(20) NOT NULL,
    B varchar(20) NOT NULL,
    C varchar(20) NOT NULL,
    D date NULL,
    E varchar(20) NULL,
    Comp AS A + '-' + B + '-' + C,

    CONSTRAINT CK_T_Comp_NotNull
        CHECK (A + '-' + B + '-' + C IS NOT NULL),

    CONSTRAINT PK_T_ID 
        PRIMARY KEY (ID)
);

CREATE NONCLUSTERED INDEX IX_T_Comp_D
ON dbo.T (Comp, D) 
WHERE D IS NOT NULL;

The computed column is only required to be PERSISTED in this case if you want to use a NOT NULL constraint or to reference the Comp column directly (instead of repeating its definition) in a CHECK constraint.

Sql-server – Computed column ‘IsWeekend’ in table cannot be persisted because the column is non-deterministic

You can do a datediff between 1900-01-01, that just happens to be a monday, and check if modulo 7 is a 5 or a 6.

create table D
(
  DateRecorded date,
  IsWeekend as cast(case when datediff(day, 0, DateRecorded) % 7 in (5, 6) 
                      then 1 
                      else 0 
                    end as bit) persisted
)

BTW, you don't need to persist the computed column in order to use it in an index.

Best Answer

Related Solutions

Sql-server – Index on Persisted Computed column needs key lookup to get columns in the computed expression

Explanation

Workaround

Persisted computed columns

Sql-server – Computed column ‘IsWeekend’ in table cannot be persisted because the column is non-deterministic

Related Question