I have been recently assigned to administer a database in SQL Server 2016 and I have discovered that there are many tables in the database with Non-Unique Clustered indexes resulting in Non-Clustered Primary Key indexes.
I know that the above is apparently allowed but is not ideal (specially for large tables) and maybe for some few cases, that would make sense, but the number of such tables tells me that this was more like a result of careless table definitions.
For example, a table named
Transaction will have a Clustered Index with the following keys:
is_deleted. Also, the table will have a Non-Clustered Primary key on a column named
Now, some of these tables are pretty large and have many foreign key references on them, so I came up with the below steps to change all these tables with the help of Aaron's answer in this thread Unable to drop non-PK index because it is referenced in a foreign key constraint:
- Drop the Clustered Index of the table
- Drop all foreign key constrains that reference the table
- Drop current Primary Key constraint of the table
- Create Primary Key Clustered constraint using the
- Create all foreign key constraints that were previously deleted
- Optionally - Create the previously Clustered index as a Non-Clustered index
I know that for the large tables I need to find a maintenance window to perform the change but do you find any problems or gotchas with the above solution what I didn't think of? Will this method cause fragmentation on the rest of Non-Clustered Indexes?
There are a lot of tables involved and I want to make sure that I'm not triggering any side effects.