September 19, 2018 by Kenneth Fisher
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.