Using WITH AS command in Sql Server

Question

I have seen this example below, I would like to know how this command With [name] AS works on Sql Server, and what are its uses compared to tables in memory or SubQueries, in terms of performance.

  With ClientesAtivos AS
    (
            SELECT Id from Client WHERE IsActive = 1
    ),
    ClientesInativos AS
    (
            SELECT Id from Client WHERE IsActive = 0
    )
    SELECT * FROM ClientesAtivos
    UNION ALL
    SELECT * FROM ClientesInativos

Answer 1

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 that 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 several times in the same query."

Practical 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

Link tutorial CTE

Answer 2

This syntax would not work, the correct 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 bring exactly the same result of:

SELECT id FROM Client WHERE IsActive = 1
UNION ALL
SELECT id FROM Client WHERE IsActive = 0

I do not know how to answer the doubt about performance, I particularly only use WITH to search X results of a given item, ex:

-- 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 the most amount of each style, just an example.