Jack Corbett (5/5/2010)
I got it wrong because I didn't read the script. I read the text where it says you created a non-clustered index on LastName and the query used EmailAddress. IF the index had been on LastName there would have been only a clustered index scan and no join. Yes, with the index on EmailAddress I would have expected 1 join because of a key/bookmark lookup.
Good thought except the code has this in it:
[font="Courier New"]CREATE NONCLUSTERED INDEX [IX_EmailAddress] ON [dbo].[QOTD]
(
[EmailAddress] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
[/font]
That does create a non clustered index on the EmailAddress column.