cengland0 (5/6/2010)
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.
Right, but I did say that I didn't read the script, which I state in my post. In reality the information provided without the code does provide enough information to answer the question, it's just that the information provided does not match what it is in the code. I am commenting so that the question can be corrected.
I usually try to answer the questions without running the code provided because it is usually too easy to get it right if you run the code. It is in this case as well, if you view an execution plan.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question