T-SQL Querying (2015)

---

The alternative in SQL Server is to use the window functions and eliminate duplicates with a DISTINCT clause, like so:

SELECT DISTINCT testid,
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY score)
OVER(PARTITION BY testid) AS mediandisc,
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY score)
OVER(PARTITION BY testid) AS mediancont
FROM Stats.Scores;

This query generates the following output:

testid mediandisc mediancont
---------- ---------- ----------------------
Test ABC 75 75
Test XYZ 75 77.5

From an optimization perspective, these functions are optimized very inefficiently. The query plan has a number of rounds where the rows are spooled, and then the spool is read once to compute an aggregate and once for the detail. See Chapter 5 for a discussion about more efficient alternatives.

Gaps and islands

Gaps and islands are classic problems in T-SQL that involve a sequence of values. The type of the values is usually date and time, but sometimes integer and others. The gaps task involves identifying the ranges of missing values, and the islands task involves identifying the ranges of existing values. Examples for gaps and islands tasks are identifying periods of inactivity and periods of activity, respectively. Many gaps and islands tasks can be solved efficiently using window functions.

I’ll first demonstrate solutions using a sequence of integers simply because it’s much easier to explain the logic with integers. In reality, most gaps and islands tasks involve date and time sequences. Once you figure out a technique to solve the task using a sequence of integers, you need to perform fairly minor adjustments to apply it to a date-and-time sequence.

For sample data, I’ll use the following table T1:

SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;

CREATE TABLE dbo.T1(col1 INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY);
GO

INSERT INTO dbo.T1(col1) VALUES(1),(2),(3),(7),(8),(9),(11),(15),(16),(17),(28);

Gaps

The gaps task is about identifying the ranges of missing values in the sequence as intervals. I’ll demonstrate a technique to handle the task against the table T1.

One of the simplest and most efficient techniques involves two steps. The first is to return for each current col1 value (call it cur) the immediate next value using the LEAD function (call it nxt). This is achieved with the following query:

SELECT col1 AS cur, LEAD(col1) OVER(ORDER BY col1) AS nxt
FROM dbo.T1;

This query generates the following output:

cur nxt
----------- -----------
1 2
2 3
3 7
7 8
8 9
9 11
11 15
15 16
16 17
17 28
28 NULL

We have an index on col1, so the plan for this query does a single ordered scan of the index to support the LEAD calculation.

Examine the pairs of cur-nxt values, and observe that the pairs where the difference is greater than one represent gaps. The second step involves defining a CTE based on the last query, and then filtering the pairs representing gaps, adding one to cur and subtracting one from nxt to return the actual gap information. Here’s the complete solution query:

WITH C AS
(
SELECT col1 AS cur, LEAD(col1) OVER(ORDER BY col1) AS nxt
FROM dbo.T1
)
SELECT cur + 1 AS range_from, nxt - 1 AS range_to
FROM C
WHERE nxt - cur > 1;

This query generates the following output:

range_from range_to
----------- -----------
4 6
10 10
12 14
18 27

Notice also that the pair with the NULL in nxt was naturally filtered out because the difference was NULL, NULL > 1 is unknown, and unknown gets filtered out.

Islands

The islands task is the inverse of the gaps task. Identifying islands means returning the ranges of consecutive values. I’ll first demonstrate a solution using the generic sequence of integers in T1.col1, and then I’ll use a more realistic example using a sequence of dates.

One of the most beautiful and efficient techniques to identify islands involves computing row numbers based on the order of the sequence column, and using those to compute an island identifier. To see how this can be achieved, first compute row numbers based on col1 order, like so:

SELECT col1, ROW_NUMBER() OVER(ORDER BY col1) AS rownum
FROM dbo.T1;

This query generates the following output:

col1 rownum
----------- --------------------
1 1
2 2
3 3
7 4
8 5
9 6
11 7
15 8
16 9
17 10
28 11

Look carefully at the two sequences and see if you can identify an interesting relationship between them that can help you identify islands. For this, focus your attention on one of the islands. If you do enough staring at the output, you will figure it out at some point—within an island, both sequences keep incrementing by a fixed interval of 1 integer; therefore, the difference between the two is the same within the island. Also, when switching from one island to the next rownum increases by 1, whereas col1 increases by more than 1; therefore, the difference keeps growing from one island to the next and hence is unique per island. This means you can use the difference as the island, or group, identifier. Run the following query to compute the group identifier (grp) based on this logic:

SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) AS grp
FROM dbo.T1;

This query generates the following output:

col1 grp
----------- --------------------
1 0
2 0
3 0
7 3
8 3
9 3
11 4
15 7
16 7
17 7
28 17

Because there’s an index defined on col1, the computation of the row numbers doesn’t require explicit sorting, as can be seen in the plan for this query in Figure 4-12.

FIGURE 4-12 Plan for query computing group identifier.

Most of the cost of this plan is in the ordered scan of the index PK_T1. The rest of the work involving the calculation of the row numbers and the computation of the difference between the sequences is negligible.

Finally, you need to define a CTE based on the last query, and then in the outer query group the rows by grp, return the minimum col1 value as the start of the island, and return the maximum as the end. Here’s the complete solution query:

WITH C AS
(
SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) AS grp
FROM dbo.T1
)
SELECT MIN(col1) AS range_from, MAX(col1) AS range_to
FROM C
GROUP BY grp;

This query generates the following output:

range_from range_to
----------- -----------
1 3
7 9
11 11
15 17
28 28

The col1 sequence in T1 has unique values, but you should be aware that if duplicate values were possible, the solution would not work correctly anymore. That’s because the ROW_NUMBER function generates unique values in the target partition, even for rows with the same ordering values. For the logic to be correct, you need a ranking function that assigns the same ranking values for all rows with the same ordering values, with no gaps between distinct ranking values. Fortunately, such a ranking function exists—that’s the DENSE_RANK function. Here’s the revised solution using DENSE_RANK:

WITH C AS
(
SELECT col1, col1 - DENSE_RANK() OVER(ORDER BY col1) AS grp
FROM dbo.T1
)
SELECT MIN(col1) AS range_from, MAX(col1) AS range_to
FROM C
GROUP BY grp;

Because the DENSE_RANK function works correctly both when the sequence is unique and when it isn’t, it makes sense to always use DENSE_RANK and not ROW_NUMBER. It’s just easier to explain the logic first with a unique sequence using the ROW_NUMBER function, and then examine a nonunique sequence, and then explain why DENSE_RANK is preferred.

As mentioned, in most cases in reality you will need to identify islands involving date and time sequences. As an example, suppose you need to query the Sales.Orders table in the TSQLV3 database, and identify for each shipper the islands of shipped date values. The shipped date values are stored in the shippeddate column. Clearly you can have duplicate values in this column. Also, not all orders recorded in the table are shipped orders; those that were not yet shipped have a NULL shipped date. For the purpose of identifying islands, you should simply ignore those NULLs. To support your solution, you need an index on the partitioning column shipperid, sequence column shippeddate, and tiebreaker column orderid as the keys. Because NULLs are possible and should be ignored, you add a filter that excludes NULLs from the index definition. Run the following code to create the recommended index:

USE TSQLV3;

CREATE UNIQUE INDEX idx_sid_sd_oid
ON Sales.Orders(shipperid, shippeddate, orderid)
WHERE shippeddate IS NOT NULL;

When computing the group identifier for the islands in col1 in T1, both the col1 values and the dense rank values were integers with the same interval 1. With the shippeddate sequence, things are trickier because the shipped date values and the dense rank values are of different types; one is temporal and the other is integer. Still, the interval between consecutive values in each is fixed, only in one case it’s the temporal interval 1 day and in the other it’s the integer interval 1. So, to apply the logic in this case, you subtract from the temporal shipped date value the dense rank value times the temporal interval (1 day). This way, the group identifier will be a temporal value, but like before it will be the same for all members of the same island.

Here’s the complete solution query implementing this logic:

WITH C AS
(
SELECT shipperid, shippeddate,
DATEADD(
day,
-1 * DENSE_RANK() OVER(PARTITION BY shipperid ORDER BY shippeddate),
shippeddate) AS grp
FROM Sales.Orders
WHERE shippeddate IS NOT NULL
)
SELECT shipperid,
MIN(shippeddate) AS fromdate,
MAX(shippeddate) AS todate,
COUNT(*) as numorders
FROM C
GROUP BY shipperid, grp;

A good way to understand the logic behind computing the group identifier is to first run the inner query, which defines the CTE C, and examine the result. You will get the following output:

shipperid shippeddate grp
----------- ----------- ----------
1 2013-07-10 2013-07-09
1 2013-07-15 2013-07-13
1 2013-07-23 2013-07-20
1 2013-07-29 2013-07-25
1 2013-08-02 2013-07-28
1 2013-08-06 2013-07-31
1 2013-08-09 2013-08-02
1 2013-08-09 2013-08-02
...
1 2014-01-10 2013-12-07
1 2014-01-13 2013-12-09
1 2014-01-14 2013-12-09
1 2014-01-14 2013-12-09
1 2014-01-22 2013-12-16
...

You will notice that the dates that belong to the same island get the same group identifier.

The plan for the inner query defining the CTE C can be seen in Figure 4-13.

FIGURE 4-13 Plan for inner query computing group identifier with dates.

This plan is very efficient. It performs an ordered scan of the index you prepared for this solution to support the computation of the dense rank values. The cost of the rest of the work in the plan is negligible.

The complete solution generates the ranges of shipped date values as the following output shows:

shipperid fromdate todate numorders
----------- ---------- ---------- -----------
1 2013-07-10 2013-07-10 1
2 2013-07-11 2013-07-12 2
1 2013-07-15 2013-07-15 1
2 2013-07-16 2013-07-17 2
3 2013-07-15 2013-07-16 2
...

