If I have a query like this:
如果我有这样的查询：
SELECT * FROM tTable
where tTable does not contain any indexes a table-scan happens, as expected. If I add a clustered index on some column then QO decides to use clustered index scan on this query. Why? Why is clustered-index-scan preferred instead of table-scan in this case?
其中tTable不包含任何索引，正如预期的那样发生表扫描。如果我在某些列上添加聚簇索引，则QO决定对此查询使用聚簇索引扫描。为什么？在这种情况下，为什么首选聚集索引扫描而不是表扫描？
2 个解决方案
#1
0
If I add a clustered index on some column then QO decides to use clustered index scan on this query
如果我在某些列上添加聚簇索引，则QO决定对此查询使用聚簇索引扫描
because when you create a clustered index on a table,data in table is rearranged in index order..so table it self is clustered index.This is also the reason why you can't have two clustered indexes on same table
因为当你在表上创建聚簇索引时，表中的数据会按索引顺序重新排列。所以表自身是聚簇索引。这也是你不能在同一个表上有两个聚簇索引的原因
To summarize,when you create a clustered index,there is only one structure ,not two(clustered index and table)
总而言之，当您创建聚簇索引时，只有一个结构，而不是两个（聚簇索引和表）
#2
0
The query is "give me all rows and all columns" which means "read every row" which is a scan
查询是“给我所有行和所有列”，这意味着“读取每一行”这是一个扫描
There is nothing to do an index seek on, because there is no WHERE clause. Unlike this:
索引搜索无关，因为没有WHERE子句。不同于此：
SELECT * FROM tTable WHERE PrimaryClusteredKeyValue = 45
Then this may use a nonclustered seek followed by a clustered key lookup or it may still scan the clustered index because you ask for all columns. It depends on how many rows gbn will match
然后，这可能会使用非聚簇搜索，然后是聚簇键查找，或者它仍然可以扫描聚簇索引，因为您要求所有列。这取决于gbn将匹配多少行
SELECT * FROM tTable WHERE NonClusteredOtherColumnValue = 'gbn'
#1
0
If I add a clustered index on some column then QO decides to use clustered index scan on this query
如果我在某些列上添加聚簇索引，则QO决定对此查询使用聚簇索引扫描
because when you create a clustered index on a table,data in table is rearranged in index order..so table it self is clustered index.This is also the reason why you can't have two clustered indexes on same table
因为当你在表上创建聚簇索引时，表中的数据会按索引顺序重新排列。所以表自身是聚簇索引。这也是你不能在同一个表上有两个聚簇索引的原因
To summarize,when you create a clustered index,there is only one structure ,not two(clustered index and table)
总而言之，当您创建聚簇索引时，只有一个结构，而不是两个（聚簇索引和表）
#2
0
The query is "give me all rows and all columns" which means "read every row" which is a scan
查询是“给我所有行和所有列”，这意味着“读取每一行”这是一个扫描
There is nothing to do an index seek on, because there is no WHERE clause. Unlike this:
索引搜索无关，因为没有WHERE子句。不同于此：
SELECT * FROM tTable WHERE PrimaryClusteredKeyValue = 45
Then this may use a nonclustered seek followed by a clustered key lookup or it may still scan the clustered index because you ask for all columns. It depends on how many rows gbn will match
然后，这可能会使用非聚簇搜索，然后是聚簇键查找，或者它仍然可以扫描聚簇索引，因为您要求所有列。这取决于gbn将匹配多少行
SELECT * FROM tTable WHERE NonClusteredOtherColumnValue = 'gbn'