SQL 2016列存储索引返回错误的结果集 [英] SQL 2016 columnstore index returns wrong result set

问题描述

我会告诉你一个失败的选择陈述 - 不会返回预期结果。

接下来我将对选择短语进行微小更改（保持相同的
来自和其中短语），该语句将返回预期结果。

然后我将删除并重新创建列存储索引并再次执行第一个语句，它现在将返回正确的结果。

我有一个名为CACH.Employee的表。

此表有一个列存储索引以及两个唯一索引：

在select语句后面应返回一行，但不返回任何内容。此外，执行计划使用唯一索引显示索引查找以及使用列存储索引进行索引扫描（键查找）。

接下来我将选择列表从*更改为COUNT（*）：

令人惊讶的是，现在SQL实际上可以找到此记录。

注意唯一的区别是我从 SELECT * 改为 SELECT COUNT（*）：

最后我删除columstore索引，然后再次创建列存储索引：

DROP INDEX cci_Employee ON CACH.Employee;

GO

CREATE CLUSTERED COLUMNSTORE INDEX cci_Employee

  ON CACH.Employee

  WITH（DROP_EXISTING = OFF，COMPRESSION_DELAY = 0）;
$
GO

最后我运行第一个失败的选择语句，现在声明有效：

现在执行计划不同了。我原本希望SQL使用唯一索引（索引搜索）来查找此记录，但由于某种原因，它决定使用列存储索引进行索引扫描。

1. 这是一个错误吗？
2. 如果我不能信任列存储索引，那么我将不得不删除我使用的所有列存储索引并创建旧样式索引。有什么意见吗？
3. 为什么SQL没有使用唯一索引来进行索引查找而不是索引查找？

解决方案

PS - 我为每个语句都有执行计划XML，但它们相当大，我没有看到任何方法为这个文档添加附件。

I will show you a select statement that fails -- does not return expected result.
Next I will make a minor change to the select phrase (keep the same from and where phrases) and that statement will return expected result.
Then I will drop and recreate the column store index and execute the first statement again, it will now return correct result.

I have a table called CACH.Employee.
This table has a column store index as well as two unique indices:

following select statement should return one row, but it returns nothing. Furthermore, execution plan shows an index seek using a unique index as well as a index scan (key lookup) using columnstore index.

Next I change the selection list from * to COUNT(*):
Surprisingly, now SQL can actually find this record.
Note the only difference is I changed from SELECT * to SELECT COUNT(*):

finally I drop the columstore index, then create the column store index again:

DROP INDEX cci_Employee ON CACH.Employee;
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_Employee
  ON CACH.Employee
  WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0);
GO

finally I run the first select statement that failed, now the statement works:

and now the execution plan is different. I would have expected SQL to use the unique index (index seek) to find this record, but for some reason it decided to do an index scan using the column store index.

1. Is this a bug?
2. If I cannot trust columnstore indices, then I will have to drop all columnstore indices that I use and create old style indices. Any opinions?
3. Why didn't SQL use the unique index to do an index lookup instead of index seek?

解决方案

PS - I have the execution plan XML for each of these statements, but they're rather big and I don't see any way to add attachments to this document.

这篇关于SQL 2016列存储索引返回错误的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！