This the Part 2 of my Study notes for 70-461 exam. You can find the Part 1 here

04. Aggregate Functions

1. GROUPING SETS Using GROUPING SETS operator returns the same result set generated by using a GROUP BY, ROLLUP and CUBE operator. For example, this query generates the following result set:

    SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total
    FROM Sales.SalesOrderHeader o
    GROUP BY GROUPING SETS ((), o.TerritoryID, YEAR(OrderDate))
    ORDER BY SaleYear ASC, o.TerritoryID ASC, Total ASC
   

   Output:

   Source: http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

2. RANK / DENSE_RANK / NTILE / ROW_NUMBER These functions may be quite helpful for reporting queries.

   RANK – DENSE_RANK: These functions help to order rows by assigning an incrementing number based on the criteria used in ORDER BY clause. The difference between them is rank skips the next value when there are two of the same one and dense_rank doesn’t.

   Example:

    SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total,
    RANK() OVER(ORDER BY SUM(SubTotal) DESC) AS Rank
    FROM Sales.SalesOrderHeader o
    GROUP BY o.TerritoryID, YEAR(OrderDate)
    ORDER BY Rank, SaleYear ASC, o.TerritoryID ASC, Total ASC
   

   Output:

   

1. NTILE Works like rank but it takes an integer parameter (n) and partitions the results to n-pieces. For example, the query below with NTILE returns 40 rows. As n is 4 in this example, the first 10 rows got rank 1 and 11 – 20 got rank 2 and so forth. Example:

    SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total,
    NTILE(4) OVER(ORDER BY SUM(SubTotal) DESC) AS Rank
    FROM Sales.SalesOrderHeader o
    GROUP BY o.TerritoryID, YEAR(OrderDate)
    ORDER BY Rank, SaleYear ASC, o.TerritoryID ASC, Total ASC
   

   Output:

   

   ROW_NUMBER: Just increments the rank no matter what. Example:

    SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total,
    ROW_NUMBER() OVER(ORDER BY SUM(SubTotal) DESC) AS Rank
    FROM Sales.SalesOrderHeader o
    GROUP BY o.TerritoryID, YEAR(OrderDate)
    ORDER BY Rank, SaleYear ASC, o.TerritoryID ASC, Total ASC
   

   Output:

   

Source: http://msdn.microsoft.com/en-us/library/ms189798.aspx

05. Analytic Functions

LAG / LEAD: When I first saw these I instantly loved them! They prevent an unnecessary costly self-join to access previous and next rows in a result set.

So for example check out the following query and its output:

SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total,
LAG(SUM(SubTotal)) OVER(ORDER BY YEAR(OrderDate)) AS PrevYear,
LEAD(SUM(SubTotal)) OVER(ORDER BY YEAR(OrderDate))  AS NextYear
FROM Sales.SalesOrderHeader o
WHERE o.TerritoryID = 1
GROUP BY o.TerritoryID, YEAR(OrderDate)
ORDER BY SaleYear ASC, o.TerritoryID ASC, Total ASC

Output:

Notice that every row has the previous row’s current value in its PrevYear column. Except the first one obviously as it doesn’t have a previous year. And the same logic applies to the NextYear value with the difference that LEAD function is used to obtain those values.

Source: http://msdn.microsoft.com/en-us/library/hh213234.aspx

06. TRANSACTION TIPS

One important topic to grasp is transaction isolation levels. I had a few questions related to this topic. Also it is crucial to create high-performance queries. The isolation levels you need to pay attention are:

READ UNCOMMITTED: This one is most loose one as it allows dirty reads. But much faster than the others as it doesn’t lock the rows.

READ COMMITTED: It doesn’t allow reading uncommitted data. This is the default level.

REPEATABLE READ: Prevents dirty reads. Concurrent operations cannot modify or delete data.

SERIALIZABLE: This level takes it one step further: In  addition to REPEATABLE READ it prevents concurrent operations from inserting data too.

SNAPSHOT: Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

Source: http://msdn.microsoft.com/en-us/library/ms173763.aspx