We have Cumulative Update #10 installed on SQL Server 2012 SP1 that we need because of a specific fix to DQS.
Since the recently released SQL Server 2012 SP2 only contains fixes from CU1 through CU #9, if we decide to update to SP2, will that overwrite and remove the fixes from CU #10? And if so, could we re-apply CU#10 to it? Or would we have to wait until another CU is made for SQL Server 2012 SP2?
Best Answer
Yes, this is how it works: You lose the updates that were made in any CUs not included in the service pack (sometimes it is more than one CU that is "lost" temporarily).
And no, you can't then apply a Service Pack 1 Cumulative Update to Service Pack 2: the installer simply halts and won't let you do it.
The reason it works this way is that the Service Pack code goes through a longer and more rigorous testing cycle, so needs to be "locked down" months before release, unlike Cumulative Updates which go through less rigorous testing in their - typically - 8-week cycle.
While the SP is being tested, issues continue to be discovered, fixes continue to be checked in, and Cumulative Updates continue to be released. The world doesn't stop because a Service Pack is being tested.
In no situation that I have seen has an SP cycle been halted so that it can include a fix for some issue that has been discovered in the meantime - depending on the nature and severity of the issue, they may "fast-track" a post-SP CU, or they may issue a separate on-demand hotfix. I've seen both of these scenarios happen (more below on the latter). Typically, though, the first CU after the SP was released includes all of the fixes from the last CU or two from the previous branch, kind of a "catch-up."
So, please, wait for Service Pack 2 Cumulative Update 1 (should be end of July, according to this post) before installing Service Pack 2, or be willing to lose the fixes in CU #10.
(And in either case, if you are on Enterprise and run your rebuilds online, please see this blog post about an IMHO critical hotfix addressing a data loss/corruption issue.)