• ramana3327 (8/27/2014)


    Mainly we need this for archiving purpose.

    Again, why?

    You can't partition across databases, so the 'old stuff into the archive database' can't be done with partitioning. Fast switching works when the table and the partition are in the same filegroup, which means you can't use just fast switching to move data to a slower filegroup. You'll need to add in extra steps. Partitioning won't automatically move old data to a different filegroup. If you try merging two partitions that both have data in them, you've looking at long running operations (the rows have to be moved) with nasty locks, especially if those two partitions are not in the same filegroup.

    It doesn't look like the solution solves the problem. Not without creating a whole pile more problems.

    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