Logical query processing phase of INSERT, DELETE, and UPDATE in SQL queries

Question

I am curious about the logical query processing phase of SQL queries.

For SELECT queries, the logical query processing phase order is:

FROM
ON
OUTER
WHERE
GROUP BY
CUBE | ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

What is the order for INSERT, for UPDATE and for DELETE?

it is still the same as SELECT. The affected rows are determine first before any action is performed — Squirrel, Feb 29 '16 at 05:18
Depends what you mean by ' logical query processing phase'. If you take a look at the query plan you can see SORTS (ORDER BY) earlier than joins in the actual plan — Nick.Mc, Mar 05 '16 at 04:25

score 1 · Accepted Answer · answered Mar 05 '16 at 04:18

1

If you would like to know what the actual query processing order is, take a look at the execution plan. That will tell you step by step exactly what SQL Server is doing.

https://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx

answered Mar 05 '16 at 04:18

Jonathan Allen

68,373
70
259
447

Thank u.. Mr.Jonathan Allen – Mar 08 '16 at 06:15

Michael Betterton · Answer 2 · 2016-03-03T01:12:36.723

0

SQL Server: Source

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

edited Mar 03 '16 at 01:12

answered Mar 02 '16 at 03:56

Michael Betterton

114
12

Hi michael.. thank u for the answer. Can you explain what about insert ,update and delete query .. – Mar 02 '16 at 09:17
@Sruthy My understanding is there is no difference. A query plan of a Select/Update/Insert/Delete has no difference apart from the final operator. I'm struggling to find sources to back up my belief though. I welcome anyone who has evidence to the contrary however. – Michael Betterton Mar 03 '16 at 01:12

score 0 · Answer 3 · edited Oct 05 '17 at 16:07

I had the same question and could not find an answer on the internet. So i tried to logically derive the answer. Here is a simple UPDATE statement (with alias a for the table):

UPDATE tbl_employees a
  SET a.Name = 'Anna'
  WHERE a.Id = 122;

Obviously, whether SET nor WHERE can be performed before the table is identified, so UPDATE must be the first logical step. Proof: Alias a is working (in Microsoft Access).

Before applying the SET Statement, one needs to know what records to apply it on. So WHERE must go as the second logical step (omitting WHERE would alter all records in the table)

Applying the SET Statement on the WHERE-filtered recordset must be the third step.

Summing up, the logical processing order must be:

UPDATE (~equivalent to FROM)
WHERE
SET (~equivalent to SELECT)

Any other order seems absurd (can you hypothetically think of any other order?).

Once again, its my own logical derivation. I dont know for sure. I would appriciate any link to a serious internet resource.

Logical query processing phase of INSERT, DELETE, and UPDATE in SQL queries

3 Answers3