SQL - Apresentando Common Table Expression (CTE)

Já ouviu falar de Common Table Expression (CTE). Aposto que não.

A Common Table Expression (CTE) não é um recurso novo e foi introduzido no SQL Server 2005 e continua presente até hoje mas pouco desenvolvedores conheço o recurso.

Mas o que vem a ser mesmo Common Table Expression (CTE) ?

Uma Common Table Expression (CTE) pode ser vista como um result set temporário que é definido no escopo de execução de uma única instrução SELECT, INSERT, UPDATE, DELETE, ou CREATE VIEW. Uma CTE é muito parecida com uma tabela derivada que não é armazenada como um objeto e que existe apenas durante a execução da consulta. Diferente de uma tabela derivada uma CTE pode ser auto-referenciada e pode ser referenciada várias vezes na mesma consulta.

Podemos usar uma CTE para:

Usando uma CTE temos as vantagens de melhorar a legibilidade e facilidade de manutenção de consultas complexas. A consulta pode ser dividida em blocos lógicos simples e separados. Estes blocos simples podem então ser utilizados para construir blocos mais complexos até que a consulta final seja montada.

As CTEs podem ser definidas em rotinas definidas pelo usuário, tais como funções, procedimentos armazenados, triggers, ou views.

Uma CTE é composta de:

  1. um nome de expressão representando a CTE,
  2. uma lista de colunas opcionais,
  3. e uma consulta definindo a CTE.

Depois que uma CTE é definida, ela pode ser referenciada como uma tabela ou view em uma instrução SELECT, INSERT, UPDATE, ou DELETE.

Uma CTE também pode ser usada em uma instrução CREATE VIEW, como parte de sua declaração SELECT que a define.

A estrutura básica da sintaxe de uma CTE é:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

A lista de nomes de colunas é opcional apenas se nomes distintos para todas as colunas resultantes forem fornecidos na definição da consulta.

A declaração para executar o CTE é:

SELECT <column_list>

FROM expression_name;

Abaixo um exemplo mais simples possível de definição e execução de uma CTE executada no SQL Server 2008 Express Edition:

A seguir um exemplo onde referenciarmos mais de uma vez uma CTE definida(ExemploCTE) executada no SQL Server 2008 Express Editon:

Agora um exemplo de uma consulta CTE (NumerosPares) recursiva onde usamos o operador UNION ALL para calcular os números pares até o número 10:

Vejamos agora um exemplo prático bem básico usando uma aplicação Windows Forms.

Abra o Visual C# 2010 Express Edition e crie um novo projeto do tipo Windows Forms Application com o nome Usando_Common Table Expression;

Inclua no formulário padrão um controle DataGridView (gdvResultado) um controle Button (btnExecutar) e uma Label e um controle TextBox(txtValor), conforme o leiaute abaixo:

Defina os seguintes namespaces no formulário:

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

No evento Click do botão - Executar Common Table Expression - inclua o código abaixo:

  private void btnExecutar_Click(object sender, EventArgs e)
        {
            //define a string de conexão usando uma instância anexada do banco de dados Northwind
            string sqlConnectString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\dados\Northwind.MDF;Integrated
Security=True;Connect Timeout=30;User Instance=True";

            double valor = Convert.ToDouble(txtValor.Text);

            //monta a Common Table Expression(CTE) usando o banco de dados Northwind e a tabela Products
            string sqlSelect = "WITH ProdutosCategoriasFiltroPorValor (ProductName, CategoryName, UnitPrice) AS " +
                                      "( SELECT p.ProductName, c.CategoryName, p.UnitPrice " +
                                      "FROM Products p " +
                                      "INNER JOIN Categories c ON c.CategoryID = p.CategoryID " +
                                      "WHERE p.UnitPrice > " + valor + ") " +
                                      "SELECT * " +
                                      "FROM ProdutosCategoriasFiltroPorValor " +
                                      "ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC ";
            try
            {
                // preenche o datatable
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
                da.Fill(dt);
                //preenche o DataGridView
                gdvResultado.DataSource = dt.DefaultView;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Erro : " + ex.Message);
            }
        }

Agora execute o projeto, defina um valor e veja o resultado:

Pegue o projeto completo aqui: Usando_CommonTableExpression.zip

Rom 1:22 Dizendo-se sábios, tornaram-se estultos,

Rom 1:23 e mudaram a glória do Deus incorruptível em semelhança da imagem de homem corruptível, e de aves, e de quadrúpedes, e de répteis.

Rom 1:24 Por isso Deus os entregou, nas concupiscências de seus corações, à imundícia, para serem os seus corpos desonrados entre si;

Rom 1:25 pois trocaram a verdade de Deus pela mentira, e adoraram e serviram à criatura antes que ao Criador, que é bendito eternamente. Amém.

Referências:

José Carlos Macoratti