如何避免HASHBYTES使用时的“表达式类型转换”问题及索引优化

我编写了如下查询语句，能得到正确结果，但不确定通过哈希比对用户密码与输入密码的方式是否为最优方案：

declare @password nvarchar(254) ='R0nald0$123456.', 
@username nvarchar(50)='Ronaldo',
@playerId uniqueidentifier,
@userType int;

declare @UserUsername_CS int =CHECKSUM(@username);

    SELECT Top 1 @playerId = [UserId]
      FROM [dbo].[UsersPrivate]
     WHERE [UserUsername_CS] =  @UserUsername_CS    
       And [UserUsername]   = @username                
       AND [UserPassword]   = HASHBYTES('SHA2_256', CONCAT([Salt], @password));

select @playerId

由于[Salt]是表中字段，无法预先将其与密码拼接为Binary(32)类型变量。我创建了如下索引：

CREATE UNIQUE INDEX [IX_UsersPrivate_UserName_UserPassword] ON [dbo].[UsersPrivate]
(
    [UserUsername_CS] ASC,
    [UserUsername] ASC,
    [UserPassword] ASC  
)

但该索引未被执行计划使用，同时收到警告：Type conversion in expression (CONVERT_IMPLICIT(nvarchar(8),[ClubStat].[dbo].[UsersPrivate].[Salt],0)) may affect "CardinalityEstimate" in query plan choice。

表定义如下：

CREATE TABLE [dbo].[UsersPrivate](
    [UserId] [uniqueidentifier] NOT NULL,
    [UserPassword] [binary](32) NOT NULL,
    [UserUsername] [nvarchar](50) NOT NULL,
    [UserUsername_CS]  AS (checksum([UserUsername])) PERSISTED,
    [Salt] [binary](16) NOT NULL,
 CONSTRAINT [PK_UsersPrivate] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC
)

我使用如下代码生成密码哈希和Salt：

DECLARE @salt BINARY(16) = CAST(CRYPT_GEN_RANDOM(16) AS BINARY(16));

-- Compute the hash
DECLARE @hashedPassword BINARY(32);
SET @hashedPassword = HASHBYTES('SHA2_256', CONCAT(@salt, CAST(@password AS VARBINARY(510))));

请告知如何避免上述类型转换问题，并让索引被正常使用？

1. 修复类型转换警告 #

警告的核心原因是CONCAT函数会自动将binary类型的Salt转换为nvarchar，再和nvarchar类型的@password拼接，触发了隐式类型转换。解决方法是统一拼接操作的类型：将输入密码先转换为varbinary，再和Salt拼接：

DECLARE @password nvarchar(254) ='R0nald0$123456.', 
@username nvarchar(50)='Ronaldo',
@playerId uniqueidentifier,
@userType int;

DECLARE @UserUsername_CS int = CHECKSUM(@username);
-- 预先将输入密码转为varbinary类型
DECLARE @passwordBinary varbinary(510) = CAST(@password AS varbinary(510));

SELECT TOP 1 @playerId = [UserId]
FROM [dbo].[UsersPrivate]
WHERE [UserUsername_CS] = @UserUsername_CS    
  AND [UserUsername] = @username                
  AND [UserPassword] = HASHBYTES('SHA2_256', CONCAT([Salt], @passwordBinary));

SELECT @playerId

这样CONCAT的两个参数都是二进制类型，不会触发隐式转换，消除警告。

2. 让索引生效并优化性能 #

当前索引包含UserPassword，但查询中是通过计算后的哈希值匹配该列，SQL Server无法直接利用索引做查找。你可以调整逻辑：先通过用户名定位唯一用户，再在内存中验证密码哈希，同时优化索引为覆盖索引：

优化后的查询

DECLARE @password nvarchar(254) ='R0nald0$123456.', 
@username nvarchar(50)='Ronaldo',
@playerId uniqueidentifier,
@storedSalt binary(16),
@storedPassword binary(32);

DECLARE @UserUsername_CS int = CHECKSUM(@username);

-- 通过用户名索引获取用户的Salt和存储的密码哈希
SELECT TOP 1 
  @playerId = [UserId],
  @storedSalt = [Salt],
  @storedPassword = [UserPassword]
FROM [dbo].[UsersPrivate]
WHERE [UserUsername_CS] = @UserUsername_CS    
  AND [UserUsername] = @username;

-- 内存中计算哈希并比对
IF @storedPassword = HASHBYTES('SHA2_256', CONCAT(@storedSalt, CAST(@password AS varbinary(510))))
BEGIN
  SELECT @playerId;
END
ELSE
BEGIN
  -- 密码不匹配时返回NULL或自定义逻辑
  SELECT NULL AS playerId;
END

调整后的索引

CREATE UNIQUE INDEX [IX_UsersPrivate_UserName] ON [dbo].[UsersPrivate]
(
    [UserUsername_CS] ASC,
    [UserUsername] ASC
)
INCLUDE ([UserId], [Salt], [UserPassword]);

这个索引是覆盖索引，查询时不需要回表，直接从索引中获取所需数据，会被执行计划优先选用。

3. 密码哈希方案的优化建议 #

你当前的方案是可行的，还可以进一步提升安全性：

• 使用HMAC_SHA2_256替代SHA2_256，它是带密钥的哈希算法，抗碰撞能力更强：

  SET @hashedPassword = HASHBYTES('HMAC_SHA2_256', CAST(@password AS varbinary(510)), @salt);
  

• 增加哈希迭代次数（比如循环哈希多次），提升暴力破解的难度，注意平衡性能与安全性。

内容的提问来源于stack exchange，提问作者Walter Verhoeven