What is the execution order of the PARTITION BY clause compared to other SQL clauses?

Window functions are executed/calculated at the same stage as SELECT, stage 5 in your table. In other words, window functions are applied to all rows that are "visible" in the SELECT stage.

In your second example

Select top 1 *, row_number() over (Partition by Name order by Date) from NPtable Where Date > '2018-01-02 00:00:00'

WHERE is logically applied before Partition by Name of the row_number() function.

Note, that this is logical order of processing the query, not necessarily how the engine physically processes the data.

If query optimiser decides that it is cheaper to scan the whole table and later discard dates according to the WHERE filter, it can do it. But, any kind of these transformations must be performed in such a way that the final result is consistent with the order of the logical steps outlined in the table you showed.

It is part of the SELECT phase of the query execution. There are different types of SELECT clauses, based on the query.

• SELECT FOR
• SELECT GROUP BY
• SELECT ORDER BY
• SELECT OVER
• SELECT INTO
• SELECT HAVING

PARTITION BY comes in the SELECT OVER clause. Here, a window of the result set is generated out of the result set generated in the previous stages: FROM, WHERE, GROUP BY etc.

The OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )

Arguments

PARTITION BY Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

value_expression Specifies the column by which the rowset is partitioned. value_expression can only refer to columns made available by the FROM clause. value_expression cannot refer to expressions or aliases in the select list. value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.

Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window functioncalculation is performed.

order_by_expression Specifies a column or expression on which to sort. order_by_expression can only refer to columns made available by the FROM clause. An integer cannot be specified to represent a column name or alias.

You can read more about it SELECT-OVER