TSQL - Improved Parallel DML between 2008 and 2014 breaking code

Parallel DML have been improved between the versions of SQL Server 2008 and SQL Server 2014 - which have broken some code.

This particular code is borderline broken even in SQL Server 2008. I would not trust it.

The required number of rows in #t to break this depends on your server. It have to be big enough to cause a parallel plan.

This code would run in SQL Server 2008 and produce unique IDs for all rows in the table.

In SQL Server 2014 the update statement is run in parallel and causes duplicates IDs to appear.

You can cause a parallel plan in SQL Server 2008 by adding the filter (where bloat <> '') on the update statement. However, the part that calculates the value of ID is done in serial and actually produces the correct result, for me, this time.

Don't do this at home kids.

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;CREATE TABLE #t (id INT, bloat VARCHAR(500));INSERT #t ( bloat )SELECT TOP 2000000 'bloat n stuff'FROM master..spt_values, master..spt_values b; DECLARE @id INT = 0;UPDATE t SET @id = id = @id + 1FROM #t t; --WHERE bloat <> ''SELECT COUNT(*), COUNT(distinct id) FROM #t;

Plan in SQL Server 2014.

It is using a parallel plan and computing the ID (Compute Scalar) as part of the parallel part of the plan.