How to sort the result set of an UPDATE statement that uses the OUTPUT clause?

Question

The title says it all. I'd like to sort the rows returned by an UPDATE statement, but there's no direct way to do this according to the msdn page for the OUTPUT Clause, which says:

SQL Server does not guarantee the order in which rows are processed and returned by DML statements using the OUTPUT clause.

Since I can't just tack "order by" onto my original query, my next approach is to just put the results into a temporary table or table variable, and then select and order from that. I should be able to figure out a solution using temporary tables, but I don't think I've ever used a table variable before. Is there a reason to prefer one over the other in this context? Or is there a better way to get sorted results from an OUTPUT clause (i.e. w/out using a temp table or table variable)?

The DB platform is SQL Server 2005.

Here's the original query; I want to sort on the ReleaseDate field.

Update dbo.WFItem
Set ApprovedDate = getdate()
Output inserted.WFItemID, inserted.Title, inserted.Teaser
Where ApprovedDate is null

The table it runs against looks something like this:

CREATE TABLE [dbo].[WFItem](
    [WFItemID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [varchar](255) NULL,
    [Teaser] [varchar](255) NULL,
    [ReleaseDate] [datetime] NULL,
    [ApprovedDate] [datetime] NULL,
    [SentDate] [datetime] NULL,
 CONSTRAINT [PK_WFItem] PRIMARY KEY CLUSTERED 
(
    [WFItemID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

I guess your only option would be to turn the OUTPUT clause into an OUTPUT INTO and store the values in a temporary (in-memory) table and sort them there, when selecting from the temporary table.

DECLARE @UpdateOutputTable TABLE 
         (WFItemID INT, Title VARCHAR(255), Teaser VARCHAR(255), ReleaseDate DATETIME)

and then change your UPDATE statement to be:

Update dbo.WFItem
Set ApprovedDate = getdate()
Output inserted.WFItemID, inserted.Title, inserted.Teaser, inserted.ReleaseDate
    INTO @UpdateOutputTable 
Where ApprovedDate is null

and then select from the temporary table:

SELECT WFItemID, Title, Teaser
FROM @UpdateOutputTable
ORDER BY ReleaseDate

Marc

Answer 2

You can output results into table variable and then select from that table results in any order Here is an example from the same MSDN page that you referred to in your Q. but I added Order By in the end

DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 
OUTPUT INSERTED.EmployeeID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar
Order by EmpID;

Answer 3

There is no way to sort an OUTPUT, to the best of my knowledge. As to which object to use, it really depends on the data volumes that you're dealing with. Here's an article comparing temporary tables to table variables:

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/18/11-0-temporary-tables-table-variables-and-recompiles.aspx

Just use OUTPUT INTO to drop your results into your temporary object, then fetch them back using an ORDER BY clause.