Did you know you can’t do this?

DELETE TOP (10)
FROM SalesOrderDetail
ORDER BY SalesOrderID DESC;

Msg 156, Level 15, State 1, Line 8

Incorrect syntax near the keyword ‘ORDER’.

I didn’t. Until I tried it anyway. Turns out, it says so right in the limitations section of BOL. Fortunately, that same section does have a recommendation on how to move forward.

If you need to use TOP to delete rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause in a subselect statement.

Here is my version of the example they mentioned.

DELETE FROM SalesOrderDetail
WHERE SalesOrderDetailID IN  
   (SELECT TOP 10 SalesOrderDetailID
    FROM SalesOrderDetail
    ORDER BY SalesOrderID DESC);  
GO

And this works fine. But I remembered that you can update (and probably delete) from a CTE.

Well, I hadn’t tried it out before, so let’s give it a shot.

WITH MyCTE AS (
SELECT TOP (10) * 
FROM SalesOrderDetail
ORDER BY SalesOrderID DESC)
DELETE FROM MyCTE;

Well, the code is shorter. And it does work! So how about performance? Well, I checked the IO, and much like with the UPDATE it uses about half the IO (which when you look at the extra join in the subquery kind of makes sense). I ran both pieces of code 50 times and the subquery runs at about 62ms while the CTE runs at 48ms. Not a big difference, but these are pretty well indexed and very small. I’m going to go with the CTE as the better version.