There are different variations of islands challenges. A common and interesting one is when you need to identify islands while ignoring gaps of up to a certain size. For example, in our challenge to identify islands of shipped dates per shipper, suppose you need to ignore gaps of up to 7 days.

A very elegant solution to this variation involves the use of two window functions: LAG and SUM. You use LAG to return for each current order the shipped date of the previous order that was shipped by the same shipper. Using a CASE expression, you compute a flag called startflagthat is set to 0 if the difference between the previous and the current dates is less than or equal to 7 and 1 otherwise. Here’s the code implementing this step:

SELECT shipperid, shippeddate, orderid,
CASE WHEN DATEDIFF(day,
LAG(shippeddate) OVER(PARTITION BY shipperid ORDER BY shippeddate, orderid),
shippeddate) <= 7 THEN 0 ELSE 1 END AS startflag
FROM Sales.Orders
WHERE shippeddate IS NOT NULL;

This query generates the following output:

shipperid shippeddate orderid startflag
----------- ----------- ----------- -----------
1 2013-07-10 10249 1
1 2013-07-15 10251 0
1 2013-07-23 10258 1
1 2013-07-29 10260 0
1 2013-08-02 10270 0
1 2013-08-06 10267 0
1 2013-08-09 10269 0
1 2013-08-09 10275 0
1 2013-08-12 10265 0
1 2013-08-16 10274 0
1 2013-08-21 10281 0
1 2013-08-21 10282 0
1 2013-08-27 10284 0
1 2013-09-03 10288 0
1 2013-09-03 10290 0
1 2013-09-11 10296 1
1 2013-09-12 10280 0
...

As you can see, the startflag flag marks the start of each island with a 1 and a nonstart of an island with a 0. Next, using the SUM window aggregate function, you compute the group identifier as the running total of the startflag flag over time, like so:

WITH C1 AS
(
SELECT shipperid, shippeddate, orderid,
CASE WHEN DATEDIFF(day,
LAG(shippeddate) OVER(PARTITION BY shipperid ORDER BY shippeddate, orderid),
shippeddate) <= 7 THEN 0 ELSE 1 END AS startflag
FROM Sales.Orders
WHERE shippeddate IS NOT NULL
)
SELECT *,
SUM(startflag) OVER(PARTITION BY shipperid
ORDER BY shippeddate, orderid
ROWS UNBOUNDED PRECEDING) AS grp
FROM C1;

This query generates the following output:

shipperid shippeddate orderid startflag grp
----------- ----------- ----------- ----------- -----------
1 2013-07-10 10249 1 1
1 2013-07-15 10251 0 1
1 2013-07-23 10258 1 2
1 2013-07-29 10260 0 2
1 2013-08-02 10270 0 2
1 2013-08-06 10267 0 2
1 2013-08-09 10269 0 2
1 2013-08-09 10275 0 2
1 2013-08-12 10265 0 2
1 2013-08-16 10274 0 2
1 2013-08-21 10281 0 2
1 2013-08-21 10282 0 2
1 2013-08-27 10284 0 2
1 2013-09-03 10288 0 2
1 2013-09-03 10290 0 2
1 2013-09-11 10296 1 3
1 2013-09-12 10280 0 3
...

Observe that the group identifier, as required, is the same for all members of the same island and is unique per island. The final step is to group the rows by shipperid and grp, and return the minimum and maximum shipped dates per island, like so:

WITH C1 AS
(
SELECT shipperid, shippeddate, orderid,
CASE WHEN DATEDIFF(day,
LAG(shippeddate) OVER(PARTITION BY shipperid ORDER BY shippeddate, orderid),
shippeddate) <= 7 THEN 0 ELSE 1 END AS startflag
FROM Sales.Orders
WHERE shippeddate IS NOT NULL
),
C2 AS
(
SELECT *,
SUM(startflag) OVER(PARTITION BY shipperid
ORDER BY shippeddate, orderid
ROWS UNBOUNDED PRECEDING) AS grp
FROM C1
)
SELECT shipperid,
MIN(shippeddate) AS fromdate,
MAX(shippeddate) AS todate,
COUNT(*) as numorders
FROM C2
GROUP BY shipperid, grp;

This query generates the following output:

shipperid fromdate todate numorders
----------- ---------- ---------- -----------
2 2014-12-31 2015-05-06 118
3 2013-09-11 2013-09-11 1
2 2013-08-26 2013-11-15 33
1 2014-04-04 2014-05-07 11
3 2013-07-15 2013-08-02 8
3 2013-11-18 2014-01-28 31
1 2013-12-13 2014-01-14 12
3 2014-05-12 2014-05-27 5
1 2014-08-06 2014-12-15 57
2 2014-06-18 2014-07-04 8
3 2013-08-12 2013-08-14 3
...

Examine the query plan for this solution, which is shown in Figure 4-14.

FIGURE 4-14 Plan for query identifying islands ignoring gaps of up to seven days.

Amazingly, not even one sort operation was required throughout the plan. Index order was relied on for the calculation of both the LAG and SUM window functions.

When you’re done, run the following code to drop the index you created for this example:

DROP INDEX idx_sid_sd_oid ON Sales.Orders;

Pivoting

Pivoting is an operation that rotates data from a state of rows to a state of columns. There are different reasons to pivot data. A common reason is related to reporting: you have a certain amount of data to present but a limited space in the web page or printed report. Pivoting helps you fit more data in less space. Also, visually it’s often convenient to analyze data presented as the intersection of dimensions represented by rows and columns. Other reasons to use pivoting are to handle custom aggregates (as I will discuss later in the chapter), solve relational division problems, and more.

There are two kinds of pivot operations: one-to-one and many-to-one. I’ll describe both in the following sections.

One-to-one pivot

In a one-to-one pivot operation, you return in the result the exact same data you had in the source, only using a different shape. Multiple values from the same column in different source rows are returned as different column values of the same result row. This is best explained through a practical example.

As an example for a scenario where you need to apply a one-to-one pivot operation, I’ll use a modeling scheme known as open schema and also as dynamic schema. You have a table called OpenSchema where you keep track of object attribute values using a model known as EAV (for entity, attribute, and value). In this model, you store each object attribute value in a separate row. Each object can have a different set of applicable attributes. Each attribute can be of a different type, so SQL_VARIANT is used as the type for the value column.

Run the following code to create the OpenSchema table and populate it with some sample data:

USE tempdb;

IF OBJECT_ID(N'dbo.OpenSchema', N'U') IS NOT NULL DROP TABLE dbo.OpenSchema;

CREATE TABLE dbo.OpenSchema
(
objectid INT NOT NULL,
attribute NVARCHAR(30) NOT NULL,
value SQL_VARIANT NOT NULL,
CONSTRAINT PK_OpenSchema PRIMARY KEY (objectid, attribute)
);
GO

INSERT INTO dbo.OpenSchema(objectid, attribute, value) VALUES
(1, N'attr1', CAST(CAST('ABC' AS VARCHAR(10)) AS SQL_VARIANT)),
(1, N'attr2', CAST(CAST(10 AS INT) AS SQL_VARIANT)),
(1, N'attr3', CAST(CAST('20130101' AS DATE) AS SQL_VARIANT)),
(2, N'attr2', CAST(CAST(12 AS INT) AS SQL_VARIANT)),
(2, N'attr3', CAST(CAST('20150101' AS DATE) AS SQL_VARIANT)),
(2, N'attr4', CAST(CAST('Y' AS CHAR(1)) AS SQL_VARIANT)),
(2, N'attr5', CAST(CAST(13.7 AS NUMERIC(9,3))AS SQL_VARIANT)),
(3, N'attr1', CAST(CAST('XYZ' AS VARCHAR(10)) AS SQL_VARIANT)),
(3, N'attr2', CAST(CAST(20 AS INT) AS SQL_VARIANT)),
(3, N'attr3', CAST(CAST('20140101' AS DATE) AS SQL_VARIANT));

Run the following query to show the contents of the table:

SELECT objectid, attribute, value FROM dbo.OpenSchema;

This query generates the following output:

objectid attribute value
--------- ---------- ------------------------
1 attr1 ABC
1 attr2 10
1 attr3 2013-01-01 00:00:00.000
2 attr2 12
2 attr3 2015-01-01 00:00:00.000
2 attr4 Y
2 attr5 13.700
3 attr1 XYZ
3 attr2 20
3 attr3 2014-01-01 00:00:00.000

As you can imagine, implementing dynamic schema using the EAV model has advantages and disadvantages compared to alternative models. But that’s not your concern—for reasons that have already been debated, this model was chosen. The problem is that attempting to query the data in its current form can be complex. The values of different attributes for the same object are stored in different rows, so even simple requests can result in complex relational division queries. To make reporting simpler, once a night the data needs to be pivoted and the pivoted form should be persisted in a table. Then reports can be issued off of that table, requiring simpler and more efficient queries.

Your task is to write a query that pivots the data into the following form:

objectid attr1 attr2 attr3 attr4 attr5
----------- ------ ------ ------------------------ ------ -------
1 ABC 10 2013-01-01 00:00:00.000 NULL NULL
2 NULL 12 2015-01-01 00:00:00.000 Y 13.700
3 XYZ 20 2014-01-01 00:00:00.000 NULL NULL

The result of your query should have a row per distinct object ID, a column per distinct attribute, and the applicable value in the intersection of each object ID and attribute.

A pivot operation involves three steps, described here using the open schema example:

1. Grouping by objectid Each set of rows that are associated with the same object are going to be represented by a single result row. You achieve this step by grouping the data. In our example, the grouping column is objectid. In T-SQL, you simply use GROUP BY objectid.

