SQL Server Advent Calendar 7 – Partitions & Indexed Views

  • Article

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.

Partition-Aligned Index Views Switching

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.

Technorati Tags: SQL Server 2008,partitions,indexed views,performance