SQL Server 组合键体验

Question

我想在文档表上创建一个复合键（这就是想法，但我愿意接受其他建议）。它将由两列组成，年份（2010 年，...）和 ID，它会自动递增，但每年都应该重新启动。

因此，像 2010-1、2010-2、...、2011-1、2011-2、... 这样的键，并且最好这些键也应该用于显示、打印和搜索。

虽然我不相信自动增量会起作用，因为每年都会重置，所以我想我必须自己增量，不是吗？

或者我应该创建一个 varchar 列并自己构建每个键，然后在该列上放置一个唯一的键？

那么，我有什么选择呢？

还请考虑我所选择的设计可能会遇到的未来设计问题（无论它是什么）以及查询的方便性。

更新：

我真的开始考虑让应用程序构建密钥并在插入时提供它。但是，这需要在数据库中查找最后发布的 ID，这可能会导致大量使用出现问题。

Answer 1

将业务与数据存储结构分离是一个很好的实践。为什么？因为明天有人会决定更改业务逻辑：

• 使用非整数文档顺序（1-AA、1-AB、1-AC...）
• 包括年份和月份来构建一些月度报告
• 任何其他更改...

以及应该做什么你比？

所以，我的解决方案是：

• 使用主键（例如 int 或您喜欢的数据类型）与数据库中的其他表建立关系
• 使用业务键 1.2.3...如您所愿（可能是某些标识符生成器）
• 使用日期时间字段存储添加文档的日期，可以动态计算年份。

Answer 2

为什么不添加一个实际的自增id呢？复合键可能会迅速增长到几乎无用的程度 - 特别是出于性能原因（如果您必须加入表）。如果您还想记录文档 XYZ 是 2010 年存储的第一个文档，您仍然可以拥有 Year 和 Order （或其他）列，但您的主键保持干净整洁。

Answer 3

如果您在创建自动递增键时遇到麻烦，我会放弃每年重置它的想法，而只需使用 IDENTITY INT 列即可。

如果您想获取一年内文档的序列号，可以使用 SQL 函数来实现：

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY...)

Answer 4

虽然我不相信自动增量会起作用，因为每年都会重置，所以我想我必须自己增量，不是吗？

是的。

我建议添加一列以生成重置值。 IDENTITY 列是最好的； DATETIME 可以保存记录创建时间，但 3.33 毫秒（0. 00333 秒）内的事务彼此将具有相同的时间戳。

无论哪种方式，您都可以使用以下方法生成 id 值：

SELECT (SELECT COUNT(*)
          FROM DOCUMENTS t
         WHERE t.year = d.year
           AND t.col <= d.col) AS id,
        d.year
   FROM DOCUMENTS d

或者，如果您使用的是 SQL Server 2005+，则可以使用：

 SELECT ROW_NUMBER() OVER (PARTITION BY d.year ORDER BY d.col) AS id,
        d.year
   FROM DOCUMENTS d