2. Spreading attribute IN (attr1, attr2, attr3, attr4, attr5) The values in the value column should be spread to different result columns based on the attribute that the current row is associated with. The spreading column in our case is the attribute column. You need to identify the distinct elements in the spreading column; those become column names in the result. In our case, the distinct elements are attr1, attr2, attr3, attr4, and attr5. For each such spreading element, you need to write an expression that implements conditional logic to return the value only when it’s applicable. For example, for the element attr1, the logic should be as follows: if the current row is associated with attr1, return the value from the value column; otherwise, return a NULL as a placeholder. To implement this logic in T-SQL, you use the expression CASE WHEN attribute = ‘attr1’ THEN value END. When you don’t indicate an ELSE clause, ELSE NULL is assumed by default. You use such an expression for each distinct spreading element.

3. Aggregating MAX(val) Remember that the query is implemented as a grouped query and each object is going to be represented by a single result row. With one-to-one pivoting per group, each CASE expression will return at most one applicable value; all the rest will be NULLs. The purpose of the third step is to return that applicable value if it exists and to return NULL otherwise. Because the query is a grouped query, the only tool you are allowed to use for this purpose is an aggregate function applied to the result of the CASE expression. With at most one applicable value per group, you could use the function MAX (or MIN). The function ignores NULLs and works with most data types. So the aggregate function in our case is MAX, and it is applied to the results of the CASE expression (the value column when it’s applicable). To summarize, our aggregation element is MAX(val).

The three steps combined give you the following solution query:

SELECT objectid,
MAX(CASE WHEN attribute = 'attr1' THEN value END) AS attr1,
MAX(CASE WHEN attribute = 'attr2' THEN value END) AS attr2,
MAX(CASE WHEN attribute = 'attr3' THEN value END) AS attr3,
MAX(CASE WHEN attribute = 'attr4' THEN value END) AS attr4,
MAX(CASE WHEN attribute = 'attr5' THEN value END) AS attr5
FROM dbo.OpenSchema
GROUP BY objectid;

The execution plan for this query is shown in Figure 4-15.

FIGURE 4-15 Plan for a standard pivot query.

The plan looks like a plan for a normal grouped query with the extra Compute Scalar operator handling the CASE expressions. The index PK_OpenSchema is defined based on the key-list (objectid, attribute), so it has the rows in the desired order, and because it’s a clustered index it covers the query. The plan scans the index in order and then provides the rows in that order to the Stream Aggregate operator. As you can see, it’s a very efficient plan.

A question that often comes up is how do you deal with a case where you don’t know ahead of time what the distinct spreading values are? The answer is that you have to query the distinct values from the data, construct a string with the pivot query based on those values, and then use dynamic SQL to execute it. I will demonstrate how this can be done later in the chapter when discussing specialized solutions for custom aggregations.

The preceding technique to pivot data uses only standard SQL constructs, so it’s a very portable technique. Also, as you will see in the section about many-to-one pivoting, you can easily extend it to support multiple aggregates when needed without adding much cost.

T-SQL supports an alternative technique using a proprietary table operator called PIVOT. Like any table operator, you use it in the FROM clause of your query. You use the following syntax:

SELECT <col_list>
FROM <source> PIVOT(<agg_func>(<agg_col>) FOR <spreading_col> IN (<spreading_elements>)) AS P;

Identifying the elements involved in our pivot task, the solution query looks like this:

SELECT objectid, attr1, attr2, attr3, attr4, attr5
FROM dbo.OpenSchema
PIVOT(MAX(value) FOR attribute IN(attr1, attr2, attr3, attr4, attr5)) AS P;

Observe that the spreading elements are specified in this syntax already as identifiers of column names and not the literal character strings they used to be in the source. If the identifier happens to be irregular (starts with a digit, has spaces, is a reserved keyword, and so on), you need to delimit it with double quotes or square brackets, as in “2015”.

The plan for this query is shown in Figure 4-16.

FIGURE 4-16 Plan for a pivot query with the PIVOT operator.

If you look at the properties of the Stream Aggregate operator, you will find a small surprise: SQL Server generated CASE expressions similar to the ones you used in the previous solution. So you realize that from a performance perspective, the two solutions are treated pretty much the same.

A curious thing about the syntax of the PIVOT operator is that while you do indicate the spreading and aggregation parts, you do not indicate the grouping columns. This part is implied; the grouping columns are all columns from the queried table that were not mentioned as the spreading and aggregation columns. In our case, the only column left in the OpenSchema table is objectid. But you realize the danger in this syntax; if you add columns to the table in the future, the next time you run the query those columns implicitly also become part of the grouping. For this reason, it is recommended that instead of querying the table directly as the source for the PIVOT operator, you define a table expression like a derived table or CTE, where you project only the columns that need to play a role in the pivot operation. Here’s an example for implementing this approach using a derived table:

SELECT objectid, attr1, attr2, attr3, attr4, attr5
FROM (SELECT objectid, attribute, value FROM dbo.OpenSchema) AS D
PIVOT(MAX(value) FOR attribute IN(attr1, attr2, attr3, attr4, attr5)) AS P;

This way, your code is safe and you don’t need to be concerned about future additions of columns to the table.

This syntax has the advantage that it is less verbose than the previous solution. However, it has a few disadvantages. It is proprietary, so it is less portable. You are limited to only one aggregate function. It might not be apparent why you would want multiple functions when discussing one-to-one pivoting, but with many-to-one pivoting it could be needed. This syntax doesn’t allow expressions that manipulate the spreading or aggregation columns. Any needed manipulation has to be done in a table expression that you will use as the input for the PIVOT operator.

In case you were wondering, T-SQL doesn’t support providing a subquery as input to the IN clause. Like I explained with the previous solution, if the spreading values are unknown ahead of time, you need to construct the query string yourself and use dynamic SQL to execute it. I will demonstrate an example later in the chapter.

Many-to-one pivot

A many-to-one pivot is a kind of pivot where you can have multiple source rows represented by a single target row-column intersection. Although the aggregate function used in the one-to-one case is artificial, in the many-to-one case it’s meaningful. That’s pretty much the difference. Otherwise, the techniques used in both cases are the same.

As an example, suppose you need to query the Sales.OrderValues view in the TSQLV3 database. You want to pivot data so that you return a row per customer, a column per order year, and in the intersection of each customer and year, you want to return the sum of applicable values. The grouping column in this case is custid. The spreading column is orderyear (the result of the YEAR function applied to the orderdate column), with the distinct spreading elements currently being 2013, 2014, and 2015. The aggregation function is SUM applied to the column val. To allow using the alias orderyear in the computations, you can define it in a table expression.

Here’s the solution query using the standard method with the grouping and CASE expressions:

USE TSQLV3;

SELECT custid,
SUM(CASE WHEN orderyear = 2013 THEN val END) AS [2013],
SUM(CASE WHEN orderyear = 2014 THEN val END) AS [2014],
SUM(CASE WHEN orderyear = 2015 THEN val END) AS [2015]
FROM (SELECT custid, YEAR(orderdate) AS orderyear, val
FROM Sales.OrderValues) AS D
GROUP BY custid;

This query generates the following output:

custid 2013 2014 2015
------- -------- --------- ---------
1 NULL 2022.50 2250.50
2 88.80 799.75 514.40
3 403.20 5960.78 660.00
4 1379.00 6406.90 5604.75
5 4324.40 13849.02 6754.16
6 NULL 1079.80 2160.00
7 9986.20 7817.88 730.00
8 982.00 3026.85 224.00
9 4074.28 11208.36 6680.61
10 1832.80 7630.25 11338.56
...

Here’s the solution query using the PIVOT operator:

SELECT custid, [2013],[2014],[2015]
FROM (SELECT custid, YEAR(orderdate) AS orderyear, val
FROM Sales.OrderValues) AS D
PIVOT(SUM(val) FOR orderyear IN([2013],[2014],[2015])) AS P;

As mentioned, the advantage of the PIVOT operator is in the brevity of the code, and one of its disadvantages is that it’s limited to only one aggregate. So what if you need to support multiple aggregates using the standard solution, and you end up with so many expressions that the length of the code becomes an issue for you? There is a method that can help you shorten the query string.

You create a helper table called Matrix with a row and a column per distinct spreading value. You fill the table with 1s only when the row and column represent the same spreading value. Here’s the code to create and populate the Matrix table to support our pivot task, followed by a query that presents its contents:

IF OBJECT_ID(N'dbo.Matrix', N'U') IS NOT NULL DROP TABLE dbo.Matrix;

CREATE TABLE dbo.Matrix
(
orderyear INT NOT NULL PRIMARY KEY,
y2013 INT NULL,
y2014 INT NULL,
y2015 INT NULL
);
GO

INSERT INTO dbo.Matrix(orderyear, y2013) VALUES(2013, 1);
INSERT INTO dbo.Matrix(orderyear, y2014) VALUES(2014, 1);
INSERT INTO dbo.Matrix(orderyear, y2015) VALUES(2015, 1);

SELECT orderyear, y2013, y2014, y2015 FROM dbo.Matrix;

The query generates the following output:

orderyear y2013 y2014 y2015
----------- ----------- ----------- -----------
2013 1 NULL NULL
2014 NULL 1 NULL
2015 NULL NULL 1

Then to shorten the standard solution’s length, you do the following:

Join the source table (D in our case) with the Matrix table, matching rows based on an equality between the orderyear values on both sides.

Instead of the CASE expression form of SUM(CASE WHEN orderyear = 2015 THEN val END), use SUM(val*y2015). The two are equivalent because in both cases, when the year is 2015 the value in the val column is returned (y2015 is 1), and when the year is different a NULL is returned.

