Updating SQL partition schemes & partition functions
Posted on 2010-11-16
My client's IT dept had set up SQL Server 2005 filegroups on a particular database for each fiscal quarter: fg2006Q4, fg2007Q1, fg2007Q2, ... up to fg2010Q4. I noticed today that the fg2010Qx files had not been used, still at 1MB each.
Upon further digging, I found the Partition Schemes and Partition Functions folders in Mgmt Studio.
One Scheme: CREATE PARTITION SCHEME [psEvent] AS PARTITION [pfEvent] TO ([fg2006Q4], [fg2007Q1], [fg2007Q2], [fg2007Q3], [fg2007Q4], [fg2008Q1], [fg2008Q2], [fg2008Q3], [fg2008Q4], [fg2009Q1], [fg2009Q2], [fg2009Q3], [fg2009Q4])
Corresponding Function: CREATE PARTITION FUNCTION [pfEvent](int) AS RANGE RIGHT FOR VALUES (1000300, 5493523, 25712235, 45243827, 67741212, 88804361, 106935897, 121343754, 122508529, 122512053, 139523161, 154878083)
Q1: I'd like to create filegroups for fg2011Qx... then can I simply ALTER PARTITION SCHEME with fg2010Q1 through fg2011Q4 added to the list?
Q2: I believe I understand the function ranges, but wondering where/how these ID's would have been generated (likely)? And more fundamentally, how would SQL know when to move onto the next filegroup (assuming it had existed when needed)?
Q3: Is there a safe & easy way to "re-balance" the filegroups... so that fg2010Qx's are used, and the fg2009Q4's aren't so big. I suppose not a huge deal if not, but it'd look cleaner if so.