Data Placement | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Data Placement

I would like to split my data by year into seperate filegroups for a given table without having to have a new table for every year. Is there a way to do this? Thanks for the help
As far I know, no. But wait others members post.
Luis Martin
Moderator All postings are provided “AS IS” with no warranties for accuracy.
Id say it depends on your reasoning for wanting different filegroups? Is it the case that data attached to historic years is merely archive data and is never updated or inserted into?
Modern RDBMS are highly optimized for handling very large amounts of data.
How many rows are we talking about?

The answer to your question is no. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> I think Frank brings up a good point. How much data do you actually have in this table? What problem are you experiencing that causes you to ask this? You can split the indexes onto a different filegroup which might help a little. The answer to this question would also depend on how your disk subsystem is built. Where would you put the other filegroup?<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
I spent about a minute trying to figure out why myself. Performance wise it would be wiser to split the data into partitions (seperate tables) also if you are worried about storage splitting the data into partitions would also solve your problem.
As referred by CHappy if the data is only used for MI purposes which was archived and as well as the application, its better to differentiate the archive data to another database and keep the live database size to minimal for performance sake. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.