Here’s the complete solution query:

SELECT custid,
SUM(val*y2013) AS [2013],
SUM(val*y2014) AS [2014],
SUM(val*y2015) AS [2015]
FROM (SELECT custid, YEAR(orderdate) AS orderyear, val
FROM Sales.OrderValues) AS D
INNER JOIN dbo.Matrix AS M ON D.orderyear = M.orderyear
GROUP BY custid;

If you want to compute a count instead of a sum, without the Matrix table you either replace the SUM function with COUNT or use the following trick:

SELECT custid,
SUM(CASE WHEN orderyear = 2013 THEN 1 END) AS [2013],
SUM(CASE WHEN orderyear = 2014 THEN 1 END) AS [2014],
SUM(CASE WHEN orderyear = 2015 THEN 1 END) AS [2015]
FROM (SELECT custid, YEAR(orderdate) AS orderyear
FROM Sales.Orders) AS D
GROUP BY custid;

You return a 1 for each applicable order, and therefore the sum of applicable 1s gives you the count. This query generates the following output:

custid 2013 2014 2015
------- ----- ----- -----
1 NULL 3 3
2 1 2 1
3 1 5 1
4 2 7 4
5 3 10 5
6 NULL 4 3
7 3 7 1
8 1 1 1
9 3 8 6
10 1 5 8
...

Using the Matrix table, you just apply the SUM function directly to the Matrix column, like so:

SELECT custid,
SUM(y2013) AS [2013],
SUM(y2014) AS [2014],
SUM(y2015) AS [2015]
FROM (SELECT custid, YEAR(orderdate) AS orderyear
FROM Sales.Orders) AS D
INNER JOIN dbo.Matrix AS M ON D.orderyear = M.orderyear
GROUP BY custid;

Because the Matrix column has a 1 only when the order was placed in that year, the SUM function computes a count in practice.

Remember one of the benefits in using the standard method was the fact that it can support multiple aggregate functions without adding significant cost. Here’s an example computing the sum, average, and count:

SELECT custid,
SUM(val*y2013) AS sum2013,
SUM(val*y2014) AS sum2014,
SUM(val*y2015) AS sum2015,
AVG(val*y2013) AS avg2013,
AVG(val*y2014) AS avg2014,
AVG(val*y2015) AS avg2015,
SUM(y2013) AS cnt2013,
SUM(y2014) AS cnt2014,
SUM(y2015) AS cnt2015
FROM (SELECT custid, YEAR(orderdate) AS orderyear, val
FROM Sales.OrderValues) AS D
INNER JOIN dbo.Matrix AS M ON D.orderyear = M.orderyear
GROUP BY custid;

Recall the motivation for using the technique with the Matrix table was to shorten the query string. The benefit might not be apparent when you have a small number of expressions, but imagine if you had dozens or hundreds; then the benefit is realized.

Unpivoting

Unpivoting is an operation that rotates data from a state of columns to rows. The starting point for an unpivot task is source data stored in pivoted form. As an example, use the following code to create the table PvtOrders, fill it with sample data, and then query it to present its contents:

IF OBJECT_ID(N'dbo.PvtOrders', N'U') IS NOT NULL DROP TABLE dbo.PvtOrders;

SELECT custid, [2013], [2014], [2015]
INTO dbo.PvtOrders
FROM (SELECT custid, YEAR(orderdate) AS orderyear, val
FROM Sales.OrderValues) AS D
PIVOT(SUM(val) FOR orderyear IN([2013],[2014],[2015])) AS P;

SELECT custid, [2013], [2014], [2015] FROM dbo.PvtOrders;

The query generates the following output:

custid 2013 2014 2015
------- -------- --------- ---------
1 NULL 2022.50 2250.50
2 88.80 799.75 514.40
3 403.20 5960.78 660.00
4 1379.00 6406.90 5604.75
5 4324.40 13849.02 6754.16
6 NULL 1079.80 2160.00
7 9986.20 7817.88 730.00
8 982.00 3026.85 224.00
9 4074.28 11208.36 6680.61
10 1832.80 7630.25 11338.56
...

It’s common for data to be stored like this in spreadsheets. Imagine you just imported the data from a spreadsheet to the database. It’s not very convenient to query the data in its current shape, so you want to unpivot it to a form that has a row per customer and year. In the following sections, I will describe a number of methods to achieve this unpivot task.

Unpivoting with CROSS JOIN and VALUES

You can split an unpivot task into three steps:

1. Generate copies.

2. Extract the element.

3. Remove NULLs.

I will describe these steps applied to our example.

The first step is about generating copies from the source rows. You need as many copies as the number of columns that you need to unpivot. In our case, the columns you need to unpivot represent order years, so you need a copy per order year. You can use the VALUES clause to define a derived table called Y holding order years, like so:

(VALUES(2013),(2014),(2015)) AS Y(orderyear)

Here’s a query against the derived table presenting its contents:

SELECT orderyear FROM (VALUES(2013),(2014),(2015)) AS Y(orderyear);

This query generates the following output:

orderyear
-----------
2013
2014
2015

To generate copies, you perform a cross join between the source table PvtOrders and the table Y holding the order years, like so:

SELECT custid, [2013], [2014], [2015], orderyear
FROM dbo.PvtOrders
CROSS JOIN (VALUES(2013),(2014),(2015)) AS Y(orderyear);

This query generates the following output:

custid 2013 2014 2015 orderyear
------- ------- -------- -------- ----------
1 NULL 2022.50 2250.50 2013
1 NULL 2022.50 2250.50 2014
1 NULL 2022.50 2250.50 2015
2 88.80 799.75 514.40 2013
2 88.80 799.75 514.40 2014
2 88.80 799.75 514.40 2015
3 403.20 5960.78 660.00 2013
3 403.20 5960.78 660.00 2014
3 403.20 5960.78 660.00 2015
...

The second step is about extracting the value from the right column in each copy to generate the result val column. What determines which column’s value to return is the value in the orderyear column. When it’s 2013, you need to return the value from the column [2013]; when it’s 2014, you need to return the value from the column [2014], and so on. To implement this step, you can use a simple CASE expression, like so:

SELECT custid, orderyear,
CASE orderyear
WHEN 2013 THEN [2013]
WHEN 2014 THEN [2014]
WHEN 2015 THEN [2015]
END AS val
FROM dbo.PvtOrders
CROSS JOIN (VALUES(2013),(2014),(2015)) AS Y(orderyear);

This query generates the following output:

custid orderyear val
----------- ----------- --------
1 2013 NULL
1 2014 2022.50
1 2015 2250.50
2 2013 88.80
2 2014 799.75
2 2015 514.40
3 2013 403.20
3 2014 5960.78
3 2015 660.00
...

Finally, the third step is supposed to remove the rows that are inapplicable (representing no activity). Those are the ones with a NULL in the val column. You cannot refer to the val column alias in the WHERE clause because it’s defined in the SELECT list. As a workaround, you can define the alias in a VALUES clause using the CROSS APPLY operator, and then refer to it in the query’s WHERE clause, like so:

SELECT custid, orderyear, val
FROM dbo.PvtOrders
CROSS JOIN (VALUES(2013),(2014),(2015)) AS Y(orderyear)
CROSS APPLY (VALUES(CASE orderyear
WHEN 2013 THEN [2013]
WHEN 2014 THEN [2014]
WHEN 2015 THEN [2015]
END)) AS A(val)
WHERE val IS NOT NULL;

This query generates the following output where the rows with a NULL in the val column are removed:

custid orderyear val
----------- ----------- ---------------------------------------
1 2014 2022.50
1 2015 2250.50
2 2013 88.80
2 2014 799.75
2 2015 514.40
3 2013 403.20
3 2014 5960.78
3 2015 660.00
...

Unpivoting with CROSS APPLY and VALUES

There’s a more elegant way to achieve unpivoting compared to the previous method. You combine the steps that generate the copies and extract the element into one step by using CROSS APPLY and VALUES. The CROSS APPLY operator gives the right table expression visibility to the elements of the left row. The right table expression can use the VALUES clause to produce multiple rows out of each left row. Each row is formed from a constant for the result orderyear column, and the corresponding column value for the result val column.

Here’s the solution query:

SELECT custid, orderyear, val
FROM dbo.PvtOrders
CROSS APPLY (VALUES(2013, [2013]),(2014, [2014]),(2015, [2015])) AS A(orderyear, val)
WHERE val IS NOT NULL;

What’s interesting about this technique is that it can be used in more complex unpivoting tasks. To demonstrate such a task, I’ll use a table called Sales that you create and populate by running the following code:

USE tempdb;
IF OBJECT_ID(N'dbo.Sales', N'U') IS NOT NULL DROP TABLE dbo.Sales;
GO

CREATE TABLE dbo.Sales
(
custid VARCHAR(10) NOT NULL,
qty2013 INT NULL,
qty2014 INT NULL,
qty2015 INT NULL,
val2013 MONEY NULL,
val2014 MONEY NULL,
val2015 MONEY NULL,
CONSTRAINT PK_Sales PRIMARY KEY(custid)
);

INSERT INTO dbo.Sales
(custid, qty2013, qty2014, qty2015, val2013, val2014, val2015)
VALUES
('A', 606,113,781,4632.00,6877.00,4815.00),
('B', 243,861,637,2125.00,8413.00,4476.00),
('C', 932,117,202,9068.00,342.00,9083.00),
('D', 915,833,138,1131.00,9923.00,4164.00),
('E', 822,246,870,1907.00,3860.00,7399.00);

Here you need to unpivot not just one, but two sets of columns; one with quantities in different order years, and another with values. To achieve this, you use a solution similar to the last one, only each row in the VALUES clause is formed from three elements: the constant for the order year, the column holding the quantity for that year, and the column holding the value for that year. Here’s the complete solution query:

