Home / server / Questions / 52955
Accepted
Cade Roux
Asked: 2009-08-12 13:22:20 +0800 CST

Easy Table Partitioning in SQL Server 2005 for Fact Tables?

  • 772

I've been reading the article at http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx in preparation for implementing some partitioning.

I have several tables I want to partition on a DATA_DT_ID integer, and I'd really like to be able to handle this very simply - add new partitions on the end as I publish a new month of data, remove partitions from the back end as they are expired.

But the article shows a relatively complex series of steps required to do this.

Has anyone created any stored procedures to automate this? This would seem to be a fairly common and general problem in data warehousing.

sql-server sql-server-2005 table-partitioning data-warehouse

2 Answers

  1. Best Answer

    Table partitioning is very database specific as the names of the file groups and paths for the new files being added to the file groups are all specific to the individual database.

    You should be able to fairly easily configure a stored procedure with the needed commands to handle the moving of data through the partitions.

    • 1

  2. I have, sort of. Partitioning on SQL Server 2005 is somewhat fiddly and probably not worth the trouble unless you have enough data to get a real performance win. Rather than creating partitions on the fly you should just create partitions until some period in the future. The don't cost anything until you put data into them.

    Removing partitions is a bit fiddlier - you will have to make a table with the correct schema and a check constraint on your partition key then do an alter table swap partition. Getting the partition ID can be a bit fiddly but you can do it from the data dictionary. I also did it once by making a 'bellwether' table with one row for each partition on the same partition scheme and selecting $partition.{partition function} from that table where the key is the appropriate value.

    The table structure of your scratch table has to line up with the main table, including indexes. Then you swap the partition out and drop the table.

    I think the paint is still a bit wet on partitioning for SQL Server 2005. There are several missing features, for example you can't easily build indexes on a specific partition; the only way to avoid building and rebuilding indexes for the whole fact table is to do a similar trick with swapping the data in and out of the scratch table. SQL Server 2008 also gets the facility to move tables between filegroups without doing the clustered index trick.

    However, I'm not aware of anything downloadable to automate this. Everything I've done for this id DIY, and yes it is a pain in the arse.

    • 1