Table Partition

  • 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?

  • 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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

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

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply