while working on the query below in order to answer this question:
Having the following tables:
CREATE TABLE [dbo].[#foo] ( [creation] DATETIME NOT NULL, [value] MONEY NULL, [DT] AS (CONVERT([date],[CREATION])) PERSISTED) -- add a clustered index on the dt column CREATE CLUSTERED INDEX CI_FOO ON #FOO(DT) GO
and this other table for joining:
create table #bar (dt date primary key clustered) go
But when running the following query:
WITH RADHE AS ( SELECT THE_ROW=ROW_NUMBER() OVER(PARTITION BY B.DT ORDER BY B.DT), THE_DATE=B.dt, THE_NUMBER_OF_RECORDS_ON_THIS_DAY=CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY F.DT ) END , THE_TOTAL_VALUE_FOR_THE_DAY=COALESCE(SUM(F.VALUE) OVER (PARTITION BY b.DT ),0) FROM #BAR B LEFT OUTER JOIN #FOO F ON B.dt = F.dt ) --get rid of the duplicates and present the result SELECT THE_DATE, THE_NUMBER_OF_RECORDS_ON_THIS_DAY, THE_TOTAL_VALUE_FOR_THE_DAY FROM RADHE WHERE THE_ROW = 1
I get something like this picture below, which is exactly what I was looking for.
But the execution plan generated has several Sort and Nested Loops Operations, as you can see on the picture below.
this is a very simple operation, a left outer join between 2 tables, the indexes are already ordered, and therefore I was wondering if I could simplify the query plan.
alternatively, I could change the query code.
why exactly do we need
nested loops 2 times and
sort 2 times in the query plan?