locked
Partition Existing table with data RRS feed

  • Question

  • Hi,

    I have around 400 tables in a database which are not partitioned. We need to partition the data for current and historic data. Now to implement partition on tables with existing data, I have read a few articles, which says that we need to create a Clustered index on table on the partioned column for the partition schema.

    I tried doing this and it worked, Next I tried doig it by adding a NonClustered index on the partioned column to the table and not clustered index and still it shows the data in different file groups as per the Partition function.

    I am not sure, if this is correct? If it is correct how is the data now in the filegroups, is it based on the the partion function first and then the ordering is done as per the Clustered index?

    I am not very keen to change the existing Clustered index to non clustered index, and createing a clustered index for the Partitoining. Let me know the options.


    My Blog    |      Ask Me     |      SSIS Basics     

    Monday, February 25, 2013 1:36 PM

Answers

  • There's a lot of confusion on this point, but it's actually quite simple:  A unique index can only be partitioned by an index key column.  Any other index or row heap can be partitioned by any table column.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Sudeep Raj Monday, February 25, 2013 3:07 PM
    Monday, February 25, 2013 2:00 PM

All replies

  • when you create the partition function you provide the 'limits' for the function. 

    then when you do the partition schema, you base it on the function and assign it the filegroups you want to use.

    this is useful because ig you know the function you can query a specific partition. sql server will handle the files and filegroups associated with it.

    other than that, the great advantages mainly relate to maintaining tables that are partitioned. you have options to switch partitions, rebuild indexes per partition, etc.

    Monday, February 25, 2013 1:47 PM
  • Thanks Rui,

    I am aware of how partitioning works, My question is how will the scenario be handled in my case?


    My Blog    |      Ask Me     |      SSIS Basics     

    Monday, February 25, 2013 1:56 PM
  • There's a lot of confusion on this point, but it's actually quite simple:  A unique index can only be partitioned by an index key column.  Any other index or row heap can be partitioned by any table column.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Sudeep Raj Monday, February 25, 2013 3:07 PM
    Monday, February 25, 2013 2:00 PM
  • Thanks David,

    Could you please explain may be with an example, as you said its lot confusing :)

    In my scenario, we have tables with existing clustered indexes(could be unique or non unique) We have another column in all the tables on which we apply partition.

    What is the best way to partition the tables with data, keeping current indexes intact?


    My Blog    |      Ask Me     |      SSIS Basics     

    Monday, February 25, 2013 2:09 PM