Geek Answers Handbook

Why is there a non-clustered index scan when counting all rows in a table?

As far as I understand, each transaction sees its own version of the database, so the system cannot get the total number of rows from some counter and, therefore, must scan the index. But I thought it would be a clustered primary key index, not additional indexes. If I had more than one additional index, which one will be selected?

When I delve into the matter, I noticed another strange thing. Suppose there are two identical tables: articles and articles2, each of which has three columns: Id, View_Count and Title. The first has only a PK-based clustered index, and the second has an additional non-clustered, not unique index on view_count. The query SELECT COUNT(1) FROM Articlesis 2 times faster for a table with an additional index.

+5
source share
1 answer

SQL Server optimizes your query - if it needs to count the rows in the table, it will select the smallest possible data set for this.

, - - , . - / .

, , , SQL Server - , NC ( ) - .

+8

More articles:


All Articles