Scalar Aggregate

Transact-SQL includes the aggregate functions AVG(), COUNT(), COUNT(*), MAX(), MIN(), and SUM(). Whenever an aggregate function is used in a SELECT statement that does not include a GROUP BY clause, it produces a single value, regardless of whether it is operating on all the rows in a table or on a subset of the rows defined by a WHERE clause. When an aggregate function produces a single value, the function is called a scalar aggregate, and is listed as such by SHOWPLAN.

The following example shows the use of scalar aggregate functions:

Query:

SELECT AVG(advance), SUM(ytd_sales)
FROM titles
WHERE type = 'business'

SHOWPLAN:

STEP 1
The type of query is SELECT
Scalar Aggregate
FROM TABLE
titles
Nested iteration
Table Scan

STEP 2
The type of query is SELECT

Notice that SHOWPLAN considers this a two-step query, similar to the SHOWPLAN from the GROUP BY query shown earlier. Because the query contains a scalar aggregate, which will return a single value, SQL Server keeps internally a "variable" to store the result of the aggregate function. Think of this variable as a temporary storage space to keep a running total of the aggregate function as the qualifying rows from the table are evaluated. After all rows have been evaluated from the table (STEP 1), the final value from the "variable" is then selected (STEP 2) to return the scalar aggregate result.