SQL Server
I am a big fan of Books Online. Originally developed by Sybase, it was, and still is, ahead of its time in terms of online help systems. Not only syntax reference for SQL Server but also code samples and examples are provided throughout. A new version is available every couple months so Microsoft dedicates significant resources to make sure it is up to date and accurate. The MSDN version online is always up to date. Even the experts use Books Online on a regular basis. Not so with most help systems. I was teaching a class recently and trying to show examples of the new SQL Server 2008 feature of using GROUP BY with GROUPING SETS. Before 2008, we had to code multiple SELECT GROUP BY statements with UNION ALL to concatenate the result sets. Now we can do it all in a single SELECT using GROUP BY with GROUPING SETS. Books Online does have an example, but lo and behold it runs against the 2005 version of AdventureWorks. Since I was using SQL Server 2008, I had loaded AdventureWorks2008 which includes significant redesign work and extra normalization. I tried to run the sample code against this database without success. OK, so I needed a copy of the AdventureWorks database pronto. Enter www.codeplex.com. This is now where all the sample code and databases are downloaded for free from Microsoft. Yes, the 2005 version is still available so a quick download of an .MSI file is all that’s needed. When run, the .MSI puts the database in the C:Program FilesMicrosoft SQL ServerMSSQL.1 folder so I had to search for that folder when attaching the database. It attached successfully. Great. Then I could run the sample against the old version of AdventureWorks with success. Awesome. Just remember that some of the samples in BOL need this older version of AdventureWorks. Even 2008 features like Grouping Sets. I then proceeded to test some of my own 2005 scripts against the older database. Scripts that worked in 2005 suddenly did not work any more. What could be the problem? The AdventureWorks database on the codeplex web site has a Case Sensitive collation. This took me back to the Sybase days when the default collation was case sensitive because of UNIX. But it meant if a column is called Item it must be spelt Item and using ITEM will yield an error. Those were the days. OK, so it must be easy to update the collation for a database. ALTER DATABASE COLLATE. Books Online has an example. Running this caused an error due to all the CHECK constraints that are dependent on the collation. Each of these need to be changed before you can update the collation of the database. All I wanted to do was demonstrate a neat new 2008 feature…they don’t make it easy for us, do they? As always, think of it as job security…if it was easy, everyone would be doing it… cheers Brian Using GROUP BY with GROUPING SETS http://msdn.microsoft.com/en-us/library/bb522495.aspx
