Given the following SQL code against the AdventureWorks Database:

begin transaction;

drop trigger Sales.iduSalesOrderDetail;
alter table Sales.SalesOrderDetail
    add LineNumber int null;

with sod as (
            /* other cols */
        NewLineNumber = row_number() over 
            (partition by SalesOrderID order by SalesOrderDetailID) + 1
    from Sales.SalesOrderDetail
    where SalesOrderID <= 44000)
update sod
set LineNumber = NewLineNumber,
    ModifiedDate = getdate()
    /* other cols */

    /* other cols */
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.

  • I presume something to do with the fix here connect.microsoft.com/SQLServer/feedback/details/682645/… Commented Feb 13, 2013 at 12:51
  • Looks like someone has now raised this issue here connect.microsoft.com/SQLServer/feedback/details/779077/… Commented Feb 13, 2013 at 18:08
  • Yes - I raised the issue at MS connect after hearing from Aaron below and from other people at MS TechNet. Commented Feb 13, 2013 at 21:22
  • As for the other ticket, I believe that it is related. I would presume that when they fixed the problem listed in that ticket, they created the problem I'm running into. I agree that the problem given by the original ticket is a valid bug and should be fixed. However, I think they may have been a little overzealous in preventing it, as I see no reason why this one should not work, especially seeing as it works fine with the +1 in the set clause. Commented Feb 13, 2013 at 21:24

1 Answer 1


This is a bug in SQL Server 2012, and will be fixed in SP2:


A workaround is to remove the + 1 from the ROW_NUMBER() calculation; this spurious error message goes away. Rewrite this portion:

  NewLineNumber = row_number() over 
    (partition by SalesOrderID order by SalesOrderDetailID)
    ---------------- remove the +1 here --------------------^^^
  from Sales.SalesOrderDetail
  where SalesOrderID <= 44000)
update sod 
  set LineNumber = NewLineNumber + 1,
  ------- and put it here -------^^^
  • Hmm... very interesting. All the more reason to think this might be a bug in the query engine. I've put in a note at the tech forums to see if maybe the MS people know anything more. This is probably enough for me to jerry-rig a workaround for my project though. Thanks! Commented Feb 12, 2013 at 21:31
  • @Stuart I have some contacts that I'm pinging as well. I don't think this is a bug per se, I just think the error message is slightly misleading (it shouldn't talk about deleted). But I would like to get an explanation about why the workaround works simply by moving the location of the addition. Commented Feb 12, 2013 at 21:40
  • @Stuart it is in fact a bug, and will be fixed in 2012 SP2 (ETA unknown): connect.microsoft.com/SQLServer/feedback/details/779077/… The workaround is trivial so it is unlikely pestering PSS will get you a hot fix any faster. Commented Feb 18, 2013 at 18:14
  • Thanks Aaron. This is the feedback I posted, and I was grateful to hear from them that they had already fixed it. Yes, the workaround is easy, but will require a bit of ugly code in my project. Right or wrong, I am building a SQL string in my project on the fly (though with parameters for user provided data; thankfully I'm not a moron). The original pattern is easy to abstract as "provide the definition of sod and we'll update from there". The new pattern requires I provide the update statement as well. Commented Feb 18, 2013 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.