Statement evaluation precedence

Hugo Kornelis (5/7/2010)

I have to disagree with the comments so far. I do not consider this a very good question.

The processing order as described in the correct answer is the logical order of evaluation. What order a Relational Database Management System uses internally is completely implementation dependant; any internal processing order is valid as long as the results are the same as when the logical processing order had been used. This is where the query optimizer comes in - it considers countless various processing orders and access methods to find the one with the lowest estimated cost.

I would have liked the question (a lot!) if the question had asked for the logical processing order. But the question specificallly asked aboout the sequence internally used, and that threw me off completely. I did answer correctly, but only because, after reading the answer options, I was able to work out what the intention of the question was.

Thanks for taking the effort to submit a question, Tom. I know how much work goes into it, and I appreciate the effort. I hope my harsh comments won't keep you from submitting more questions.

USE TEMPDB;
GO

CREATE TABLE Items (
pKey INT NOT NULL,
Created DateTime,
ID char(20),
Region INT,
fItemType INT );
CREATE TABLE ItemTypes (
pKey INT NOT NULL,
Description varchar(50),
CODE char(20) );

INSERT INTO ItemTypes
SELECT 1, 'Purchase Order', 'ORDER' UNION ALL
SELECT 2, 'Invoice Order', 'INVOICE';

INSERT INTO Items
SELECT 1, GetDate(), '381203', 1, 1 UNION ALL
SELECT 7, DATEADD(day,-1,GetDate()), '371203', 3, 1 UNION ALL
SELECT 8, DATEADD(day,-1,GetDate()), '371203', 3, 1 UNION ALL
SELECT 2, DATEADD(day,-2,GetDate()), '391203', 1, 1 UNION ALL
SELECT 3, DATEADD(hour,-1,GetDate()), '383203', 2, 2 UNION ALL
SELECT 4, DATEADD(day,-1,GetDate()), '385203', 2, 2 UNION ALL
SELECT 5, DATEADD(year,-1,GetDate()), '394203', 3, 2 UNION ALL
SELECT 6, DATEADD(month,-1,GetDate()), '340203', 3, 2 ;

-- Test Query - get the actual execution plan for this
-- remember to read from right to left
SELECT DISTINCT TOP(3) I.ID, COUNT(I.Region) AS RCount, T.Description, I.Created, T.CODE
FROM dbo.Items AS I
JOIN dbo.ItemTypes AS T ON I.fItemType = T.pKey
WHERE I.Created > '2009-12-10'
AND T.Description like '%Order'
GROUP BY T.CODE, I.ID, I.Region, T.Description, I.Created, T.CODE
HAVING COUNT(I.Region) <= 1
ORDER BY I.Created DESC, T.CODE

ALTER TABLE dbo.Items
ADD PRIMARY KEY CLUSTERED (pKey) ;
ALTER TABLE dbo.ItemTypes
ADD PRIMARY KEY CLUSTERED (pKey);
ALTER TABLE dbo.Items
WITH CHECK ADD FOREIGN KEY (fItemType) REFERENCES dbo.ItemTypes(pKey);

-- try again with clustered & foreign keys (doesn't make a difference to the processing order
SELECT DISTINCT TOP(3) I.ID, COUNT(I.Region) AS RCount, T.Description, I.Created, T.CODE
FROM dbo.Items AS I
JOIN dbo.ItemTypes AS T ON I.fItemType = T.pKey
WHERE I.Created > '2009-12-10'
AND T.Description like '%Order'
GROUP BY T.CODE, I.ID, I.Region, T.Description, I.Created, T.CODE
HAVING COUNT(I.Region) <= 1
ORDER BY I.Created DESC, T.CODE

-- clean up
DROP TABLE TempDb.dbo.Items
DROP TABLE TempDB.dbo.ItemTypes