I came out with the same code as Jeff showed in the article, but I believe that Scott's code would be better as it is a single table scan instead of 2 or 3.
And now call me crazy, but execution plans show one thing and time indicators show another on performance issues. Maybe is because I'm using temp tables instead of normal physical tables, but the statement with 3 table scans is performing faster than the other two. And the statement with 1 table scan is the slowest.
Can anyone confirm or deny this?
This is the code I used:
--insert into #product_details
--SELECT TOP 1000000
--'CUST' + CAST( (ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) / 4) + 6 AS varchar(10)),
--ABS(CHECKSUM(NEWID())) % 5,
--GETDATE()
--FROM sys.all_columns ac1
-- CROSS JOIN sys.all_columns ac2
DECLARE @DATE datetime2, @dummy varchar(50)
SET @DATE = SYSDATETIME()
SELECT @dummy = CustId
FROM(
SELECT CustId
FROM #product_details
WHERE ProdId IN( 1,4)
GROUP BY CustId HAVING COUNT(DISTINCT prodid) = 2
EXCEPT
SELECT CustId
FROM #product_details
WHERE ProdId = 0) t
PRINT DATEDIFF( ns, @DATE, SYSDATETIME())
SET @DATE = SYSDATETIME()
SELECT @dummy = CustId
FROM
(SELECT CustId
FROM #product_details
WHERE ProdId = 1
AND CustId IN (SELECT DISTINCT CustId FROM #product_details WHERE ProdId = 4)
EXCEPT
SELECT CustId
FROM #product_details
WHERE ProdId = 0) AS A
PRINT DATEDIFF( ns, @DATE, SYSDATETIME())
SET @DATE = SYSDATETIME()
SELECT
@dummy = CustId
FROM #product_details
GROUP BY
CustId
HAVING
MAX(CASE WHEN ProdId = 0 THEN 1 ELSE 0 END) = 0 AND
MAX(CASE WHEN ProdId = 1 THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN ProdId = 4 THEN 1 ELSE 0 END) = 1
ORDER BY
CustId
PRINT DATEDIFF( ns, @DATE, SYSDATETIME())
SET @DATE = SYSDATETIME()