Please explain this delete top 100 SQL syntax
- by Patrick
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).
