Why is query using Clustered Index when it shouldn't? Let us presume I have a table named Category in a SQL Server 2005 database.  Category has category_id (bigint, identity) as its primary key and name (nvarchar(50)).  There is obviously a clustered index on category_id, and I have also added a non-clustered index on name.  If I run the query 

SELECT * 
FROM Category 
WHERE [name] like '%zz%'


and look at the Execution Plan via Management Studio 2005, it says it is using a Clustered Index scan.  The clustered index is on category_id, not on name.

Why is it saying that?
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.