Table Partition

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Table Partition

Table Partition

Rate Topic

Display Mode

Topic Options

Author

Message

k.thanigaivel

Posted Tuesday, September 11, 2012 12:54 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 05, 2013 12:01 AM
Points: 45, Visits: 851

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

Brandie Tarvin

Posted Tuesday, September 11, 2012 4:28 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 6,655, Visits: 5,678

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, MCDBA, MCSA

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

GilaMonster

Posted Tuesday, September 11, 2012 4:33 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

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

k.thanigaivel

Posted Tuesday, September 11, 2012 4:56 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 05, 2013 12:01 AM
Points: 45, Visits: 851

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

Brandie Tarvin

Posted Tuesday, September 11, 2012 4:58 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 6,655, Visits: 5,678

It is not possible to do a file level partition at this time.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

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

GilaMonster

Posted Tuesday, September 11, 2012 5:28 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

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 »

Permissions