consider FG_january_2011 file group contains 100000 records. and FG_january_2012 file group contains 2000 records.
update Table_Name set date=dateadd(yyyy,+1,date) where Year(date)=2011
if i execute this query, what will happen.?? my thought is FG_january_2011 file group 100000 records will move to FG_january_2012 file group. and records too incremented as 102000.
Is it right things?? else what???
Best Answer
Removing data from a filegroup never reduces its size. It just frees space to be used for other purposes in the filegroup. In a monthly partitioned table you should rotate the filegroups. You will need 14 partitions for 12 months (you always need an empty partition in the beginning and an empty one in the end) and you do the rotation by switching out the oldest month partition, truncating the switched out table, then switching in a new partition for the next month which reuses the filegroup freed by switching out the oldest month.
If you ever find yourself merging or splitting partitions with data, you're doing it wrong. See How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005.
And, to answer the actual question in the title, an empty filegroup can be 1) shrunk or 2) dropped to reclaim the disk size. Not that I would ever bother with a tiny 200mb size filegroup. But again, you never have to do it if you implement partitioning correctly.