I tried to reproduce the problem using the following query, which does pretty much the same thing (warning: the query will not make any logical sense). However, I could not reproduce it. I'm still able to reproduce the problem on our development server, using the original query.

An interesting observation is the original query worked fine when I specified MAXDOP as 1 in the final select.

;WITH cte

AS

(

SELECT

o.name [tablename],

SUM(ac.max_length) [ColLength],

ROW_NUMBER() OVER (PARTITION BY o.name ORDER BY SUM(ac.max_length)) AS cnt

FROM

sys.objects o

INNER JOIN sys.COLUMNS c

ON c.OBJECT_ID = o.object_id

INNER JOIN sys.all_columns ac

ON ac.NAME = c.NAME AND ac.OBJECT_ID = c.OBJECT_ID

GROUP BY

o.name,

ac.max_length

)

SELECT

tablename, SUM(ColLength) * -1 [ColLength]

FROM

(

SELECT tablename, Collength * 2 [ColLength] FROM cte WHERE cnt = 1

UNION ALL

SELECT tablename, ColLength * 4 [ColLength] FROM cte WHERE cnt = 2

) t

GROUP BY tablename