SELECT custid, salesyear, qty, val
FROM dbo.Sales
CROSS APPLY
(VALUES(2013, qty2013, val2013),
(2014, qty2014, val2014),
(2015, qty2015, val2015)) AS A(salesyear, qty, val)
WHERE qty IS NOT NULL OR val IS NOT NULL;

This query generates the following output:

custid salesyear qty val
---------- ----------- ----------- ---------------------
A 2013 606 4632.00
A 2014 113 6877.00
A 2015 781 4815.00
B 2013 243 2125.00
B 2014 861 8413.00
B 2015 637 4476.00
C 2013 932 9068.00
C 2014 117 342.00
C 2015 202 9083.00
D 2013 915 1131.00
D 2014 833 9923.00
D 2015 138 4164.00
E 2013 822 1907.00
E 2014 246 3860.00
E 2015 870 7399.00

Using the UNPIVOT operator

Just like with PIVOT, T-SQL supports a table operator called UNPIVOT. It operates on the table provided to it as its left input, and it generates a result table as its output. The design of the UNPIVOT operator is elegant, requiring you to provide the minimal needed information in its specification. Never mind how many columns you need to unpivot, you will always generate two result columns out of them—one to hold the source column values and another to hold the source column names. So the three things you need to provide to the UNPIVOT operator are (specified in order and applied to the PvtOrders table as the input):

1. Values column You need to provide the name you want to assign to the column that will hold the source column values. In our case, we want to name this column val. The type of this result column is the same as the type of the source columns you’re unpivoting. (They all must have the same type.)

2. Names column You need to provide the name you want to assign to the column that will hold the source column names. In our case, we want to name this column orderyear. The type of this column is NVARCHAR(128).

3. Source columns You need to provide the names of the source columns that you want to unpivot. In our case, those are [2013],[2014],[2015].

With the preceding three elements in mind, the syntax for UNPIVOT is as follows:

SELECT <col_list>
FROM <source> UNPIVOT(<values_col> FOR <names_col> IN (<source_cols>)) AS U;

Applied to our example with the PvtOrders table as input, you get the following query:

USE TSQLV3;

SELECT custid, orderyear, val
FROM dbo.PvtOrders
UNPIVOT(val FOR orderyear IN([2013],[2014],[2015])) AS U;

This query generates the following output:

custid orderyear val
------- ---------- --------
1 2014 2022.50
1 2015 2250.50
2 2013 88.80
2 2014 799.75
2 2015 514.40
3 2013 403.20
3 2014 5960.78
3 2015 660.00
...

The method using the UNPIVOT operator is short and elegant, but it is limited to unpivoting only one set of columns.

From a performance perspective, the method with CROSS JOIN and VALUES is the slowest. The one with CROSS APPLY and VALUES and the one with UNPIVOT get similar plans, although it seems like in more cases the method with CROSS APPLY and VALUES tends to get a parallel plan by default when there’s enough data involved.

Custom aggregations

Custom aggregations are aggregate calculations you need to perform but are not provided as built-in aggregate functions. As examples, think of calculations like aggregate string concatenation, aggregate product, and so on.

To demonstrate solutions for custom aggregations, I’ll use a table called Groups, which you create and populate by running the following code:

USE tempdb;

IF OBJECT_ID(N'dbo.Groups', N'U') IS NOT NULL DROP TABLE dbo.Groups;

CREATE TABLE dbo.Groups
(
groupid VARCHAR(10) NOT NULL,
memberid INT NOT NULL,
string VARCHAR(10) NOT NULL,
val INT NOT NULL,
PRIMARY KEY (groupid, memberid)
);
GO

INSERT INTO dbo.Groups(groupid, memberid, string, val) VALUES
('a', 3, 'stra1', 6),
('a', 9, 'stra2', 7),
('b', 2, 'strb1', 3),
('b', 4, 'strb2', 7),
('b', 5, 'strb3', 3),
('b', 9, 'strb4', 11),
('c', 3, 'strc1', 8),
('c', 7, 'strc2', 10),
('c', 9, 'strc3', 12);

Run the following code to show the contents of the table:

SELECT groupid, memberid, string, val FROM dbo.Groups;

This query generates the following output:

groupid memberid string val
---------- ----------- ---------- -----------
a 3 stra1 6
a 9 stra2 7
b 2 strb1 3
b 4 strb2 7
b 5 strb3 3
b 9 strb4 11
c 3 strc1 8
c 7 strc2 10
c 9 strc3 12

Suppose you want to concatenate per group the values in the column string based on the order of the values in the column memberid. The desired output looks like this:

groupid string
---------- -------------------------------------------
a stra1,stra2
b strb1,strb2,strb3,strb4
c strc1,strc2,strc3

There are four main types of solutions you can use:

Using a cursor

Using a CLR user-defined aggregate (UDA)

Using pivoting

Using a specialized solution

The type using a CLR user-defined aggregate is described in Chapter 9, “Programmable objects.” I’ll describe the other three types here.

Using a cursor

Recall the discussion in Chapter 2, “Query tuning,” comparing iterative and relational solutions. I demonstrated a cursor-based solution for computing a maximum aggregate. In a similar way, you could compute a string concatenation aggregate. You feed the rows to the cursor sorted by the group columns and ordering columns (groupid and memberid in our example). You keep fetching the cursor records in a loop concatenating the current value to what you have concatenated so far in a variable. If the group changes, you write the result for the last group in a table variable and reset the variable holding the concatenated values. Here’s the complete solution code:

DECLARE @Result AS TABLE(groupid VARCHAR(10), string VARCHAR(8000));

DECLARE
@groupid AS VARCHAR(10), @prvgroupid AS VARCHAR(10),
@string AS VARCHAR(10), @aggstring AS VARCHAR(8000);

DECLARE C CURSOR FAST_FORWARD FOR
SELECT groupid, string FROM dbo.Groups ORDER BY groupid, memberid;

OPEN C;

FETCH NEXT FROM C INTO @groupid, @string;

WHILE @@FETCH_STATUS = 0
BEGIN
IF @groupid <> @prvgroupid
BEGIN
INSERT INTO @Result VALUES(@prvgroupid, @aggstring);
SET @aggstring = NULL;
END;

SELECT
@aggstring = COALESCE(@aggstring + ',', '') + @string,
@prvgroupid = @groupid;

FETCH NEXT FROM C INTO @groupid, @string;
END

IF @prvgroupid IS NOT NULL
INSERT INTO @Result VALUES(@prvgroupid, @aggstring);

CLOSE C;
DEALLOCATE C;

SELECT groupid, string FROM @Result;

As discussed in Chapter 2, the downsides of a cursor-based solution are that it relies on a nonrelational approach and that iterative constructs in T-SQL are very slow.

The upsides of this solution are that you have control over order and the solution itself doesn’t impose any special restrictions on the group size, other than the ones imposed by the data type you use.

Using pivoting

Another approach to handling custom aggregations is using pivoting. This approach is good when the number of elements is small and capped at a certain maximum.

The one tricky thing about using pivoting is that the source table doesn’t have all the required elements. Remember that for pivoting you need to identify three things: the grouping column, spreading column, and aggregate function and column. In our example, the grouping column isgroupid and the aggregate function and column are MAX applied to string, but the spreading column is missing. You need the spreading column values to be common across groups. The solution is to compute row numbers that are partitioned by groupid and ordered by memberid. Assuming there’s a maximum number of elements per group (say, four in our example), you can refer to that many row numbers as the spreading values. Here’s the solution query using pivoting:

SELECT groupid,
[1]
+ COALESCE(',' + [2], '')
+ COALESCE(',' + [3], '')
+ COALESCE(',' + [4], '') AS string
FROM (SELECT groupid, string,
ROW_NUMBER() OVER(PARTITION BY groupid ORDER BY memberid) AS rn
FROM dbo.Groups AS A) AS D
PIVOT(MAX(string) FOR rn IN([1],[2],[3],[4])) AS P;

A group will have at least one element, but it’s not guaranteed that there will be more; the rest of the elements could be NULLs in the result of the pivot query. The COALESCE function is used to replace ‘,’ + <element> with an empty string in case that element number doesn’t exist. A more elegant way to achieve the same thing is using the CONCAT function, like so:

SELECT groupid,
CONCAT([1], ','+[2], ','+[3], ','+[4]) AS string
FROM (SELECT groupid, string,
ROW_NUMBER() OVER(PARTITION BY groupid ORDER BY memberid) AS rn
FROM dbo.Groups AS A) AS D
PIVOT(MAX(string) FOR rn IN([1],[2],[3],[4])) AS P;

The function concatenates its inputs, replacing NULL inputs with empty strings by default.

The advantage of using pivoting to handle custom aggregations is improved performance. Remember that pivot queries are optimized as grouped queries, so you should expect performance like that of a grouped query. The main disadvantage of this approach is that it’s most effective when you use it with small groups. With large groups, the query string becomes too long.

Specialized solutions

Solutions for custom aggregations that are based on cursors, pivoting, and CLR UDAs are fairly generic. What I mean by this is that the solution for a string concatenation aggregate looks similar to a product aggregate. But suppose your main priority is efficiency. When aiming at better performance, you usually find yourself using more specialized tools and, as a result, sacrifice the genericness. That’s typically the case in life, not just in T-SQL. While specialized tools can give you better performance for a specific task, they are less reusable for other tasks. In the context of custom aggregations, the techniques you use in your fastest solution for a string concatenation aggregate might not be reusable for a product aggregate. So you gain the performance advantage but lose the genericness.

I’ll provide specialized solutions for aggregate string concatenation, product, and mode.

