Please explain this delete top 100 SQL syntax

Question

Basically I want to do this:

delete top( 100 ) from table order by id asc

but MS SQL doesn't allow order in this position

The common solution seems to be this:

DELETE table WHERE id IN(SELECT TOP (100) id FROM table ORDER BY id asc)

But I also found this method here:

delete table from (select top (100) * from table order by id asc) table

which has a much better estimated execution plan (74:26). Unfortunately I don't really understand the syntax, please can some one explain it to me?

Always interested in any other methods to achieve the same result as well.

EDIT: I'm still not getting it I'm afraid, I want to be able to read the query as I read the first two which are practically English. The above queries to me are:

delete the top 100 records from table, with the records ordered by id ascending
delete the top 100 records from table where id is anyone of (this lot of ids)
delete table from (this lot of records) table

I can't change the third one into a logical English sentence... I guess what I'm trying to get at is how does this turn into "delete from table (this lot of records)". The 'from' seems to be in an illogical position and the second mention of 'table' is logically superfluous (to me).

Answer 1

This is explained well here (The article talks about using a view but I presume the same logic must apply to your query if you are getting a better execution plan)

the first one reads the “deleted” portion of the table twice. Once to identify the rows to delete and then once more to perform the delete.

The second one avoids this.

Edit This seems to be more a question about syntax. The syntax for delete is described here.

The relevant bit is

DELETE 
    [ FROM ]
    { <object> | rowset_function_limited 
    }
    [ FROM <table_source> [ ,...n ] ] 

Your query is

delete alias 
from 
    (select top (100) * 
     from table 
     order by id asc) alias

You are using a derived table so need the FROM <table_source>. You are omitting the first optional FROM.

Answer 2

The delete command takes several similar forms, as the from keyword is optional, and the deletion table can be specified separate from the query selecting the records:

delete table where ...

delete from table where ...

delete table from <query...>

delete from table from <query...>

The idea with the last two ones is that you can first define which table to delete from, then you can specify a query that picks out the records to delete. The query can join in other tables, that's why you would need to separately define from which table to delete.

The query that you found just uses a subquery to pick out the records to delete.

Answer 3

What you are looking to do is a technique called a Fast Ordered Delete.

Take a look at the following Blog post: Performing Fast SQL Server Delete operations