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?
如果我在日期时间字段上有一个带有聚簇索引的SQL Server表，那么在插入之前设置为DateTime.Now（来自C＃），索引应该是升序还是降序以避免重组表？
Thanks.
谢谢。
2 个解决方案
#1
27
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......)
真的不重要 - 但DateTime真的保证是独一无二的吗？我会避免在一个DateTime上放置一个聚簇索引 - 我会改为使用INT IDENTITY或BIGINT IDENTITY，并在DateTime上放一个常规的非聚集索引（因为这真的不能保证是唯一的......）
Marc
渣子
PS: Like a primary key, the general consensus on what a clustered key should be is:
PS：像主键一样，关于群集密钥应该是什么的一般共识是：
- unique (otherwise SQL Server will "uniquify" it by adding a 4-byte uniqueifier to it)
- 唯一的（否则SQL Server将通过添加一个4字节的唯一符来“统一”它）
- as narrow as possible
- 尽量缩小
- static (never change)
- 静态（永不改变）
- ever increasing
- 不断增加
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.
构成聚簇键的列（包括该4字节唯一符号）将添加到每个非聚集索引中的每个条目中 - 因此您希望尽可能保持这些列。
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.
PS 2：将聚簇键添加到每个非聚集索引中，因为这是SQL Server在非聚集索引中找到搜索值后检索整行的方式。这是数据库中行的“位置”，可以这么说。因此，它应该是独特和狭窄的。
#2
5
Read this http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx
阅读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).
如果读取经常基于日期时间字段，那么好的选择是日期和身份的复合键 - 按该顺序（日期，标识）。
#1
27
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......)
真的不重要 - 但DateTime真的保证是独一无二的吗？我会避免在一个DateTime上放置一个聚簇索引 - 我会改为使用INT IDENTITY或BIGINT IDENTITY，并在DateTime上放一个常规的非聚集索引（因为这真的不能保证是唯一的......）
Marc
渣子
PS: Like a primary key, the general consensus on what a clustered key should be is:
PS：像主键一样，关于群集密钥应该是什么的一般共识是：
- unique (otherwise SQL Server will "uniquify" it by adding a 4-byte uniqueifier to it)
- 唯一的（否则SQL Server将通过添加一个4字节的唯一符来“统一”它）
- as narrow as possible
- 尽量缩小
- static (never change)
- 静态（永不改变）
- ever increasing
- 不断增加
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.
构成聚簇键的列（包括该4字节唯一符号）将添加到每个非聚集索引中的每个条目中 - 因此您希望尽可能保持这些列。
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.
PS 2：将聚簇键添加到每个非聚集索引中，因为这是SQL Server在非聚集索引中找到搜索值后检索整行的方式。这是数据库中行的“位置”，可以这么说。因此，它应该是独特和狭窄的。
#2
5
Read this http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx
阅读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).
如果读取经常基于日期时间字段，那么好的选择是日期和身份的复合键 - 按该顺序（日期，标识）。