In the following
DELETE statement I try to delete all rows except the first one ordered by some criteria. (The actual query makes more sense, this is just a repro. All the
sys.objects stuff is just for generating test data.)
Note the filter
r <> 1. Yet, the
OUTPUT clause outputs deleted rows with
r = 1. How can this be?
USE tempdb SET XACT_ABORT ON BEGIN TRAN SELECT * INTO #o FROM sys.objects SELECT TOP 2 name FROM #o ORDER BY object_id --debug output DELETE k OUTPUT Deleted.name, Deleted.r FROM ( SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r FROM #o k ) k WHERE r <> 1 --OUTPUT returns rows with (r = 1) SELECT TOP 2 name FROM #o ORDER BY object_id --debug output ROLLBACK
(The 3rd result set is complete - only one row.)
Note, that all rows except the first one were deleted. The numbering order of column
r does not seem to match what was requested. And there is a row with
r = 1.
This is SQL Server 2014 CU3 with trace flag 4199 enabled.