2

Does anyone know how to create a Hash Computed Column Persisted? I keep receiving an error below. Otherwise, I will have to utilize an update or trigger statement everytime. I know how to conduct for single column, however this includes multiple columns now.

CREATE TABLE [dbo].[CustomerTransactiont]
(
    CustomerTransactionId int primary key identity(1,1),
    CustomerName varchar(255),
    Price decimal(10,2),
    Quantity int,

    RowHash as hashbytes('SHA2_512', (select CustomerName
    ,Price
    ,Quantity for xml raw)) persisted
) 

Msg 1046, Level 15, State 1, Line 7
Subqueries are not allowed in this context. Only scalar expressions are allowed.
6

Just concat your columns together rather than trying to convert them into xml and then a hash.

CREATE TABLE [dbo].[CustomerTransactiont]
(
    CustomerTransactionId int primary key identity(1,1),
    CustomerName varchar(255),
    Price decimal(10,2),
    Quantity int,

    RowHash as hashbytes('SHA2_512', CONCAT(    UPPER(COALESCE(CustomerName, '')), '|'
                                                , COALESCE(CONVERT(VARCHAR(50), Price), ''), '|'
                                                , COALESCE(CONVERT(VARCHAR(20), Quantity), ''), '|'
                                                )) persisted
) 

Note: The coalesce and the pipe characters were just for examples. The Upper is optional if you either want hash to be case sensitive or not.

| improve this answer | |

Your Answer

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