Using WITH AS command on Sql Server

The reserved word 'WITH' is used in SQL to create a CTE ( Common Table Expression )

Explanation:

"We can think of a common table expression (CTE) as a temporary result set defined in the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table which is not stored as an object and remains only for the duration of a query. Unlike a derived table, a CTE can be self-referenced and can be referenced multiple times in the same query. "

Useful utility

We usually use CTE to form hierarchical structures because it is a much more performative and theoretically simpler form.

Example:

-- Cria tabela base
CREATE TABLE Empregado
(
    id INT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    cargo VARCHAR(50) NOT NULL,
    id_supervisor INT NULL 
        CONSTRAINT fk_productSales_pid 
        FOREIGN KEY REFERENCES Empregado(id)    
);

-- Popula a tabela (reparem na sintaxe do insert, 
-- só é permitida no SQL Server 2008 ou superior).
INSERT INTO Empregado VALUES 
(1, 'Thiago Zavaschi', 'Diretor', null),
(2, 'Angelina Jolie', 'Gerente de Vendas', 1),
(3, 'Megan Fox', 'Gerente de Marketing', 1),
(4, 'Uma Thurman', 'Vendedor', 2),
(5, 'Jessica Alba', 'Vendedor', 2),
(6, 'Julia Roberts', 'Estagiário', 3);

;WITH hierarquia(id, nome, cargo, id_supervisor, nivel_hierarquia)
AS
(
    SELECT  id, nome, cargo, id_supervisor, 1 as nivel_hierarquia
    FROM Empregado
    WHERE id_supervisor IS NULL 
    -- Perceba que aqui no âncora temos como retorno somente o diretor.
    UNION ALL -- Ligação para a recursão
    SELECT e.id, e.nome, e.cargo, e.id_supervisor, nivel_hierarquia + 1 
    FROM Empregado e
    INNER JOIN hierarquia h ON h.id= e.id_supervisor 
)
SELECT * FROM hierarquia

CTE tutorial link

This syntax would not work, the correct one is:

With ClientesAtivos AS
(
        SELECT Id from Client WHERE IsActive = 1
),
ClientesInativos AS -- apenas um WITH, os demais são separados por vírgula)
(
        SELECT Id from Client WHERE IsActive = 0
)
SELECT * FROM ClientesAtivos
UNION ALL
SELECT * FROM ClientesInativos

The above query would have exactly the same result as:

SELECT * FROM ClientesAtivos WHERE IsActive = 1
UNION ALL
SELECT * FROM ClientesInativos WHERE IsActive = 0

I do not know how to answer the question about performance, I particularly only use WITH to search X results for a given item, eg:

-- AS mais tocadas da semana

With MaisTocadas AS
    (
            SELECT *, ROW_NUMBER() OVER(PARTITION BY estilo ORDER BY quantidade DESC) AS validacao from musica
    )
    SELECT *
    FROM musica mu
    INNER JOIN maistocadas mt ON mu.id_musica=mt.id_musica AND mt.validacao <= 10
    WHERE mu.estilo IN ('ROCK', 'POP')
 -- o inner exclui as que não estão entre as 10 mais de cada estilo
    ORDER BY mu.estilo, mt.validacao DESC

This would look for the songs with more of each style, just an example.