SQL Server：日期时间、ASC 或 DESC 上的聚集索引
如果我有一个在日期时间字段上有聚集索引的 SQL Server 表，在插入之前将其设置为 DateTime.Now（来自 C#），索引应该升序还是降序以避免表重组？
谢谢。
If I have an SQL Server table with a clustered index on a datetime field, that is set to DateTime.Now (from C#) before inserts, should the index be ascending or descending to avoid reorganization of the table?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
并不重要 - 但 DateTime 真的能保证是唯一的吗？ 我会避免仅在 DateTime 上放置聚集索引 - 我会使用 INT IDENTITY 或 BIGINT IDENTITY 来代替，并在 DateTime 上放置常规非聚集索引（因为这确实不能保证是唯一的......）
Marc
PS：与主键一样，对于聚集键应该是什么的普遍共识是：
组成聚集键的列（包括 4 字节唯一符）被添加到每个非聚集索引中的每个条目 - 因此您希望它们尽可能精简。
PS 2：聚集键被添加到每个非聚集索引中，因为这是 SQL Server 在非聚集索引中找到搜索值后检索整行的方式。 可以这么说，这是该行在数据库中的“位置”。 因此，它应该是独特且狭窄的。
Doesn't really matter - but is the DateTime really guaranteed to be unique?? I would AVOID putting a clustered index on just a DateTime - I would use a INT IDENTITY or BIGINT IDENTITY instead, and put a regular non-clustered index on DateTime (since that's really not guaranteed to be unique......)
Marc
PS: Like a primary key, the general consensus on what a clustered key should be is:
The column(s) that make up the clustered key (including that 4-byte uniqueifier) are added to EVERY ENTRY in EVERY non-clustered index - so you want to keep those as slim as possible.
PS 2: the clustering key(s) are added to each non-clustered index because that's the way that SQL Server will retrieve the whole rows once it's found the search value in the non-clustered index. It's the row's "location" in the database, so to speak. Therefore, it should be unique and narrow.
阅读此 http:// /www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx
如果经常基于日期时间字段读取，则不错的选择是复合键日期和身份 - 按该顺序（日期、身份）。
Read this http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx
If read was frequently based on datetime field, the good choice is a composite key of date and identity - in that order (date, identity).
在大多数情况下，DateTime 列“足够唯一”，可以在其上创建聚集索引。 在极少数情况下，如果您有重复的值，SQL Server 将添加一个唯一符以使这些值唯一。
Aaron Bertrand 对此做了一个非常好的实验，使用 DateTime 列作为聚集索引：
https://dba.stackexchange.com/questions/61894/getdate-uniqueness-插入时使用
In most cases, a DateTime column is "unique enough" to create a clustered index on that. And in those rare cases where you have duplicate values, SQL Server will add a uniquifier to make those values unique.
Aaron Bertrand did a very nice experiment with this, using a DateTime column as the clustered index:
https://dba.stackexchange.com/questions/61894/getdate-uniqueness-when-used-on-insert