I have a confusion regarding update locks in combination with CTE's (or subqueries) as in the following simple example -
In the above example, which of the following 2 options is SQL Server's locking behavior?
It's going to do both. CTE is nothing but a fancy way to trick out a query. If you're curious what might be locked, take a look at the locks as @Fatherjack suggests. You can also look at the execution plan to see how SQL Server is going to access the table. But in this case, it seems pretty clear, it's #2.
One other point, the semi-colon is a statement terminator. Putting it front of the WITH statement is a crutch, mainly used by people posting code online so that those who are not already using the statement terminator don't get errors. Instead, just use the semi-colon as a statement terminator.
answered Oct 29, 2012 at 12:54 PM
Grant Fritchey ♦♦