Questions on database table and index partitioning in SQL Server

Question

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?

Answer 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