Friday, September 23, 2005

Partitioning in SQL Server 2005

Sql Server 2005 Partitioning

"A very interesting an powerful feature of Sql Server 2005 is called Partitioning. In a few word this means that you can horizontally partition the data in your table, thus deciding in which filegroup each rows must be placed.

This allows you to operate on a partition even with performance critical operation, such as reindexing, without affecting the others. In addition, during restore, as soon a partition is available, all the data in that partition are available for quering, even if the restore is not yet fully completed."


While not nearly as glitzy as some of the other SQL2K5 features, the new partitioning is pretty nifty.

Think about the ability to move an entire set of data (say day/week/month/quarter/yearly data for example) out of a primary/active/transactional table and into an archive table with a couple lines of TSQL. All without taking either table off line (and NOT having to write/execute UPDATE/DELETE DDL).

Anyone who has had to deal with archiving of "old" data will be pretty excited by the new partitioning...

No comments: