Chris Johnson has a how-to guide:
That all looks ok, and everything published fine, and kept on publishing fine until I happened to check the generated script for some of the publishes (I was in the early stages of development at this point so deployments were just being done through Visual Studio direct to my machine).
When I did I noticed that every deployment I was getting code like this:
That’s dropping all my constraints, recreating them using
WITH NOCHECK, and then usingWITH CHECKto check the existing data. Obviously all the data will pass those checks, but that’s going to add some time to my deployments, and as the size of the data and the number of checks both increase, these checks will take more and more time.
Read on to understand what’s happening. I’d call this a fairly silly limitation on the part of SSDT.