需要AdventureWorks样本？codeplex.com ......不是那么快...

我是在线书籍的忠实粉丝。最初由Sybase开发，它是，并且仍然是在线帮助系统的时间之前。不仅提供了SQL Server的语法参考，还提供了代码样本和示例。每对几个月都有新版本，因此Microsoft致力于重大资源，以确保其最新和准确。MSDN版在线始终是最新的。即使是专家们也定期在线使用书籍。大多数帮助系统都没有。我最近教一班，并尝试展示使用组与分组集的新SQL Server 2008功能的示例。在2008年之前，我们必须通过与Union全部的语句代码多个SELECT组来连接结果集。现在，我们可以使用组与分组集进行一次选择。 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 Files\Microsoft SQL Server\MSSQL.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