Generated Query Not Hitting the Optimal Table Index

 

I have a tableau-generated query that looks like: 

SELECT (((DATEPART(year,(CASE
WHEN 0 = ISDATE(CAST([table1].[M_Date] AS VARCHAR)) THEN NULL
ELSE DATEADD(day, DATEDIFF(day, 0, CAST(CAST([table1].[M_Date] AS VARCHAR) as datetime)), 0) END)) * 10000) + (DATEPART(month,(CASE
WHEN 0 = ISDATE(CAST([table1].[M_Date] AS VARCHAR)) THEN NULL
ELSE DATEADD(day, DATEDIFF(day, 0, CAST(CAST([table1].[M_Date] AS VARCHAR) as datetime)), 0) END)) * 100)) + DATEPART(day,(CASE
WHEN 0 = ISDATE(CAST([table1].[M_Date] AS VARCHAR)) THEN NULL
ELSE DATEADD(day, DATEDIFF(day, 0, CAST(CAST([table1].[M_Date] AS VARCHAR) as datetime)), 0) END))) AS [md:M_Date:ok]
FROM [tbl].[table] [table1]
GROUP BY (((DATEPART(year,(CASE
WHEN 0 = ISDATE(CAST([table1].[M_Date] AS VARCHAR)) THEN NULL
ELSE DATEADD(day, DATEDIFF(day, 0, CAST(CAST([table1].[M_Date] AS VARCHAR) as datetime)), 0) END)) * 10000) + (DATEPART(month,(CASE
WHEN 0 = ISDATE(CAST([table1].[M_Date] AS VARCHAR)) THEN NULL
ELSE DATEADD(day, DATEDIFF(day, 0, CAST(CAST([table1].[M_Date] AS VARCHAR) as datetime)), 0) END)) * 100)) + DATEPART(day,(CASE
WHEN 0 = ISDATE(CAST([table1].[M_Date] AS VARCHAR)) THEN NULL
ELSE DATEADD(day, DATEDIFF(day, 0, CAST(CAST([table1].[M_Date] AS VARCHAR) as datetime)), 0) END)))

table 'table' has two indexes:

  • A clustered index over the columns [M_Date], [Team], [Player], [Second].
  • A non-unique, non-clustered index over the columns [Player], [M_Date], [Team]

    • When I look at the Execution Plan for the query, it tells me that the second index is being used. This seems strange to me because I thought that an index could only be used if the columns involved form 'a leftmost prefix of the index' (as explained here) which this query does not. If anyone could explain what it is I am not understanding, or if this is just an odd case where the incorrect index is getting used, your help would be much appreciated!

    链接地址: http://www.djcxy.com/p/44014.html

    上一篇: 为什么选择top ... order by indexed column仍然排序？

    下一篇: 生成的查询未达到最佳表格索引