0

I have developed Transact-SQL script to partition the table. To apply the partition, I am creating one non-clustered index for my partition key column and associating it to partition scheme. I have few questions:

  1. My table could have multiple indexes, and when we create index, duplicate data is created. When I partition using a non-clustered index on only one column (i.e. Partition Key Column), will other index data be partitioned too?

  2. How is the partitioning applied in the case of multiple indexes?

  3. Do we need to partition table data and indexes separately?

  4. What are the best practices for partitioning a table in SQL Server?

Improve this question
1
  1. When you partition an index all its data will be split according your partitions.
  2. The partitioning is applied to each particular index independently others.
  3. From the previous point - yes, if you need a partitioned table with partitioned indexes you have to apply the partitioning to the table and each of the indexes.
  4. Too broad to be answered. You could start with SQL Server Table Partitioning: Resources
Improve this answer
2
  • So, even if the partitioning is happening based on one index, other index data will too be partitioned. is it correct?
    – Murali Dhar Darshan
    Feb 20 '19 at 11:07
  • If you mean a composit or covering index that is partioned using one of its key columns, then it's correct - each partition will have its own part of data.
    – Denis Rubashkin
    Feb 20 '19 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.