1

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.

Alex Angas
  • 2,007
  • 2
  • 26
  • 37
Cade Roux
  • 375
  • 2
  • 5
  • 18

2 Answers2

1

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.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • Do you have to pre-determine the number of partitions when you create the partition scheme before creating the table? – Cade Roux Aug 11 '09 at 23:01
  • You don't have to, but it'll make life easier. If your table is very large, and you change the partition scheme it'll take quite a while to move all the data into it's new location as the data has to be physically moved from one file to another. – mrdenny Aug 14 '09 at 07:28
  • If you make partitions off to several years in the future you won't have much trouble. They partitions don't take up space as such (apart maybe for some header information) until you actually load data into them, so there's no significant cost to pre-building 5 years' worth. – ConcernedOfTunbridgeWells Aug 24 '09 at 11:27
  • The only think to worry about here would be having to preallocate all the file groups. But if you are ok with having the storage sitting there doing nothing you should be fine. – mrdenny Aug 24 '09 at 12:17
1

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.

ConcernedOfTunbridgeWells
  • 8,810
  • 2
  • 31
  • 52