How does the "Set primary key and unique constraints Novalidate" option work? When to use "Before the switch" or "Inside the switch" options?
Before the switch
This is the original scripting method. The original table's constraints are altered to no validate prior to starting the switching process.
This requires exclusive access to the original table at least one time per constraint that needs to be altered to no validate. Although the number of times exclusive access to the original table is increased, the actual switching process is faster.
Inside the switch
This is a new scripting method. The original table's constraints are altered to no validate as a part of the switching process.
This minimizes the number of times exclusive access is required on the original table, by altering constraints during the switching process. Although the number of times exclusive access to the original table is decreased, the switching process is slower.
When to use the new "Inside the switch" method?
When a table is very busy and many row level locks are held on the table, it can be difficult to obtain exclusive table access. Not being able to obtain exclusive access can cause the Live Reorg to prevent access to others, delays and possible failure. And that is why the option is there to put the ALTER inside the switch - the user can control exactly when the switch occurs, and if they are going to reduce activity during the switch that is the best time to execute the ALTER... NOVALIDATE statement also.
So the recommendation is;
- When using automatic switch approval (likely when reorganizing a large number of tables) - leave the ALTER outside the switch. (If an issue attempting to ALTER... NOVALIDATE occurs, restart later when the system is less busy.)
- When using manual or time period based approval (especially when managing activity during the switch) - put the ALTER (Novalidate Constraints) inside the switch .
