Sql-server – Clustered monotonically increased index insert performance

The "like '%zzz%'" largely negates the possible benefit of the index since it will need to examine each entry to determine whether it matches; and doing the "SELECT *" means it would have to do a lookup to the clustered index to get all of the column data on the matched records; the optimizer can't estimate how many matches there will be so chooses the clustered index.

Try changing it to "like 'zz%'" and see what you get.

When you have a clustered index on a table, the clustered index IS the table!

Mentally you can substitute "table" for "clustered index" in this instance and it will make sense.

The data for every field in every row is in your clustered index. The clustered index just sets the order of the physical pages in the database to be organized by your clustering key(s).

You can always fall back on the phone book analogy for these things, too: in your classic phone book, the data is clustered on Last Name, First Name. Each entry still has PhoneNum, Address at the leaf level but you don't order by that. The pages are in physical order by the the keys.

I can't advise on optimizing the query unless you show us the table and query you are running, but basically this cost will be paid one way or another. If you don't update the clustered index it will be a table update and a table scan.