Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table Partition Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 12:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:44 AM
Points: 45, Visits: 852
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?
Post #1357160
Posted Tuesday, September 11, 2012 4:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 7,004, Visits: 6,183
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1357284
Posted Tuesday, September 11, 2012 4:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:21 AM
Points: 41,507, Visits: 34,422
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1357291
Posted Tuesday, September 11, 2012 4:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:44 AM
Points: 45, Visits: 852
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.

Post #1357310
Posted Tuesday, September 11, 2012 4:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 7,004, Visits: 6,183
It is not possible to do a file level partition at this time.

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1357312
Posted Tuesday, September 11, 2012 5:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:21 AM
Points: 41,507, Visits: 34,422
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1357329
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse