CODEWITHSUNDEEP
sql-server-2008partitioning
PseudoToad
PseudoToad

Reputation: 1574

SQL Server Partitioning and FileGroups

I have a single very large table (100s of millions of rows) that I want to partition. Is it standard (or good) practice to have the files and file groups laid in in a one to one fashion (e.g. FG_2012 would contain only FILE_2012, FG_2011 would only containe FILE_2011, etc.). Most of the examples on the web show it this way but I'm not sure that that isn't for simplicity of explantion more than for good advice.

Upvotes: 4

Views: 6581

Answers (1)

usr
usr

Reputation: 171178

You answer in the comments that you intend to put old partitions onto cheap storage and change its backup scheme. Those are very good reasons for partitioning.

Those reasons force you to use multiple file groups. You are not forced to have one file group per partition, so you could partition by day and use file groups per year. That can be a good idea if you want to have many partitions (like 15.000 which is the maximum). In that case you don't want that many files on disk and it makes sense to not synchronize partitions with file groups.

I think it makes sense to have the two concepts aligned because it simplifies management, though. In your case (storage considerations) I'd go with aligned partitions and file groups. Maybe one of each per month or so.

Upvotes: 3

Related Questions