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:
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?
How is the partitioning applied in the case of multiple indexes?
Do we need to partition table data and indexes separately?
What are the best practices for partitioning a table in SQL Server?