How this query generates two join operators in execution plan?

Question

Table structure with a clustered unique index on empno.

CREATE TABLE [dbo].[EMP](
    [EMPNO] [int] NOT NULL,
    [ENAME] [varchar](10) NULL,
    [JOB] [varchar](9) NULL,
    [MGR] [int] NULL,
    [HIREDATE] [datetime] NULL,
    [SAL] [int] NULL,
    [COMM] [int] NULL,
    [DEPTNO] [int] NULL
) ON [PRIMARY]

Query

SELECT sal,sum(sal) over(PARTITION BY empno)
FROM emp

Query Plan

Answer 1

Plans with windowed aggregates normally use a common sub expression spool. A good write up of this type of plan is here Partitioning and the Common Subexpression Spool

Suppose the table has the following rows

CREATE TABLE [dbo].[EMP](
    [EMPNO] [int] NOT NULL,
    [SAL] [int] NULL) 

INSERT INTO [dbo].[EMP] 
VALUES (1,1),
       (1,2), 
       (1,3),
       (1,4), 
       (2,1),
       (2,2)

It has 6 rows in total with 2 distinct EMPNO values. The actual execution plan showing the actual number of rows emitted is below.

The segment iterator at the top of the plan adds a flag to the rows that pass through it indicating when it is the start of a new partition (i.e. the empno has changed).

The spool to its immediate left (primary spool) gets a row at a time from the segment iterator and inserts it into a work table in tempdb. Once it gets the flag saying that a new group has started it returns a row to the top input of the nested loops operator.

This causes the stream aggregate to be invoked over the rows in the work table (secondary spool in the plan), the SUM([SAL]) is computed then this value is joined back with the rows in the work table (the third spool operator in the plan) before the work table is truncated ready for the new group.

The primary segment spool emits a dummy row in order to get the final group processed which is why the actual number of rows emitted is shown as 3 (number of groups plus one)