如果索引是 DATETIME 或 DATETIME2，为什么索引可能不会做太多事情，因为它们包含时间部分？
对问题“如何减少简单的响应时间”的评论select 查询？” 告诉：
“LaunchDate 上的数据类型是什么？如果是 DATETIME 或 DATETIME2，索引不太可能起多大作用，因为它们包含时间部分 – OMG Ponies”
“@OMG - 为什么不在 DateTime 上建立聚集索引列提高性能？查询是范围扫描，因为所有数据都在顺序块中，因此可以进行快速范围索引查找？ Calgary Coder"
"Calgary Coder: DATETIME/2 包括时间 -- 索引、聚集或非聚集、对于具有重复时间但不具有范围的日期很有用。 – OMG Ponies”
我在 DATETIME 类型列 LaunchDate 上创建了一个带有聚集索引的测试表，并观察索引查找类似于上述问题中引用的查询：
SELECT COUNT(primaryKeyColumn)
FROM MarketPlan
WHERE LaunchDate > @date
而不是表或索引扫描为什么
DateTime 列上的聚集索引不能提高性能？
如果是 DATETIME 或 DATETIME2，为什么索引不太可能发挥作用，因为它们包含时间部分？
我希望有一个脚本说明 DATETIME 列的索引不会提高性能。
更新：此外，OMG 是否暗示 DATE 类型列上的索引会有帮助，但对 DATETIME 和 DATETIME2 没有帮助？
Comments to question "How to decrease response time of a simple select query?" tell:
"What is the data type on LaunchDate? An index isn't likely to do much if it's DATETIME or DATETIME2 because they include the time portion – OMG Ponies"
"@OMG - Why wouldn't a Clustered Index on a DateTime column improve performance? The query is a range scan which would allow for a fast range index lookup as all data would be in sequential blocks? Semi-related...msdn.microsoft.com/en-us/library/ms177416.aspx – Calgary Coder"
"Calgary Coder: DATETIME/2 includes time -- an index, clustered or non-clustered, would be good for dates with duplicate times but not ranges. – OMG Ponies"
I created a test table with clustered index on DATETIME type column LaunchDate and observe index seeks for queries similar to cited in above question:
SELECT COUNT(primaryKeyColumn)
FROM MarketPlan
WHERE LaunchDate > @date
instead of table or index scans.
Why wouldn't a clustered index on a DateTime column improve performance?
Why isn't index likely to do much if it's DATETIME or DATETIME2 because they include the time portion?
I'd appreciate a script illustrating that indexing of DATETIME column does not improve performance.
Update: Also, Did OMG imply that index on DATE type column would be helpful but not DATETIME and DATETIME2?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我读过另一个问题，不知道 OMG 小马意味着什么
3 点：
只需搜索或扫描
：根据统计数据，如果
LaunchDate > > @date表示 90% 的行，那么很可能会发生扫描。如果它是非常有选择性的，那么寻找的可能性就更大。不管集群还是非集群！
什么索引？
像这样的查询需要 LaunchDate 和 PrimaryKeyColumn 上的索引
现在，任何非聚集索引都指默认情况下假定为 PK 的聚集索引。因此，primaryKeyColumn 已经隐式包含在内。
迷信
但是，
COUNT(primaryKeyColumn)是一种迷信。因为 PKs 不允许 NULL，所以相当于所以你只需要 LaunchDate 上的索引，无论是聚簇还是非聚簇
I've read the other question, no idea what OMG ponies means
3 points:
Seek or scan:
Based on statistics, if
LaunchDate > @datemeans, say, 90% of the rows, then most likely a scan will happen. If it is quite selective, then a seek is more likely.Regardless of clustered or non-clustered!
What index?
A query like this would require an index on LaunchDate and primaryKeyColumn
Now, any non-clustered index refers to the clustered index which is assumed to the PK by default. So primaryKeyColumn is implicitly included already.
Superstition
However,
COUNT(primaryKeyColumn)is a superstition. Because PKs do not allow NULL, it is equivalent toSo you only need an index on LaunchDate, whether clustered or non-clustered
如果您的应用程序使用日期时间，则不会使用日期列上的索引，这会导致隐式数据类型转换。如果您查看执行计划，您可以看到有一个内部函数应用于列。解决方案是将日期列更改为时间戳（4）或调整客户端应用程序以使用日期而不是日期时间。
Index on a date column will not be use if your application use datetime which cause implicit data type conversion . If you look on the execution plan you can see that there is an internal function applied to a column. Solution is change the date column to timestamp(4) or adjust client application to use date instead of datetime.