生成的查询未达到最佳表格索引
我有一个tableau生成的查询，如下所示：
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)))
表'表'有两个索引：
当我查看查询的执行计划时，它告诉我正在使用第二个索引。 这对我来说似乎很奇怪，因为我认为索引只能在涉及的列形成'该索引最左边的前缀'的情况下才能使用（如此处所述），而不是这个查询。 如果任何人可以解释它是什么我不理解，或者如果这只是一个奇怪的情况下不正确的索引得到使用，您的帮助将非常感激！
链接地址: http://www.djcxy.com/p/44013.html