使用 DateTime 作为主键的 Sql Server
您好，我的问题类似于：
但我我对 Sql Server 特别感兴趣，我想在考虑特定场景的情况下实际解决这个问题，而不是像另一篇文章中那样从理论上解决。
我想存储用户执行的事件/操作。多个用户在同一 100 毫秒间隙内执行一项操作的几率非常低，并且偶尔发生的冲突是可以接受的。如果我可以离散地表达 10 毫秒甚至 1 毫秒的间隙，那么我对风险感到非常满意。
因此提出了一个问题，我可以使用 DateTime 作为我的主键而不是唯一标识符，因为我将定期查询最新的 100 个事件并按事件发生的时间对事件进行排序。
Hi my questions is similar to:
MySQL: Using DATETIME as primary key
But I'm specifically interested in Sql Server and I want to approach the question practically with a specific scenario in mind rather than theoretically as in the other post.
I want to store events/actions that users perform. The odds of more than one user performing an action in the same 100ms gap is very low and infrequent collision are acceptable. If I could discretely express 10ms or even 1ms gaps then I'm very happy with the risks.
Thus begs the question, can I use a DateTime as my primary key instead of a unique identifier, because I will be regularly querying the latest 100 events and sorting the events by the time which they occurred.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
是的，你可以，但这对我来说听起来是一个非常糟糕的主意。
如果您确实担心性能，可以使用顺序唯一标识符、自动递增整数，或者可以为 DateTime 列提供其自己的聚集索引（推荐）。
Yes you can, but it sounds like a very bad idea to me.
If you are really worried about performance, you can use a sequential unique identifier, an auto-incrementing integer, or you can give the DateTime column its own clustered index (recommended).
如果您确实想避免使用代理键（ID/身份列），我至少会将日期时间列与用户 id 列组合起来作为复合键。这听起来更适合您的数据。
If you really want to avoid a surrogate key for this (ID/identity column), I would at least combine the datetime column with the user id column for a composite key. This sounds like a more natural fit for your data.
在 SQL Server 2008 中，使用 DATETIME2，而不是 DATETIME。您可以在 SQL Server 2008 中实现高达 100 纳秒的精度。
如果您有时需要在给定时间记录多于一行（无论频率如何），那么我不确定您通过将日期和时间转换为钥匙。选择密钥的重要标准是熟悉性、简单性和稳定性。在此基础上，假设它确实符合您的要求，那么日期和时间似乎是一个明智的选择。
In SQL Server 2008, use DATETIME2, not DATETIME. You can achieve upto 100 nanosecond precision in SQL Server 2008.
If you sometimes need to record more than one row for a given time, however infrequently, then I'm not sure what you are trying to achieve by making the date and time into a key. Important criteria for choosing keys are Familiarity, Simplicity and Stability. On that basis, assuming it does make sense for your requirements, a date and time seems like a sensible choice.
为了让不频繁的冲突是可以接受的，你真的不能使用时间戳，因为会发生冲突。
您可以通过使用稍微修改数据时间的智能过程来编写冲突代码，但是当时间戳上的索引就足够并且更容易实现时，为什么还要麻烦呢。
这篇文章 提供了有关 C# 特有的精度问题的信息。
In order for infrequent collisions to be acceptable you really cannot use the timestamp as there will be collisions.
You could code around collisions by using a smart procedure that modifies the datatime slightly, but why bother when an index on the timestamp would suffice and be far easier to implement.
This article is informative on precision issues specific to C# however.