Luis Cazares (7/7/2016)
Nice Chris, I hope that this helps Eliseo.I think that I understand what you're saying about the TOP filtering before the aggregate, even if the execution plan indicates otherwise with the operators order. However, the reads indicate that the TOP isn't being applied. I would appreciate if you could share me the plans.
Better still, here's a test harness.
-- Run up a table with a decent number of rows.
-- 31 distinct dates
-- 10 distinct types, 11 to 21
-- 911 * date * type
IF OBJECT_ID('tempdb..#Trans') IS NOT NULL DROP TABLE #Trans;
;WITH _Tally AS (
SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n)
),
Dates AS (SELECT TOP(31) [date] = DATEADD(DAY,n-1,'20160101') FROM _Tally),
_Types AS (SELECT TOP(10) TypeID = n+10 FROM _Tally),
Iterations AS (SELECT TOP(911) n FROM _Tally)
SELECT
ID = IDENTITY(int,1,1),
d.[Date],
t.TypeID
INTO #Trans
FROM Dates d CROSS JOIN _Types t CROSS JOIN Iterations i
ORDER BY d.[Date], t.TypeID
CREATE UNIQUE CLUSTERED INDEX ucx_ID ON #Trans (ID)
CREATE INDEX ix_Helper ON #Trans (TypeID, [Date])
-- 100 rows, TypeID = 1 to 100
IF OBJECT_ID('tempdb..#TypeID') IS NOT NULL DROP TABLE #TypeID;
SELECT TOP 100 TypeID = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
INTO #TypeID
FROM #Trans;
CREATE UNIQUE CLUSTERED INDEX ucx_TypeID ON #TypeID (TypeID)
-----------------------------------------------------------------------
-- Run the query
SELECT TOP(11) *
FROM #TypeID t
LEFT loop JOIN (
SELECT TypeID, cnt = COUNT(*)
FROM #trans
WHERE [date] = '20160116'
GROUP BY TypeID
) d ON d.TypeID = t.TypeID;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden