IGNORE_DUP_KEY

I have come across an interesting feature when trying to set up index rebuild processes on SQL Server 2008R2. I am constructing an alter index rebuild statement including appropriate IGNORE_DUP_KEY and other settings and am receiving failures because of IGNORE_DUP_KEY.

It would appear that IGNORE_DUP_KEY=ON is not a valid option when rebuilding an index that is a primary key or has a unique constraint and yet I'm finding not problem finding examples where this is exactly what is configured, notably within Microsoft databases; I first picked this up in the DataCollectiondatabase.

I have managed to get around this because what I do is specifically set IGNORE_DUP_KEY=ON in the alter index rebuild statement but I do not specifically set the OFF value if it is not required and that means that when I rebuild the index without specifying whether to ignore the duplicate key or not the system leaves the system as was configured and I do not need to worry about breaking any system functionality.