General Partitioning in SQL Server 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

General Partitioning in SQL Server 2005

Hi,
A new feature is established in SQL Server 2005. That is Partitioned Table. I went through several articles in net regarding partioned table. But I did not get any generalized version of Partitioning.
For example I partitioned a table into 4 Filegroups based on Transaction Date. So I used range function to give ranges for 4 quarters of one year. It works fine. But I would like to generalize this scenario. It will work for one year. If new year comes ,I have to write new script and execute the same.
The Partitioned function should work for 12 months in a given year. So here year is the paramter and then that function should be construct based on the year automatically.
Can anybody give me answer asap. Thanks in Advance
Regards,
Ananth Chirravuri
I am new to this version, but I can give a great link: http://www.sqlskills.com/resources/whitepapers/partitioning in sql server 2005 beta ii.htm Cheers!
Michael B
Sr. DBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7
I think dynamic SQL comes handy here. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Why not create your filegroups ahead of time? Sure, you go and create next 2008’s filegroup now, but it’ll be empty until then. Also, if you are partitioning tables at this time then you should easily be able to calculate your number of insertions per year. Perhaps you may want wider filegroups and seeking through more than 1 year of data might be acceptable. Also to automate things, you could create a SQL job that executes monthly on the first day of the month. In the job, you could check to see if it is the first of January. If it is the first of January then you could perhaps execute a stored procedure to alter your function for that year.
quote:Originally posted by satya I think dynamic SQL comes handy here. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Yup, you’ll need to cruft up some dynamic sql to manage your partitions as the server will not do it automatically for you. Maybe in a service pack or next edition management of partitions will be built in…
quote:Originally posted by KrelianXgs Why not create your filegroups ahead of time? Sure, you go and create next 2008’s filegroup now, but it’ll be empty until then. Also, if you are partitioning tables at this time then you should easily be able to calculate your number of insertions per year. Perhaps you may want wider filegroups and seeking through more than 1 year of data might be acceptable. Also to automate things, you could create a SQL job that executes monthly on the first day of the month. In the job, you could check to see if it is the first of January. If it is the first of January then you could perhaps execute a stored procedure to alter your function for that year.
To move a partition from one partitioned table to another both tables have to be in the same filegroup. The data, partion, is not moved. Only the meta data for the two tables changes. That’s what’s so nice about the feature, the swap is almost instant. An sp that uses the current date to modify the partition function and scheme, run monthly, is a good idea.
Hank
Take a look the following two articles…
SQL Server 2005 Books Online
Designing Partitions to Manage Subsets of Data
http://msdn2.microsoft.com/en-us/library/ms191174.aspx http://msdn2.microsoft.com/en-us/library/ms345146.aspx
http://msdn2.microsoft.com/en-us/library/ms345146.aspx
Mohammed U.
]]>