Common Table Expressions
Una de las ventajas que nos trae SQL Server 2005 es el uso de expresiones de tablas comunes, o common table expressions. Tratando de interpretarlo, es como un conjunto de datos resultante temporal. Es muy útil en momento de reemplazar subqueries o tablas derivadas.
En el siguiente ejemplo, vemos una CTE llamada mid, que calcula la mediana de un conjunto de datos.
WITH mid AS
(SELECT ((MAX(value) - MIN(value)) / 2)
AS midval FROM invoices)
SELECT
CASE WHEN value > mid.midval THEN 0
ELSE 1 END AS half, invoices.*
FROM invoices, mid ORDER BY half
Aquí podremos ver entonces de qué se componen los CTE. Empiezan con una cláusula WITH, y la expresión que vamos a tener la colocamos entre paréntesis. En caso que tengamos múltiples CTE's, las separamos por comas. Luego colocamos la sentencia SELECT.
La gran ventaja de esto es que si es utilizada más de una vez, nos provee muchos menos lecturas que un subquery. Por ejemplo:
WITH low AS (SELECT ((max(amount)) / 3)
AS v FROM invoices),
high AS (SELECT (2 * max(amount) / 3) AS v FROM invoices)
select id, amount, amount - low.v
FROM invoices, low, high
WHERE invoices.amount > low.v
AND invoices.amount <= high.v
SELECT id, amount,
amount - (SELECT (max(amount) / 3) FROM invoices)
FROM invoices where
amount > (SELECT (max(amount) / 3) FROM invoices) and
amount < (SELECT (2 * max(amount) / 3) FROM invoices)
Si comparamos los planes de ejecución, notaremos una mejora gracias a la CTE.
De la misma manera, nos ahorran grandes procedimientos. Por ejemplo:
WITH low AS (SELECT ((MAX(amount)) / 3)
AS v FROM invoices),
high AS (SELECT (2 * MAX(amount) / 3)
AS v FROM invoices)
SELECT id, amount, amount - low.v
FROM invoices, low, high
WHERE invoices.amount > low.v
AND invoices.amount <= high.v
En lugar de
declare @high int
declare @low int
SELECT @high = max(amount) FROM invoices
SELECT @low = min(amount) FROM invoices
SELECT id, amount, amount - @low FROM invoices
El resultado final será el mismo, pero la desventaja es que no puede ser utilizada en una vista; cosa que el CTE si puede hacer. En palabras algo más técnicas, la CTE se vuelve declarativa, mientras que el query equivalente es un procedimiento.
En el siguiente ejemplo, vemos una CTE llamada mid, que calcula la mediana de un conjunto de datos.
WITH mid AS
(SELECT ((MAX(value) - MIN(value)) / 2)
AS midval FROM invoices)
SELECT
CASE WHEN value > mid.midval THEN 0
ELSE 1 END AS half, invoices.*
FROM invoices, mid ORDER BY half
Aquí podremos ver entonces de qué se componen los CTE. Empiezan con una cláusula WITH, y la expresión que vamos a tener la colocamos entre paréntesis. En caso que tengamos múltiples CTE's, las separamos por comas. Luego colocamos la sentencia SELECT.
La gran ventaja de esto es que si es utilizada más de una vez, nos provee muchos menos lecturas que un subquery. Por ejemplo:
WITH low AS (SELECT ((max(amount)) / 3)
AS v FROM invoices),
high AS (SELECT (2 * max(amount) / 3) AS v FROM invoices)
select id, amount, amount - low.v
FROM invoices, low, high
WHERE invoices.amount > low.v
AND invoices.amount <= high.v
SELECT id, amount,
amount - (SELECT (max(amount) / 3) FROM invoices)
FROM invoices where
amount > (SELECT (max(amount) / 3) FROM invoices) and
amount < (SELECT (2 * max(amount) / 3) FROM invoices)
Si comparamos los planes de ejecución, notaremos una mejora gracias a la CTE.
De la misma manera, nos ahorran grandes procedimientos. Por ejemplo:
WITH low AS (SELECT ((MAX(amount)) / 3)
AS v FROM invoices),
high AS (SELECT (2 * MAX(amount) / 3)
AS v FROM invoices)
SELECT id, amount, amount - low.v
FROM invoices, low, high
WHERE invoices.amount > low.v
AND invoices.amount <= high.v
En lugar de
declare @high int
declare @low int
SELECT @high = max(amount) FROM invoices
SELECT @low = min(amount) FROM invoices
SELECT id, amount, amount - @low FROM invoices
El resultado final será el mismo, pero la desventaja es que no puede ser utilizada en una vista; cosa que el CTE si puede hacer. En palabras algo más técnicas, la CTE se vuelve declarativa, mientras que el query equivalente es un procedimiento.