String concatenation with FOR XML

One of the fastest methods that exists in T-SQL to apply aggregate string concatenation is based on XML capabilities. I’ll first demonstrate the technique for a single group in the Groups table and then apply it to the different groups. Suppose you want to concatenate the strings returned by the following query:

SELECT string
FROM dbo.Groups
WHERE groupid = 'b'
ORDER BY memberid;

This query generates the following output:

String
----------
strb1
strb2
strb3
strb4

Using the FOR XML option, you can request to return the result as a single XML instance. Using the PATH mode with an empty string as input and asking to return the nodes as text nodes, you get basic string concatenation without the extra XML tags:

SELECT string AS [text()]
FROM dbo.Groups
WHERE groupid = 'b'
ORDER BY membered
FOR XML PATH('');

This query generates the following output:

strb1strb2strb3strb4

Normally, the FOR XML option returns entitized strings. This means that some special characters like <, >, and others are converted to alternatives like &lt, &gt, and similar, respectively. If such special characters are possible in your data and you want to return the strings with the original characters, there is a workaround. You add the TYPE directive and then extract the value from the XML instance as a character string using the .value method, like so:

SELECT
(SELECT string AS [text()]
FROM dbo.Groups
WHERE groupid = 'b'
ORDER BY membered
FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)');

To add a separator like a comma between the strings, alter the expression in the SELECT list to ‘,’ + string. You will end up with an extra separator at the beginning of the concatenated string. To remove it, you can use the STUFF function, like so:

SELECT
STUFF((SELECT ',' + string AS [text()]
FROM dbo.Groups
WHERE groupid = 'b'
ORDER BY membered
FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), 1, 1, '');

The STUFF function operates on the string provided as the first input. It removes from the character position specified as the second input as many characters as specified by the third input. It then inserts the string specified by the fourth input into the position specified by the second input (empty string, in our case). With the inputs provided in the preceding query, the STUFF function simply deletes the first character—the extra separator.

This query does the job for a single group. If you want to apply this logic to all groups, you can implement it as a correlated subquery against the Groups table, like so:

SELECT groupid,
STUFF((SELECT ',' + string AS [text()]
FROM dbo.Groups AS G2
WHERE G2.groupid = G1.groupid
ORDER BY memberid
FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), 1, 1, '') AS string
FROM dbo.Groups AS G1
GROUP BY groupid;

Now that you have an efficient specialized technique to concatenate strings, you can employ it to perform dynamic pivoting. Consider the following PIVOT query as an example:

USE TSQLV3;

SELECT custid, [2013],[2014],[2015]
FROM (SELECT custid, YEAR(orderdate) AS orderyear, val
FROM Sales.OrderValues) AS D
PIVOT(SUM(val) FOR orderyear IN([2013],[2014],[2015])) AS P;

You want a solution that doesn’t require you to hard-code the order years so that you won’t need to change the code at the beginning of every year. Using the specialized technique, you construct the separated list of years, like so:

SELECT
STUFF(
(SELECT N',' + QUOTENAME(orderyear) AS [text()]
FROM (SELECT DISTINCT YEAR(orderdate) AS orderyear
FROM Sales.Orders) AS Years
ORDER BY orderyear
FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), 1, 1, '');

The QUOTENAME function is used to convert the input to a Unicode character string and add square brackets to make it an identifier.

Currently, this query generates the output: [2013],[2014],[2015], but when orders from other years are added to the table, the query will include them the next time you run it.

To complete your solution for dynamic pivoting, construct the entire pivot query from the static and dynamic parts, and then execute the query using sp_executesql, like so:

DECLARE
@cols AS NVARCHAR(1000),
@sql AS NVARCHAR(4000);

SET @cols =
STUFF(
(SELECT N',' + QUOTENAME(orderyear) AS [text()]
FROM (SELECT DISTINCT YEAR(orderdate) AS orderyear
FROM Sales.Orders) AS Years
ORDER BY orderyear
FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), 1, 1, '')

SET @sql = N'SELECT custid, ' + @cols + N'
FROM (SELECT custid, YEAR(orderdate) AS orderyear, val
FROM Sales.OrderValues) AS D
PIVOT(SUM(val) FOR orderyear IN(' + @cols + N')) AS P;';

EXEC sys.sp_executesql @stmt = @sql;

String concatenation with assignment SELECT

There’s another specialized technique that is commonly used to perform aggregate string concatenation. It is based on a syntax where you assign a value to a variable as part of a SELECT query. The problem is that people use this syntax with certain assumptions about how it’s supposed to work, but there’s no official documentation that provides such guarantees.

Consider the following code:

SELECT @local_variable = expression FROM SomeTable;

Here’s a quote from the official SQL Server documentation indicating what this assignment SELECT does:

SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

The term last is confusing because there’s no order in a set. And if you add an ORDER BY clause to the query, who’s to say that ordering will be processed before the assignment? In fact, according to logical query processing, the SELECT clause is supposed to be evaluated before the ORDER BY clause. In short, the documentation simply doesn’t provide an explanation as to what it means by last.

In practice, if an ORDER BY clause exists in the query, there are no assurances that ordering will be addressed in the query plan before the assignment takes place. I’ve seen some plans where ordering was addressed before assignment and some after; it’s a pure matter of optimization.

An attempt to perform ordered aggregate string concatenation is a good example how this undefined behavior can get you into trouble. To demonstrate this, I’ll use a table called T1, which you create and populate by running the following code:

USE tempdb;
IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL DROP TABLE dbo.T1;

CREATE TABLE dbo.T1
(
col1 INT NOT NULL IDENTITY,
col2 VARCHAR(100) NOT NULL,
filler BINARY(2000) NULL DEFAULT(0x),
CONSTRAINT PK_T1 PRIMARY KEY(col1)
);

INSERT INTO dbo.T1(col2)
SELECT 'String ' + CAST(n AS VARCHAR(10))
FROM TSQLV3.dbo.GetNums(1, 100) AS Nums;

Here’s an example for a common use of the assignment SELECT syntax with the intention of concatenating the values in col2 based on col1 ordering:

DECLARE @s AS VARCHAR(MAX);
SET @s = '';

SELECT @s = @s + col2 + ';'
FROM dbo.T1
ORDER BY col1;

PRINT @s;

When I ran this code for the first time, I got the plan shown in Figure 4-17.

FIGURE 4-17 Plan for test 1 with assignment SELECT.

Observe that this plan scans the clustered index PK_T1, which is defined with col1 as the key, in order. Then the plan handles the assignments using the Compute Scalar operator. The execution of this plan resulted in the following output, which seems to be aligned with the expectation some have that the values will be concatenated based on the specified order:

String 1;String 2;String 3;String 4;String 5;String 6;String 7;String 8;String 9;String
10;String 11;String 12;String 13;String 14;String 15;String 16;String 17;String 18;String
19;String 20;String 21;String 22;String 23;String 24;String 25;String 26;String 27;String
28;String 29;String 30;String 31;String 32;String 33;String 34;String 35;String 36;String
37;String 38;String 39;String 40;String 41;String 42;String 43;String 44;String 45;String
46;String 47;String 48;String 49;String 50;String 51;String 52;String 53;String 54;String
55;String 56;String 57;String 58;String 59;String 60;String 61;String 62;String 63;String
64;String 65;String 66;String 67;String 68;String 69;String 70;String 71;String 72;String
73;String 74;String 75;String 76;String 77;String 78;String 79;String 80;String 81;String
82;String 83;String 84;String 85;String 86;String 87;String 88;String 89;String 90;String
91;String 92;String 93;String 94;String 95;String 96;String 97;String 98;String 99;String 100;

But as mentioned, there’s no documentation that provides you with such a guarantee. It just so happens that the optimizer’s choice resulted in such ordered strings.

Next, I added the following nonclustered index to the table and then ran the same assignment SELECT code:

CREATE NONCLUSTERED INDEX idx_nc_col2_i_col1 ON dbo.T1(col2, col1);
GO

DECLARE @s AS VARCHAR(MAX);
SET @s = '';

SELECT @s = @s + col2 + ';'
FROM dbo.T1
ORDER BY col1;

PRINT @s;

The plan I got for this execution of the query is shown in Figure 4-18.

FIGURE 4-18 Plan for test 2 with assignment SELECT.

Observe that this time the optimizer chose to use the new index. It’s narrower than the clustered index because it contains only the interesting columns. But it doesn’t sort the rows like the query needs. So the plan performs an unordered scan of the index, and it adds a Sort operator as the last thing it does. The ironic thing is that the plan sorts the rows after the assignments already took place! Not only that, you don’t get the concatenated values in the order you need; rather, you get an intermediate state of the concatenation. When I ran this code, I got the following output:

String 100;

It’s sad to see how much production code exists out there that relies on this method, with the authors of it not realizing it’s not guaranteed to work the way they expect it to. And then something causes the optimizer to change how it optimizes the code, like a new index, a new service pack, and so on, and they get into trouble. You can save yourself a lot of grief if you stick to supported and well-defined techniques like the one based on the FOR XML PATH option.

Aggregate product

Computing an aggregate product is another classic type of custom aggregate calculation. It is commonly used in financial applications—for example, to compute compound interest rates.

With our Groups table, suppose you need to compute the product of the values in the val column per group. There’s an efficient solution for this task that is based on mathematics—specifically, logarithmic calculations. There are two sets of equations that are interesting for the solution:

logb(y) = x ⇔ y = bx

logb(xy) = logb(x) + logb(y) ⇔ xy = blogb(x) + logb(y)

The LOG function in T-SQL computes the logarithm of the first input using the second input as the base. If you don’t indicate the base, the function computes the natural logarithm of the first input (using e as the base, where e is an irrational constant approximately equal to 2.718281828).

