Day 6 of my virtual advent calendar, about stuff I like in SQL Server 2008..
Indexed Views allow result sets to be persistent as they are materialized on physical storage which avoids performing costly joins and/or aggregations at execution time. However in SQL Server 2005 they would have to be dropped and recreated if one of the underlying tables was partitioned and a new partition was added.
This is fixed with the cunningly named indexed aligned partition views (that’s a Microsoft mouthful so I will abbreviate that to PAIV). This allows you to swap partitions in and out of your large table to add new data and archive older data.
The key to this is the ALTER TABLE ..SWITCH statement. There’s a really simple example to follow in books on-line on the TechNet SQL Server TechCenter.
PingBack from http://theblogsecrets.com/2008/12/andrew-fryers-blog-sql-server-advent-calendar-7-%e2%80%93-partitions/