This table is very small!
It has 20 rows of which 2 match the search condition. The table definition contains three columns and two indexes (which both support uniqueness constraints).
CREATE TABLE Person.ContactType(
ContactTypeID int IDENTITY(1,1) NOT NULL,
Name dbo.Name NOT NULL,
ModifiedDate datetime NOT NULL,
CONSTRAINT PK_ContactType_ContactTypeID PRIMARY KEY CLUSTERED(ContactTypeID),
CONSTRAINT AK_ContactType_Name UNIQUE NONCLUSTERED(Name)
)
Running
SELECT index_type_desc,
index_depth,
page_count,
avg_page_space_used_in_percent,
avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(db_id(),
object_id('Person.ContactType'),
NULL,
NULL,
'DETAILED')
Shows both indexes only consist of a single leaf page with no upper level pages.
+--------------------+-------------+------------+--------------------------------+--------------------------+
| index_type_desc | index_depth | page_count | avg_page_space_used_in_percent | avg_record_size_in_bytes |
+--------------------+-------------+------------+--------------------------------+--------------------------+
| CLUSTERED INDEX | 1 | 1 | 15.9130219915987 | 62.5 |
| NONCLUSTERED INDEX | 1 | 1 | 13.1949592290586 | 51.5 |
+--------------------+-------------+------------+--------------------------------+--------------------------+
Rows on each index page aren't necessarily in index key order but each page has a slot array with the offset of each row on the page. This is maintained in index order.
The nonclustered index covers two out of the three columns (Name as a key column and ContactTypeID as a row locator back to the base table) but is missing ModifiedDate.
You can use index hints to force the NCI seek as below
SELECT ct.*
FROM Person.ContactType AS ct WITH (INDEX = AK_ContactType_Name)
WHERE ct.Name LIKE 'Own%';
But you can see that under SQL Server's cost model this plan is given a higher estimated cost than the competing CI scan (roughly double).
The single page clustered index scan would just need to read all the 20 rows on the page, evaluate the predicate against them and return them.
The single page nonclustered index range seek might potentially be able to perform a binary search on the slot array to reduce the number of rows evaluated however the index does not cover the query so it would also need a potential IO to retrieve the CI page and then it would still need to locate the row with the missing column values on there (for each row returned by the NCI seek).
On my machine running 1 million iterations of the non clustered index plan took 15.245 seconds compared to 11.113 seconds for the clustered index plan. Whilst this is far from double the plan without the hint was measurably faster.
Even if the table was orders of magnitude larger however you may well still not get your expected plan with lookups.
SQL Server's costing model prefers sequential scans to random IO lookups and the "tipping point" between it choosing a scan of a covering index or a seek and lookups of a non covering one is often surprisingly low as discussed in Kimberley Tripp's blog post here.
It is certainly not out of the question that it would choose such a plan for a 10% selective predicate but the clustered index would likely need to be quite a lot wider than the NCI for it to do so.
The reason for the behavior is that SQL Server can't determine how many rows will match to ForeignKey, since there is no index with RowKey as the leading column (it can deduce this from statistics on the #temp table, but those don't exist for table variables/UDTTs), so it makes an estimate of 100,000 rows, which is better handled with a scan than a seek+lookup. By the time SQL Server realizes there is only one row, it's too late.
You might be able to construct your UDTT differently; in more modern versions of SQL Server you can create secondary indexes on table variables, but this syntax is not available in 2008 R2.
BTW you can get the seek behavior (at least in my limited trials) if you try to avoid the bitmap/probe by hinting a nested loops join:
DECLARE @Keys TABLE (RowKey INT PRIMARY KEY); -- can't hurt
INSERT @Keys (RowKey) VALUES (10);
SELECT
t.RowKey
,t.SecondColumn
FROM
dbo.Test t
INNER JOIN
@Keys k
ON
t.ForeignKey = k.RowKey
OPTION (LOOP JOIN);
I learned this trick from Paul White several years ago. Of course, you should be careful about putting any kind of join hints in production code - this can fail if people make changes to the underlying objects and that specific type of join is no longer possible or no longer most optimal.
For more complex queries, and when you move to SQL Server 2012 or above, it's possible that trace flag 2453 could help. That flag didn't help with this simple join, though. And the same disclaimers would apply - this is just an alternative thing you shouldn't generally do without a ton of documentation and rigorous regression testing procedures in place.
Also, Service Pack 1 is long out of support, you should get on Service Pack 3 + MS15-058.
Best Answer
Because the majority of the table fits the criteria for the first query, so it is more efficient to scan the clustered index rather than do key lookups for each of the rows that match the criteria.
Key lookups are expensive, and so are usually only used when a small percentage of the table fits the WHERE criteria. Once the query returns a certain percentage of the table (AKA The tipping point) the optimizer falls back to a Clustered Index scan, as that is the more efficient plan.
See Kimberly Tripp's blog post about The Tipping Point