The EXP function in T-SQL returns the exponent of the input value. That is, it returns e raised to the power of the input. So, EXP(10) is e¹⁰. This means that in the following pair of equations, one implies the other:

LOG(y) = x ⇔ EXP(x) = y

And therefore in the following pair of equations, one implies the other:

LOG(xy) = LOG(x) + LOG(y) ⇔ EXP(LOG(x) + LOG(y)) = xy

Expressed in T-SQL, here’s the query that computes the desired product:

SELECT groupid, ROUND(EXP(SUM(LOG(val))), 0) AS product
FROM dbo.Groups
GROUP BY groupid;

The rounding is required to correct errors caused by the imprecise nature of the floating-point calculations used by the LOG and EXP T-SQL functions. This query generates the following desired output:

groupid product
---------- ----------------------
a 42
b 693
c 960

If you want to also support zeros and negative values, things get trickier because the LOG function doesn’t support those. To see a demonstration of this, repopulate the Groups table by running the following code:

TRUNCATE TABLE dbo.Groups;

INSERT INTO dbo.Groups(groupid, memberid, string, val) VALUES
('a', 3, 'stra1', -6),
('a', 9, 'stra2', 7),
('b', 2, 'strb1', -3),
('b', 4, 'strb2', -7),
('b', 5, 'strb3', 3),
('b', 9, 'strb4', 11),
('c', 3, 'strc1', 8),
('c', 7, 'strc2', 0),
('c', 9, 'strc3', 12);

Try running the query against the new data:

SELECT groupid, ROUND(EXP(SUM(LOG(val))), 0) AS product
FROM dbo.Groups
GROUP BY groupid;

The query fails with the following error:

Msg 3623, Level 16, State 1, Line 1049
An invalid floating point operation occurred.

To deal with zeros and negatives correctly, you need a few things. Using the ISNULL function, you replace val with a NULL if it’s zero to avoid an error. You apply the original product calculation against the absolute values. Using the MIN aggregate and a CASE expression, you compute a flag (call it zero) that is 1 if you have at least one zero and 0 if you have none. Using the COUNT aggregate and a CASE expression, you compute a flag (call it negative) that is –1 if you have an odd number of negative values and 1 otherwise. Here’s the code applying these calculations:

SELECT groupid,
-- Replace 0 with NULL using NULLIF, apply product to absolute values
ROUND(EXP(SUM(LOG(ABS(NULLIF(val, 0))))), 0) AS product,
-- 0 if a 0 exists, 1 if not
MIN(CASE WHEN val = 0 THEN 0 ELSE 1 END) AS zero,
-- -1 if odd, 1 if even
CASE WHEN COUNT(CASE WHEN val < 0 THEN 1 END) % 2 > 0 THEN -1 ELSE 1 END AS negative
FROM dbo.Groups
GROUP BY groupid;

This code generates the following output:

groupid product zero negative
-------- -------- ----- ---------
a 42 1 -1
b 693 1 1
c 96 0 1

Then to get the correct product, you multiply the product of the absolute values by the zero flag and by the negative flag, like so:

SELECT groupid,
ROUND(EXP(SUM(LOG(ABS(NULLIF(val, 0))))), 0)
* MIN(CASE WHEN val = 0 THEN 0 ELSE 1 END)
* CASE WHEN COUNT(CASE WHEN val < 0 THEN 1 END) % 2 > 0 THEN -1 ELSE 1 END AS product
FROM dbo.Groups
GROUP BY groupid;

This query generates the following output:

groupid product
---------- ----------------------
a -42
b 693
c 0

If you like mathematical challenges, try computing the zero and negative flags mathematically without the use of CASE expressions. This can be done like so:

SELECT groupid,
ROUND(EXP(SUM(LOG(ABS(NULLIF(val, 0))))), 0) AS product,
MIN(SIGN(ABS(val))) AS zero,
SUM((1-SIGN(val))/2)%2*-2+1 AS negative
FROM dbo.Groups
GROUP BY groupid;

Putting it all together, you get the following query to compute the final product:

SELECT groupid,
ROUND(EXP(SUM(LOG(ABS(NULLIF(val, 0))))), 0)
* MIN(SIGN(ABS(val)))
* (SUM((1-SIGN(val))/2)%2*-2+1) AS product
FROM dbo.Groups
GROUP BY groupid;

Aggregate mode

Another example of a custom aggregate calculation is computing the mode of the distribution. Mode is the most frequently occurring value in a set. As an example, suppose you need to query the Sales.Orders table in the TSQLV3 database and find for each customer the employee who handled the most orders. That’s the employee ID that appears the most times in the customer’s orders. In case of ties in the count, you’re supposed to return the greater employee ID.

One approach to solving the task involves the following steps:

Write a grouped query computing the count of orders per customer and employee.

In the same grouped query, also compute a row number that is partitioned by the customer ID and ordered by the count descending and employee ID descending.

Define a CTE based on the grouped query, and then in the outer query, filter only the rows with the row number 1.

Here’s the complete solution code:

USE TSQLV3;

WITH C AS
(
SELECT custid, empid, COUNT(*) AS cnt,
ROW_NUMBER() OVER(PARTITION BY custid
ORDER BY COUNT(*) DESC, empid DESC) AS rn
FROM Sales.Orders
GROUP BY custid, empid
)
SELECT custid, empid, cnt
FROM C
WHERE rn = 1;

This query generates the following output:

custid empid cnt
----------- ----------- -----------
1 4 2
2 3 2
3 3 3
4 4 4
5 3 6
...

If instead of breaking the ties you want to return all ties, you need to apply two changes to your solution. You need to use the RANK or DENSE_RANK functions (either one will work) and remove the employee ID from the window order clause. Here’s the revised code:

WITH C AS
(
SELECT custid, empid, COUNT(*) AS cnt,
RANK() OVER(PARTITION BY custid
ORDER BY COUNT(*) DESC) AS rn
FROM Sales.Orders
GROUP BY custid, empid
)
SELECT custid, empid, cnt
FROM C
WHERE rn = 1;

This code generates the following output:

custid empid cnt
----------- ----------- -----------
1 1 2
1 4 2
2 3 2
3 3 3
4 4 4
5 3 6
...

Observe that for customer 1 there are two employees who handled the maximum number of orders.

Going back to the original task that involves a tiebreaker, consider the optimization of the solution. Because the ROW_NUMBER function’s ordering is based in part on the result of the COUNT computation, there will be a sort operation of the entire input set in the query plan even if you create an index on (custid, empid). With a large amount of input, the sort will be expensive. There is a way to avoid the sort with an indexed view (as a challenge, see if you can figure out how), but what if you cannot afford one?

There’s an alternative solution that relies on a carry-along-sort concept based on grouping and string concatenation that can efficiently rely on an index on (custid, empid). The solution involves the following steps:

1. Write a grouped query that computes the count of orders per customer and employee.

2. In the same grouped query, compute a binary string made of the count converted to BINARY(4) concatenated with the employee ID, also converted to BINARY(4). Call the concatenated value binval.

3. Define a table expression based on the grouped query and name it D.

4. Write an outer query against D that groups the rows by customer ID. In that query, the result of the computation MAX(binval) contains the maximum count concatenated with the ID of the mode employee (with the maximum employee ID as the tiebreaker). Apply substring logic to extract the two parts and convert them back to the original types.

Here’s the complete solution code:

SELECT custid,
CAST(SUBSTRING(MAX(binval), 5, 4) AS INT) AS empid,
CAST(SUBSTRING(MAX(binval), 1, 4) AS INT) AS cnt
FROM (SELECT custid,
CAST(COUNT(*) AS BINARY(4)) + CAST(empid AS BINARY(4)) AS binval
FROM Sales.Orders
GROUP BY custid, empid) AS D
GROUP BY custid;

What’s nice about this solution is that both layers of grouping and aggregation can rely on the order of the aforementioned index. To compare the performance of the two solutions, I created a table called OrdersBig with 10,000,000 rows and an index called idx_custid_empid on (custid, empid). Figure 4-19 shows the plans that I got for both solutions against that table. The top plan is for the solution using the ROW_NUMBER function, and the bottom plan is for the solution based on the carry-along-sort concept.

FIGURE 4-19 Plans for solutions to mode.

Notice the expensive Sort operator in the first plan before the calculation of the row numbers, and the absence of one in the second plan. The first solution took 15 seconds to complete in my test machine and the second completed in only 5 seconds.

Before concluding the section about custom aggregations, I want to mention that standard SQL defines a type of function called ordered set function, which at the date of this writing is not yet available in SQL Server. An ordered set function is a set function with ordering relevance that you can use in a grouped query. The standard defines a clause called WITHIN GROUP where you indicate the function’s ordering specification. There are multiple examples for functions that could be implemented as ordered set functions: aggregate string concatenation, percentiles, CLR aggregates, and so on. You can find a feature-enhancement request concerning ordered set functions here: https://connect.microsoft.com/SQLServer/feedback/details/728969.

Grouping sets

T-SQL supports a number of features that are related to the topic of grouping sets and that are useful for data-analysis purposes. Those features are the clauses GROUPING SETS, CUBE, and ROLLUP, as well as the functions GROUPING_ID and GROUPING.

To demonstrate these features, I’ll use a table called Orders in my examples. Run the following code to create the Orders table and populate it with sample data:

SET NOCOUNT ON;
USE tempdb;

IF OBJECT_ID(N'dbo.Orders', N'U') IS NOT NULL DROP TABLE dbo.Orders;

CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
orderdate DATETIME NOT NULL,
empid INT NOT NULL,
custid VARCHAR(5) NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
GO

