Last week, I had the opportunity to talk to the New England SQL Server Users Group . I would like to thank the group for inviting me, Adam Machanic for organizing the event, and Red Gate for sponsoring it. My talk was an introduction to query processing, query execution, and query plans in SQL Server...

In my previous post , I discussed the ROW_NUMBER ranking function which was introduced in SQL Server 2005. In this post, I'll take a look at the other ranking functions - RANK, DENSE_RANK, and NTILE. Let's begin with RANK and DENSE_RANK. These functions are similar - both in functionality and implementation...

In some of my past posts, I've discussed how SQL Server implements aggregation including the stream aggregate and hash aggregate operators. I also used hash aggregation as an initial example in my introductory post on parallel query execution . In this post, I'll look at a partial aggregation. Partial...

In my last two posts, I gave examples of aggregation WITH ROLLUP and CUBE . SQL Server 2008 continues to support this syntax, but also introduces new more powerful ANSI SQL 2006 compliant syntax. In this post, I'll give an overview of the changes. First, let's see how we rewrite simple WITH ROLLUP...

In my last post, I wrote about how aggregation WITH ROLLUP works. In this post, I will discuss how aggregation WITH CUBE works. Like the WITH ROLLUP clause, the WITH CUBE clause permits us to compute multiple "levels" of aggregation in a single statement. To understand the difference between these two...

In this post, I'm going to discuss how aggregation WITH ROLLUP works. The WITH ROLLUP clause permits us to execute multiple "levels" of aggregation in a single statement. For example, suppose we have the following fictitious sales data. (This is the same data that I used for my series of posts on the...

In my last post , I gave an overview of the PIVOT operator. In this post, I'm going to take a look at the query plans generated by the PIVOT operator. As we'll see, SQL Server generates a surprisingly simple query plan that is essentially just a fancy aggregation query plan. Let's use the same schema...

In my next few posts, I'm going to look at how SQL Server implements the PIVOT and UNPIVOT operators. Let's begin with the PIVOT operator. The PIVOT operator takes a normalized table and transforms it into a new table where the columns of the new table are derived from the values in the original table...

In my prior two posts, I wrote about the stream aggregate operator. Stream aggregate is great for scalar aggregates and for aggregations where we have an index to provide a sort order on the group by column(s) or where we need to sort anyhow (e.g., due to an order by clause). The other aggregation...

There are two physical operators that SQL Server uses to compute general purpose aggregates where we have a GROUP BY clause. One of these operators is stream aggregate which as we saw last week is used for scalar aggregates . The other operator is hash aggregate. In this post, I’ll take a closer look...

Aggregation refers to the collapse of a larger set of rows into a smaller set of rows. Typical aggregate functions are COUNT, MIN, MAX, SUM, and AVG. SQL Server also supports other aggregates such as STDEV and VAR. I’m going to break this topic down into multiple posts. In this post, I’m going to...