I have a table with 145000 rows. And have not any index on it.
When I run below sql. I found a table scan on execute plan as expected. It generate six rows and 3481 logic read.
SET STATISTICS IO ON
SELECT columnA FROM table WHERE LEN(columnA)<>5
Then I add a clustered index on columnA and run the sql.I found a cluster index scan on execute plan. It generate six rows and 3511 logic read. I can understand the greater logic read for B-tree nodes read.
But what confuse me is that I use a non-clustered index instead of clustered index on columnA and run the sql. I found an index scan on execute plan. It generate six rows and only need 417 logic read!.
I don't think the len() function can take the advantage of index. But why a non-clustered index on columnA makes less logic read(9 times)?
The LEN function can't make "use" of the index, but the index, containing only this column, will occupy far less space, in its entirety, than the base table does. So it's more efficient to scan this index than to scan the base table.
Scanning the base table will be loading all of the other columns in the table even though they're not needed to satisfy the query.