The with clause is also known as common table expression (CTE) and subquery refactory. It is a temporary named result set.
SQL:1999 added the with clause to define "statement scoped views". They are not stored in the database scheme: instead, they are only valid in the query they belong to. This makes it possible to improve the structure of a statement without polluting the global namespace.
with <QUERY_NAME_1> (<COLUMN_1>[, <COLUMN_2>][, <COLUMN_N>]) as
[,<QUERY_NAME_2> (<COLUMN_1>[, <COLUMN_2>][, <COLUMN_N>]) as
with sales_tbl as (
) sales (emp_name,dealer_id,sales)
select ROW_NUMBER() over (order by dealer_id) as rownumber, *
WITH [counter] AS (
SELECT 1 AS n -- Executes first and only once.
UNION ALL -- UNION ALL must be used.
SELECT n + 1 -- The portion that will be executed
FROM [counter] -- repeatedly until there's no row
-- to return.
WHERE n < 50 -- Ensures that the query stops.
SELECT n FROM [counter]