Table and Index Partitioning
I am planning to use table partitioning for one of my existing databases. All the tables in the database have a clustered index and a non-unique non-clustered index. The non-unique non-clustered index is built on the column which I would like to use as the partition column. The partition column is not part of the Primary Key or clustered index. I am using SQL Server 2016 SP1.
I came across these points while reading, "Partitioned Table and Index Strategies Using SQL Server 2008"
Are these points still applicable to SQL Server 2016 SP1?
Because, when I used the Create Partition wizard, it did not convert the primary key into a non-clustered index and add a clustered index for the partition column.
In a partitioned table, the partition column must be a part of:
The clustered index key. The primary key. Unique index and uniqueness constraint keys. There are also some important requirements for indexes during a SWITCH operation:
All indexes must be aligned. No foreign keys can reference the partitioned table. All the tabled I want to partition have foreign keys as well. I have to perform SWITCH operation. Are there any workarounds to perform SWITCH while keeping the foreign keys?
Filtered Index I have to purge the database based on one column (partitioning column) and another column (UserId) to filter data. For example: get 7 days worth of data for UserId 1. The database can have data for up to 100 users. There is already non-clustered index created on UserId but the query performance is poor. Please suggest whether creating a secondary non-clustered filtered index on the UserId column would improve query performance.
