4

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)?

jojo
  • 285
  • 4
  • 14

1 Answers1

6

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Yup. This is one of the reasons why it might be beneficial to include indices even when using non-sargable filters. Above all, profile :D – Luaan Apr 23 '15 at 08:43
  • Thanks! Is it because the clustered index will get a more levels B-tree as it have to store all data on leaf, which will need more page data(leaf). But non-clustered index only store columnA. It have less levels B-tree and less data page. So scan the non-clustered is also more effective than clustered index. – jojo Apr 23 '15 at 08:47
  • @jojo - the levels should be the same between a clustered and non-clustered index on the same columns, since non-leafs only store those columns. But, indeed, the leaves are much bigger in the clustered index. – Damien_The_Unbeliever Apr 23 '15 at 08:52
  • Just to be explicit, this is true since the clustered index is, by the way SQL Server implements it, *all* of the data in your table at the leaf level. – Ben Thul Apr 23 '15 at 18:46