Given the following SQL code against the AdventureWorks Database:
begin transaction; drop trigger Sales.iduSalesOrderDetail; alter table Sales.SalesOrderDetail add LineNumber int null; go with sod as ( select SalesOrderID, /* other cols */ LineNumber, NewLineNumber = row_number() over (partition by SalesOrderID order by SalesOrderDetailID) + 1 from Sales.SalesOrderDetail where SalesOrderID <= 44000) update sod set LineNumber = NewLineNumber, ModifiedDate = getdate() output inserted.SalesOrderID, /* other cols */ inserted.LineNumber; select SalesOrderID, /* other cols */ LineNumber from Sales.SalesOrderDetail where SalesOrderID <= 44000; rollback transaction;
When running this code on SQL Server 2008R2, it runs fine; that is to say, it updates the data in
Sales.SalesOrderDetail and outputs the results, which match the data from the following SELECT statement.
However, when running this code on SQL Server 2012, I get the message:
Msg 4196, Level 16, State 1, Line 2
Column 'deleted.NewLineNumber' cannot be referenced in the OUTPUT clause because the column definition contains an expression using a window function.
This is odd because I am not referencing deleted.NewLineNumber in any way. I have attempted removing various OUTPUT columns and get the same result. The only two ways I have found to stop the error have been to
- make the NewLineNumber column be exactly the result of the
row_number()(that is to say:
NewLineNumber = row_number() over (...)); or
- remove the OUTPUT clause altogether.
Is this possibly a bug in how SQL Server is processing the expressions in the CTE? Has anyone else run into this?
I'm working on possible workarounds, but I am curious about this.