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.

cengland0 (5/6/2010)

---

Good thought except the code has this in it:

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


That does create a non clustered index on the EmailAddress column.

CirquedeSQLeil (5/5/2010)

---

That is likely true - haven't tried. I am just accustomed to asking for the actual execution plan that it seemed the safer route for this too.

Tom Brown (5/6/2010)

---

Am I right in thinking there are a more efficient ways of creating the test data?
Such as:

WITH NORBAR AS (
	SELECT N FROM dbo.Tally
	WHERE N < 500 )
INSERT INTO QOTD (Title,EmailAddress,DateSubmitted,Age)
SELECT 'Mr','jsmith@email.com','24/03/2010',50
FROM NORBAR;

See Jeff's article on Tally table: http://www.sqlservercentral.com/articles/T-SQL/62867/

[b]The correct answer is 1 INNER JOIN is used. When the database engine executes the SELECT query to return all rows that have an email address of 'jdoe@email.com' the following steps take place:

1. There is an Index Seek on the IX_EmailAddress non clustered index - this searches for the 'jdoe@email.com' email address. When the email address is found in the index, its QOTDID value will be used in STEP 2 - The value for EmailAddress is returned by this step.

2. Next there is a clustered index seek on the IX_QOTDID index looking for the QOTDID value which was found in STEP 1. - The value for Title, DateSubmitted and Age are returned by this step.

3. Then the output of the index seek (STEP 1) is joined with the output of the clustered index seek (STEP 2) and the data is returned via the SELECT statement - thus returning the row of data that contains an EmailAddress of 'jdoe@email.com' :