Trying to use table memory optimization advisor on SQL Server 2016, seems I need to drop and re-create the foreign keys of some disk based tables, the size of the table is 300 GB and I'm wondering if there is any risk to drop and re-create the foreign keys before table memory migration?
Best Answer
Risk is in between you drop and recreate the Foreign key constraint any process (application, user etc etc) will be allowed to insert data in any one of these tables which can break the referential integrity. If that happens you will not be able to recreate the Foreign key constraint (with checking integrity on existing data, which is a good thing) and you will have to clean up the data.
There are ways to tell SQL Server to ignore existing data and enforce the constraint only on new data. Which is BAD or GOOD depends on your business need.
Details here: