In Sydney this week for the Yukon (SQL Server 2005) Ascend training. Covering a lot of the scalability and high-availability aspects of the product. I continue to be impressed by it.
In the material on partitioning tables, it says you can’t use more than a single column in the partition function. The idea is that you could partition a sales table on say, the region column and potentially store the data from each partition on a separate filegroup.
However, we noticed today that you can use a calculated column as the input to your partition function, as long as you make it a “persisted” calculated column. Persisting a calculated column is a new feature in Yukon too. So, this means that if you want to partition a table based upon more than one column, you could just create a calculated persisted column based upon those other columns and then use that new column in the partition function.
I was also talking to Chris Hewitt today and we kicked around the concept of creating a CLR-based user defined data type that compresses the input. I’m going to try to build one as a new example of a CLR-based type.