sql-server - 在计算列中使用 master.sys.fn_varbintohexsubstring

Question

在我的 SQL Server 表中，我想添加一个计算列，它是同一表中其他列的哈希值。下面是我的表结构。

地址:
地址ID(int , PK)
地址行1 (nvarchar)
地址行2 (nvarchar)
城市 (nvarchar)
状态 (nvarchar)
AddressHash(计算列)

以下是我想要在计算列中获得的内容:

MASTER.SYS.FN_VARBINTOHEXSUBSTRING(0, HASHBYTES('SHA1',COALESCE(AddressLine1, N'') + COALESCE(AddressLine2, N'') + COALESCE(City, N'') + COALESCE(State, N'')), 1, 0)

如果我右键单击表并转到设计并在“计算列规范”下的“公式”中输入上述内容，则会收到以下错误:

- 无法修改表。
在此上下文中，用户定义的函数名称不能以数据库名称为前缀。

所以我想我会使用用户定义的函数来计算哈希并将该 udf 映射到公式。

下面是我用来创建 UDF 的代码:

CREATE FUNCTION udfHashAddress
(   
    @pAddressLine1 nvarchar(50), @pAddressLine2 nvarchar(50), @pCity nvarchar(50), @pState nvarchar(50))
)
RETURNS nvarchar(max) -- not sure what the correct size would be
WITH SCHEMABINDING
AS
BEGIN   
    DECLARE @result nvarchar(max)   
    SELECT @result = MASTER.SYS.FN_VARBINTOHEXSUBSTRING(0, HASHBYTES('SHA1',COALESCE(@pAddressLine1, N'') + COALESCE(@pAddressLine2, N'') + COALESCE(@pCity, N'') + COALESCE(@pState, N'')), 1, 0)  
    RETURN @result
END
GO

但是上面的代码出现以下错误:

*无法架构绑定(bind)函数“udfHashAddress”，因为名称“MASTER.SYS.FN_VARBINTOHEXSUBSTRING”对于架构绑定(bind)无效。名称必须采用两部分格式，并且对象不能引用自身。*

当我删除“MASTER”数据库前缀时，我收到此错误:

*无法架构绑定(bind)函数“udfHashAddress”，因为它引用系统对象“SYS.FN_VARBINTOHEXSUBSTRING”。*

我在这里遗漏了什么吗？将不胜感激任何帮助/指示。

Answer 1

既然您使用的是 SQL Server 2008，您是否尝试过:

CONVERT(VARCHAR(MAX), HASHBYTES('SHA1','string'), 2);

这将返回大写字母而不是小写字母，但如果它很重要，您可以使用 LOWER() 修复它。

这是一个包含您实际架构的示例(有意在 tempdb 中创建):

USE tempdb;
GO

CREATE TABLE dbo.[Address]
(
    AddressID INT PRIMARY KEY,
    AddressLine1 NVARCHAR(64),
    AddressLine2 NVARCHAR(64),
    City NVARCHAR(64),
    [State] NVARCHAR(64),
    AddressHash AS LOWER(CONVERT(VARCHAR(4000), HASHBYTES('SHA1', 
        COALESCE(AddressLine1, N'') + COALESCE(AddressLine2, N'') 
        + COALESCE(City, N'') + COALESCE([State], N'')), 2))
        --PERSISTED -- you could also persist it if desired
);

INSERT dbo.[Address]
    VALUES(1, 'foo', 'bar', 'blat', 'splunge'),
          (2, 'bar', 'foo', 'blag', 'splmger');

SELECT *, master.dbo.fn_varbintohexsubstring
    (0,
        HASHBYTES
        (
            'SHA1', 
            COALESCE(AddressLine1, N'') + COALESCE(AddressLine2, N'') 
            + COALESCE(City, N'') + COALESCE([State], N'')
        ), 1, 0)
        FROM dbo.[Address];
GO

DROP TABLE dbo.[Address];