INSERT INTO dbo.Orders
(orderid, orderdate, empid, custid, qty)
VALUES
(30001, '20120802', 3, 'A', 10),
(10001, '20121224', 1, 'A', 12),
(10005, '20121224', 1, 'B', 20),
(40001, '20130109', 4, 'A', 40),
(10006, '20130118', 1, 'C', 14),
(20001, '20130212', 2, 'B', 12),
(40005, '20140212', 4, 'A', 10),
(20002, '20140216', 2, 'C', 20),
(30003, '20140418', 3, 'B', 15),
(30004, '20120418', 3, 'C', 22),
(30007, '20120907', 3, 'D', 30);

Before describing features and functions, I’ll start by saying that the term grouping set simply describes the set of expressions you group by in a grouped query. For example, if you have a query that groups by custid, empid, YEAR(orderdate), your query defines the grouping set (custid, empid, YEAR(orderdate)). Nothing special so far.

Historically, T-SQL supported defining only one grouping set per query. With the features I’m about to describe, you can define multiple grouping sets in one query. The question is, why would you want to do that? There are a couple of reasons. One reason is that there are reports where you need to present more than one level of aggregation—for example, hierarchical time reports where you need to show daily, monthly, yearly, and grand totals. Another reason is to persist aggregates for different grouping sets in a table for fast retrieval. This is a classic need in data warehouses. There are other technologies that help you speed up analytical queries, like columnstore, but this technology requires the Enterprise edition of SQL Server.

The following sections describe the aforementioned features.

GROUPING SETS subclause

You use the GROUPING SETS clause to define multiple grouping sets in one query. You specify this clause as part of the GROUP BY clause. The GROUPING SETS clause has its own pair of parentheses, and each inner pair of parentheses represents a different grouping set. You separate grouping sets with commas. Without an inner pair of parentheses, each expression is considered a separate grouping set. An empty inner pair of parentheses defines only one group from the entire input set; in other words, you use them to compute grand aggregates.

As an example for using the GROUPING SETS clause, the following query defines four grouping sets:

SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty
FROM dbo.Orders
GROUP BY GROUPING SETS
(
( custid, empid, YEAR(orderdate) ),
( custid, YEAR(orderdate) ),
( empid, YEAR(orderdate) ),
()
);

But then there’s the question of how the result sets for the different grouping sets can be combined if they have different structures. The solution is to use NULLs as placeholders in columns that are not part of the grouping set that the result row is associated with. Here’s the output of the preceding query:

custid empid orderyear qty
------ ----------- ----------- -----------
A 1 2012 12
B 1 2012 20
NULL 1 2012 32
C 1 2013 14
NULL 1 2013 14
B 2 2013 12
NULL 2 2013 12
C 2 2014 20
NULL 2 2014 20
A 3 2012 10
C 3 2012 22
D 3 2012 30
NULL 3 2012 62
B 3 2014 15
NULL 3 2014 15
A 4 2013 40
NULL 4 2013 40
A 4 2014 10
NULL 4 2014 10
NULL NULL NULL 205
A NULL 2012 22
B NULL 2012 20
C NULL 2012 22
D NULL 2012 30
A NULL 2013 40
B NULL 2013 12
C NULL 2013 14
A NULL 2014 10
B NULL 2014 15
C NULL 2014 20

The rows that have values in custid, empid, and orderyear are related to the grouping set ( custid, empid, YEAR(orderdate) ). Similarly, the rows that have values in empid and orderyear and NULLs in custid are related to the grouping set ( empid, YEAR(orderdate) ), and so on.

If you want to get the same result without the GROUPING SETS clause, you need to write four grouped queries and unify their results, like so:

SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty
FROM dbo.Orders
GROUP BY custid, empid, YEAR(orderdate)

UNION ALL

SELECT custid, NULL AS empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty
FROM dbo.Orders
GROUP BY custid, YEAR(orderdate)

UNION ALL

SELECT NULL AS custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty
FROM dbo.Orders
GROUP BY empid, YEAR(orderdate)

UNION ALL

SELECT NULL AS custid, NULL AS empid, NULL AS orderyear, SUM(qty) AS qty
FROM dbo.Orders;

The GROUPING SETS clause gives you a couple of advantages over multiple queries. Obviously, the code is much shorter. In addition, it gets optimized better. With four queries, the data will be scanned four times. With the GROUPING SETS clause, the optimizer analyzes the grouping sets and tries to minimize the number of scans by rolling up aggregates. To see this optimization technique in action, examine the query plan shown in Figure 4-20 for the query with the GROUPING SETS clause.

FIGURE 4-20 Plan for a query with GROUPING SETS.

Observe that the data is scanned only twice. The top branch of the plan, from right to left, starts by scanning the clustered index. It then computes a scalar called Expr1002 representing the result of the computation YEAR(orderdate). Because I did not prepare any indexes to support the grouping and aggregation, the optimizer adds a Sort operator that orders the rows by empid, YEAR(orderdate), custid. You can avoid this sort by creating a supporting index, as I’ll demonstrate shortly. Based on this order, in a rollup fashion, all grouping sets representing leading combinations can be computed. The rolled-up levels are numbered using a zero-based offset from lower to higher:

Level 0: empid, YEAR(orderdate), custid

Level 1: empid, YEAR(orderdate)

Level 2: empid

Level 3: ()

The first aggregate operator to the left of the Sort operator represents the base grouping set (level 0). The second aggregate operator is responsible for the rolling up of the aggregates. If you examine the query, you will notice that the GROUPING SETS clause defines three of the aforementioned grouping sets: the ones with levels 0, 1, and 3, but not 2. So when looking at the properties of the second aggregate operator, the property Rollup Levels will report only the needed levels.

The query defines an additional grouping set ( custid, YEAR(orderdate) ) that cannot be computed based on the order in the first branch of the plan. So a second branch in the plan (the bottom one) scans the data again, sorts it by YEAR(orderdate), custid, and then computes the aggregates for the remaining grouping set. There’s no need for a second aggregate operator because only one grouping set remains; so there’s no rolling up to do.

As mentioned, you can avoid explicit sorting in the plan by preparing supporting indexes. In our case, one of the elements is a result of a computation, so you can add a computed column based on that computation and then make it part of your indexes. You want the indexes to be covering, so don’t forget to include the aggregated column (qty in our case). Here’s the code to add the computed column and the supporting indexes:

ALTER TABLE dbo.Orders ADD orderyear AS YEAR(orderdate);
GO
CREATE INDEX idx_eid_oy_cid_i_qty ON dbo.Orders(empid, orderyear, custid) INCLUDE(qty);
CREATE INDEX idx_oy_cid_i_qty ON dbo.Orders(orderyear, custid) INCLUDE(qty);

Run the query again, and you will get the plan shown in Figure 4-21.

FIGURE 4-21 Plan for a query with GROUPING SETS after adding index.

The supporting indexes are scanned in order, and therefore no sort operation is required this time.

CUBE and ROLLUP clauses

The CUBE and ROLLUP clauses are simply shortcuts for the GROUPING SETS clause. When you need to define a large number of grouping sets (you’re allowed to define up to 4,096), it’s nice if you can avoid actually listing all of them.

Starting with the CUBE clause, suppose you have a set of expressions and you want to define all possible grouping sets from them. In mathematics, this is what’s known as the power set of a set. It’s the set of all subsets that are formed from the original set’s elements. For a set with cardinality N, the power set has cardinality 2^N. For example, for a set with 10 elements you get a power set of 1,024 subsets, including the original set and the empty set. That’s pretty much what CUBE is about. You provide it with a set of expressions as inputs, and you get the equivalent of a GROUPING SETS clause with all grouping sets that can be formed from the input expressions. As a simple example, here’s a query with a CUBE clause with two input expressions:

SELECT custid, empid, SUM(qty) AS qty
FROM dbo.Orders
GROUP BY CUBE(custid, empid);

This query is equivalent to the following query, which uses a GROUPING SETS clause with all possible grouping sets:

SELECT custid, empid, SUM(qty) AS qty
FROM dbo.Orders
GROUP BY GROUPING SETS
(
( custid, empid ),
( custid ),
( empid ),
()
);

With two input elements to the CUBE clause, the power set contains four grouping sets.

As for the ROLLUP clause, it’s also a shortcut, but one that assumes that the inputs form a hierarchy like time, location, and so on. You get only grouping sets that are formed by leading combinations of expressions. Consider the following query as an example:

SELECT
YEAR(orderdate) AS orderyear,
MONTH(orderdate) AS ordermonth,
DAY(orderdate) AS orderday,
SUM(qty) AS qty
FROM dbo.Orders
GROUP BY
ROLLUP(YEAR(orderdate), MONTH(orderdate), DAY(orderdate));

For N elements, there are N + 1 leading combinations (including the empty set). The preceding query has a ROLLUP clause with three inputs, and therefore it is equivalent to the following query with a GROUPING SETS clause defining four grouping sets:

SELECT
YEAR(orderdate) AS orderyear,
MONTH(orderdate) AS ordermonth,
DAY(orderdate) AS orderday,
SUM(qty) AS qty
FROM dbo.Orders
GROUP BY
GROUPING SETS
(
( YEAR(orderdate), MONTH(orderdate), DAY(orderdate) ),
( YEAR(orderdate), MONTH(orderdate) ),
( YEAR(orderdate) ),
()
);

What’s interesting about the ROLLUP clause is how nicely the optimization of the feature is aligned with its logical design. Recall the rollup concept in the optimization of grouping sets? One ROLLUP clause can be optimized with a single scan based on one order (requiring either an actual sort or an ordered scan of a supporting index). Figure 4-22 has the plan for the preceding ROLLUP query.

FIGURE 4-22 Plan for a query with ROLLUP.