主页 / dba / 问题 / 236819
Accepted
AAndrus
Asked: 2019-04-27 12:29:42 +0800 CST

将社会保险号存储为 Columnstore 的 DECIMAL(10,9)

  • 772

我正在处理一个非常大的数据库（10B + 行），该数据库对 SSN 和 BirthDate 执行匹配以尝试查找重复记录。该表正在使用列存储压缩（SQL SERVER 2016），我想到我可以将 SSN 保存为 DECIMAL(10,9) 以保留前导零而不是从 CHAR/VARCHAR 中获得性能损失。我只是想知道是否有人尝试过这个，或者是否有原因导致它无法按预期工作。我知道我可以转换为 INT 并只丢失前导零，但这对我来说似乎是一个更好的解决方案。

ISNULL(TRY_CAST('.' + SSN AS DECIMAL(10,9)),0) AS DecimalSSN

我总是可以将它转换回一个字符串RIGHT(TRY_CAST(DecimalSSN AS VARCHAR),9) AS SSN

sql-server columnstore

1 个回答

  1. Best Answer

    我不会使用 DECIMAL 来存储带有行存储或列存储表的 SSN。与 DECIMAL 相比，INT 数据类型具有以下优点：

    • SQL Server 使用起来通常更快
    • 它允许将位图过滤器下推到存储引擎
    • 如果该列不允许 NULL，则它允许一个“完美的散列函数”，它不需要连接中的探测残差。

    如果您的 SSN 列需要更好的性能，我会使用带前导 1 的 INT。这样可以保留似乎需要的前导零。您应该以相同的格式存储所有 SSN，并且只在必要时投射。例如，如果您需要将 SSN 作为字符串显示给最终用户，则SELECT RIGHT(CAST(1012345678 AS INT), 9)返回"012345678". 否则使用原始值。

    我不知道您的查询是什么样的，但假设您在一个表中有 10 万个 SSN，并且您需要检查这些 SSN 中是否有任何一个出现在具有十亿行的不同表中。查询可能如下所示：

    SELECT *
FROM dbo.SSNS_TO_CHECK_3 c
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.ALL_SSNS_CCI_INT_LEADING_1 t
    WHERE c.SSN = t.SSN
)
OPTION (MAXDOP 1);

    使用前导 1 的 INT 列，上述查询在我的机器上需要 7 秒。使用您建议的 DECIMAL(10, 9) 格式，查询需要 63 秒我的机器。几乎所有的时间都花在了位图运算符上。

    columnstore 有一些额外的注意事项，但它们在这里无关紧要。与 DECIMAL(10, 9) 相比，INT 在我所知道的各个方面都优于 DECIMAL(10, 9)。

    • 6

相关问题