As a .NET web-developer, I find myself producing lots of stored procedures and functions (SQL Server 2012). I know my understanding of query performance issues could/should be better (we don't have a DBA), so I decided to work through the above book. I confess to finding some of the concepts tricky, so I thought I might ask for clarification on issues as I come across them.
On page 71, the following simple example is given (on the AdventureWorks database - 2012 in my case).
FROM Person.ContactType AS ct
WHERE Name LIKE 'Own%'
Primary key, clustered - ContactTypeId
Unique, non-clustered - Name
The above query resulted in a non-clustered index seek, which I understand. As a way of learning, I play around with the query and try to predict what will happen. I removed the WHERE clause from the above and predicted a clustered index scan, as the clustered index would be the only index referenced. It produced a non-clustered index scan, which I don't understand.
Can anyone shed any light?