Use a scalar hashing function in a computed column - non-deterministic?
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 &lt;table_name&gt; 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.