September 11, 2012 at 12:54 am
Hi All,
I have two filegroup named with primary and secondary filegroup. The secondary filegroup contains two files called Bet_2009.ndf and Bet_2010.ndf.
My question is how to move data into different files based on the year?
September 11, 2012 at 4:28 am
You can't, really. Where the data resides is a function of the engine and the default filegroup.
The best you can do for control purposes is to either create a CLUSTERED INDEX for a table on that filegroup or create a PARTITION SCHEME on the filegroup. The file distribution itself is out of your control, though. EDIT: What I mean by that last statement is I don't believe you can dictate more granular than filegroup.
September 11, 2012 at 4:33 am
If there are two files in a filegroup, SQL spreads the data evenly across the two files, you cannot control which file in a filegroup gets what data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2012 at 4:56 am
Thanks All,
Yes, I tested that it distributed data equally for all the files. My doubt is, whether it is possible to do file level partition.
September 11, 2012 at 4:58 am
It is not possible to do a file level partition at this time.
September 11, 2012 at 5:28 am
As I said, you cannot control what data goes onto what file when there are multiple files in a filegroup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply