Post reply

Can non partitioned indexes prevent partition splitting/merging?

  • July 7, 2011 at 1:52 pm

    #243718

    I have been warned that creating a non partitioned index on a partitioned table will cause issues with split and merge functions.

    So I created a partition function, scheme and a table with test data.

    I applied the partition to the table by creating a partitioned clustered index on the table and then dropping it.

    I then created a non partitioned non clustered index. I could still do merge and split functions.

    I then tried creating a non partitioned clustered index on the table and it simply removed the partition from the table, which makes sense when you think about it.

    So i would like to know if i am overlooking something here, or if i was given bad information and indexes do not matter when you are merging/splitting a partioned table.

    Thanks.

  • July 7, 2011 at 2:05 pm

    #1350019

    iirc it's switching a partition in/out that a non-partition aligned index breaks, not splitting and merging.

    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 2 posts - 1 through 1 (of 1 total)

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