Sql-server – Temporarily disable checking of constraints

NOT FOR REPLICATION was indended for this scenario:

1. You have a Order table with a FK to Person table.
2. Order table is in publication no.1 and Person table is in publication no.2.
3. The Order pub and Person pub will happen at different times. Therefore if some new records were in the Order table were replicated down to the subscriber and the Person table was missing the person that new Order was for, you don't want the replication insert to fail at the subscriber. Instead, it can be trusted that the new Person record will come down at some point because the FK was used at the Publisher.
4. At the subscriber, if you insert an Order record, the FK will check that the Person record exists.

I would always stick with this policy and use NOT FOR REPLICATION only for that purpose of FK's relating to tables in different publications, and not for the purpose of query plan optimisation.

Performance Optimisation. By removing either the PK or an Index, there will be less pages needed for my indexes = faster writes, plus also maintenance/operational benefits, i.e. one less index to keep defragged etc.

You need to be able to prove that what's proposed will actually help (cost vs. benefit). For what reason is this change being considered? Are there actually performance issues with this table, or did it just "look wrong"?

Here are some other questions that will help you come to the best decision for your environment:

• How much time would be saved in the maintenance window? In the backup window?

• How much storage space would this save (data files, log files, backups, etc.)?

• Is INSERT performance on this table really a bottleneck right now? How much would it improve? Is removing an index the best strategy to fix that problem?

• Will this cause problems with database tools and frameworks (ORMs particularly) that expect each table to have a primary key and not just a unique index? Transactional Replication requires a primary key on published tables.

• Is a self-documenting database schema important?

• Despite its limited use, is the narrowness of the primary key index still allowing the optimizer to produce more efficient plans for certain queries? (Use sys.dm_db_index_usage_stats to find out.)

Personally speaking, from what you've told us, I would leave it alone until it can be proven that both (a) the extra index is a problem, and (b) removing it is the solution.