GROUP BY understanding problem

I am going to take the Microsoft MCTS Exam 70-433 – SQL Server 2008 Database Development. So I bought the corresponding book from Microsoft Press for this very exam:

ISBN-13: 978-0735626393
ISBN-13: 978-0735626393

Altough I have a lot of experience with SQL in general and the SQL Server in particular I decided to read Chapter 1 where they explain the SQL basics, to be sure to get it all. When I came to the part where they explained  the GROUP BY clause, I stumbled across the following simple SQL statement (page 20):

SELECT Production.Product.ProductSubcategoryID
     , AVG(ListPrice) AS 'Average'
     , MIN(ListPrice) AS 'Minimum'
     , MAX(ListPrice) AS 'Maximum'
FROM Production.Product
WHERE ListPrice <> 0
GROUP BY Product.ProductSubcategoryID;

The result set:

ProductSubcategoryID | Average  | Minimum | Maximum
NULL                   159.1333   133.34    196.92
1                      1683.365   539.99    3399.99
2                      1597.54    539.99    3578.27

What follows is this explanation of the result set:

The top row, where the ProductSubcategoryID is listed as NULL, is the summary row that provides the average, minimum and maxiumum list prices of products across all subcategories.

Aggregate functions with a simple GROUP BY do return a summary row? Could it be that I missed that all the years? Obviously the first row is simple the group of rows with ProductSubcategoryID of NULL and not a summary row. Or do I miss something here?

I know there is a WITH ROLLUP clause which produces summary rows, but this is explained in the next chapter of the book, so I don’t think it a simple typo.

Btw, this example uses the AdventureWorks2008 example database from Microsoft.

How to benchmark queries on Sql Server

Recently I had to optimize some SQL-queries on Sql Server 2005. Therefore I used  Sql Server Management Studio.
I run into the following two problems:

  1. The resolution of the timer is one second. Thats too inaccurate.
  2. The cache in Sql Server optimized subsequent runs of the same query. So i couldn’t tell if I improved the query or made it worse.

If found the following solution for both problems on Just insert the query you want to tune below the comment and you can run your query always under the same preconditions.

DECLARE @Started datetime, @Ended datetime

— insert query to tune here

SELECT DATEDIFF(ms, @Started, @Ended)