Voce já usou uma CTE?

      CTE é um recurso adicionado no SQL 2005 e pouquissimo utilizado por nossas bandas. Elas são parecidas com subqueries,você cria e nomeia um SELECT qualquer e ela passa a se comportar como uma tabela dentro do seu batch.
      Diferentemente da subquery, a CTE pode se autoreferenciar e ser reaproveitado diversas vezes em um mesmo comando. Além de fornecer recursividade de um jeito bem bacana


Em um exemplo pratico.

            Imagine que temos uma tabela padrão de clientes e uma tabela que armazena todas as compras que o cliente armazenou. Segue os scripts


-- Criando as tabelas

CREATE TABLE Cliente
      (
       ClienteID int identity(1,1) PRIMARY KEY,
       Cliente varchar(100) NOT NULL
       )
GO
CREATE TABLE Compras
      (
       CompraID int identity(1,1) PRIMARY KEY,
       ClienteID int NOT NULL FOREIGN KEY REFERENCES Cliente(ClienteID),
       DataCompra datetime
        )

-- Populando as tabelas (opcional, voce pode inventar seu proprio metodo de população)

INSERT INTO Cliente (Cliente)
SELECT UPPER(name) FROM master.sys.objects

INSERT INTO Compras (ClienteID,DataCompra)
SELECT
      Cliente.ClienteID,
      CAST(dateadd(MINUTE,(RAND(clienteid) * 100000),creation_time) as DATE)
FROM sys.dm_os_threads
CROSS JOIN  Cliente
GROUP BY
     CAST(dateadd(MINUTE,(RAND(clienteid) * 100000),creation_time) as DATE),Cliente.ClienteID

------------------------------------------------------------ 
   Imagine a necessidade de um campo DataUltimaCompra na tabela Cliente.
   Um UPDATE entre as tabelas mais um Group By não é possivel. Com uma CTE a coisa fica mais simples. Veja abaixo:


-- Adiciona o campo
ALTER TABLE Cliente ADD DataUltimaCompra datetime  

-- Declaração da CTE
;WITH CTE_UltimaCompra (ClienteID,UltimaCompra)
AS
(     SELECT
            ClienteID,
            MAX(DataCompra) UltimaCompra
      FROM  Compras
      GROUP BY
            ClienteID
)

-- Comando utilizando a CTE como se fosse uma tabela
UPDATE C
SET C.DataUltimaCompra = CTE.UltimaCompra
FROM Cliente C
INNER JOIN CTE_UltimaCompra CTE ON C.ClienteID = CTE.ClienteID

-- Com uma pequena modificação voce pode até colocar esse script em uma trigger
-- e manter esse campo atualizado na inserção de uma nova compra

CREATE TRIGGER tr_AtualizaUltimaCompra
ON Compras
FOR Insert,Update
AS
      WITH CTE_UltimaCompra (ClienteID,UltimaCompra)
      AS
      (     SELECT
                  C.ClienteID,
                  MAX(C.DataCompra) UltimaCompra
            FROM Compras C
            inner join inserted I ON C.ClienteID = I.ClienteID
            -- Atenção ao pulo do gato acima
            GROUP BY
                 C.ClienteID
      )

      UPDATE C
      SET   C.DataUltimaCompra = CTE.UltimaCompra
      FROM Cliente C
      INNER JOIN CTE_UltimaCompra CTE ON C.ClienteID = CTE.ClienteID

     No próximo post coloco um exemplo de recursividade com CTE.

     Abaixo o link do nosso querido Books Online com uma descrição completa


         Abraços!