Entendiendo el Procesamiento de Consulta Lógico

T-SQL tiene lados tanto lógicos como físicos. El lado lógico es la interpretación conceptual de la consulta que explica cuál es el resultado correcto de la consulta. El lado físico es el procesamiento de la consulta por el motor de base de datos. El procesamiento físico debe producir el resultado definido por el procesamiento de consulta lógico. Para lograr este objetivo, el motor de base de datos puede aplicar la optimización. La optimización puede reorganizar los pasos del procesamiento de consulta lógico o eliminar los pasos por completo, pero sólo mientras el resultado sigua siendo el definido por el procesamiento de consulta lógico. El enfoque de este post es el procesamiento de consulta lógico, la interpretación conceptual de la consulta que define el resultado correcto.

T-SQL Como un Lenguaje Declarativo Similar al Inglés

T-SQL, siendo basado en el SQL estándar, es un lenguaje declarativo similar al inglés. En este lenguaje, declarativo significa definir qué quiere, a diferencia de los lenguajes imperativos que definen también como obtener lo que quiere. El SQL estándar describe la interpretación lógica de la petición declarativa (la parte "qué"), pero es responsabilidad del motor de base de datos averiguar cómo procesar físicamente la solicitud (la parte "cómo").

Por esta razón, es importante no sacar conclusiones relacionadas al rendimiento, de lo que aprenda sobre el procesamiento de consulta lógico. Eso es debido a que el procesamiento de consulta lógico, sólo define la exactitud de la consulta. Cuando se abordan aspectos del rendimiento de la consulta, necesita entender cómo funciona la optimización. Como se ha mencionado, la optimización puede ser bastante diferente del procesamiento de consulta lógico, porque tiene permitido cambiar las cosas, siempre y cuando el resultado logrado sea el definido por el procesamiento de consulta lógico.

Es interesante notar que el lenguaje estándar SQL no fue originalmente llamado así; más bien, fue llamado SEQUEL; un acrónimo de "lenguaje de consulta de Ingles estructurado". Pero entonces, debido a una disputa de marca con una compañía aérea, el lenguaje fue renombrado como SQL, por "lenguaje de consulta estructurado". Aun así, el punto es que proporciona sus instrucciones de una manera similar al inglés. Por ejemplo, considere la instrucción: "Bring me a soda from the refrigerator". Observe que en la instrucción en inglés, el objeto viene antes de la ubicación. Considere la siguiente sentencia en T-SQL.

SELECT shipperid, phone, companyname

FROM Sales.Shippers;

Observe la similitud de lo tecleado en orden de la consulta, al inglés. La consulta primero indica la lista SELECT con los atributos que desea retornar y luego la cláusula FROM con la tabla que desea consultar.

Ahora trate de pensar en el orden en el cual la petición es interpretada lógicamente. Por ejemplo, ¿Cómo definiría las instrucciones a un robot en vez de a un humano? La instrucción en inglés original para obtener una soda del refrigerador probablemente necesitaría ser revisada para algo como: "Ir al refrigerador; abrir la puerta; obtener una soda; traérmela".

Del mismo modo, el procesamiento lógico de una consulta debería primero saber qué tabla está siendo consultada antes de que pueda saber qué atributos pueden ser retornados de esa tabla. Por lo tanto, contrariamente a lo tecleado en orden de la consulta anterior, el procesamiento de consulta lógico debería ser de la siguiente manera.

FROM Sales.Shippers

SELECT shipperid, phone, companyname

Este es un ejemplo básico con sólo dos cláusulas de consulta. Por supuesto, las cosas pueden ser más complejas. Si entiende bien el concepto del procesamiento de consulta lógico, será capaz de explicar muchas cosas de la forma en que se comporta el lenguaje, cosas que son muy difíciles de explicar de otro modo.

Fases del Procesamiento de Consulta Lógico

Esta sección abarca el procesamiento de consulta lógico y las fases involucradas.

La sentencia principal utilizada para recuperar datos en T-SQL es la sentencia SELECT. Luego están las cláusulas de consulta principales especificadas en el orden en que se supone son escritas (conocidas como "tecleados en orden"):

1.  SELECT

2.  FROM

3.  WHERE

4.  GROUP BY

5.  HAVING

6.  ORDER BY

Como es mencionado, el orden de procesamiento de consulta lógico, que es el orden de interpretación conceptual, es diferente. Se inicia con la cláusula FROM. Este es el orden de procesamiento de consulta lógico de las seis cláusulas de consulta principales:

1.  FROM

2.  WHERE

3.  GROUP BY

4.  HAVING

5.  SELECT

6.  ORDER BY

Cada fase opera sobre una o más tablas como entradas y retorna una tabla virtual como salida. La tabla de salida de una fase es considerada la entrada a la siguiente fase. Esto está de acuerdo con operaciones sobre las relaciones que producen una relación. Note que si una ORDER BY es especificada, el resultado es no relacional.

Considere la siguiente consulta de ejemplo.

SELECT country, YEAR(hiredate) AS annocontrato, COUNT(*) AS numempleados

FROM HR.Employees

WHERE hiredate >= '20030101'

GROUP BY country, YEAR(hiredate)

HAVING COUNT(*) > 1

ORDER BY country, annocontrato DESC;

Esta consulta es emitida sobre la tabla HR.Employees. Filtra sólo los empleados que fueron contratados en o después del año 2003. Agrupa los empleados por país y por año de contrato. Se mantiene sólo los grupos con más de un empleado. Para cada grupo de calificación, la consulta retorna el año de contrato y el recuento de empleados, ordenados por país y año de contrato, en orden descendente.

Las siguientes secciones proporcionan una breve descripción de lo que sucede en cada fase, de acuerdo con el procesamiento de consulta lógico.

1.    Evaluar la Cláusula FROM

En la primera fase, la cláusula FROM es evaluada. Ahí es donde se indica las tablas que desea consultar y los operadores de tabla como los joins si son aplicables. Si necesita consultar una sola tabla, indicar el nombre de tabla como la tabla de entrada en esta cláusula. Entonces, la salida de esta fase es una tabla de resultado con todas las filas de la tabla de entrada. Ese es el caso, en la siguiente consulta: la entrada es la tabla HR.Employees (nueve filas), y la salida es un resultado de tabla con todas las nueve filas (sólo un subconjunto de los atributos son mostrados).

empid  hiredate    country

------ ----------- --------

1      2002-05-01   USA

2      2002-08-14   USA

3      2002-04-01   USA

4      2003-05-03   USA

5      2003-10-17   UK

6      2003-10-17   UK

7      2004-01-02   UK

8      2004-03-05   USA

9      2004-11-15   UK

2.    Filtrar Filas Basadas en la Cláusula WHERE

La segunda fase filtra las filas basadas en el predicado, en la cláusula WHERE. Sólo las filas para el cual el predicado se evalúa como Verdadero son retornadas.

En esta consulta, la fase de filtrado WHERE filtra sólo las filas para los empleados contratados en o después del 01 de enero del 2003. Seis filas son retornadas de esta fase y son proporcionadas como entrada a la siguiente fase. Aquí está el resultado de esta fase.

empid  hiredate    country

------ ----------- --------

4      2003-05-03   USA

5      2003-10-17   UK

6      2003-10-17   UK

7      2004-01-02   UK

8      2004-03-05   USA

9      2004-11-15   UK

Un error típico hecho por personas que no entienden el procesamiento de consulta lógico es intentar referirse en la cláusula WHERE a un alias de columna definido en la cláusula SELECT. Esto no está permitido porque la cláusula WHERE es evaluada antes de la cláusula SELECT. Como un ejemplo, considere la siguiente consulta.

SELECT country, YEAR(hiredate) AS annocontrato

FROM HR.Employees

WHERE annocontrato >= 2003;

Esta consulta falla con el siguiente error.

Mens. 207, Nivel 16, Estado 1, Línea 3

El nombre de columna 'annocontrato' no es válido.

Si entiende que la cláusula WHERE es evaluada antes que la cláusula SELECT, notará que este intento es erróneo porque en esta fase, el atributo annocontrato no existe todavía. Puede indicar la expresión YEAR(hiredate) >= 2003 en la cláusula WHERE. Mejor aún, por razones de optimización, utilice la forma hiredate >= '20030101' como se hace en la consulta original.

3.    Agrupar Filas Basadas sobre la Cláusula GROUP BY

Esta fase define un grupo para cada combinación distinta de valores en los elementos agrupados de la tabla de entrada. Luego, asocia cada fila de entrada a su respectivo grupo. La consulta que ha estado trabajando agrupa las filas por country y por YEAR(hiredate). En las seis filas en la tabla de entrada, este paso identifica cuatro grupos. Aquí están los grupos y las filas de detalle que están asociados con ellos (información redundante eliminada para propósitos de ilustración).

grupo     grupo         detalle detalle  detalle

country   YEAR(hiredate) empid  country  hiredate

-------- -------------- ------  ------- ----------

UK        2003             5      UK       2003-10-17

6      UK       2003-10-17

UK        2004             7      UK       2004-01-02

9      UK       2004-11-15

USA       2003             4      USA      2003-05-03

USA       2004             8      USA      2004-03-05

Como puede ver, el grupo UK, 2003 tiene dos filas de detalles asociados con los empleados 5 y 6; el grupo para UK, 2004 también tiene dos filas de detalles asociados con los empleados 7 y 9; el grupo para USA, 2003 tiene una fila de detalle asociada con el empleado 4; el grupo para USA, 2004 también tiene una fila de detalle asociada con el empleado 8.

El resultado final de esta consulta tiene una fila representando cada grupo (a menos que sea filtrada). Por lo tanto, las expresiones en todas las fases que tienen lugar después de la fase de agrupamiento actual son algo limitadas. Todas las expresiones procesadas ​​en fases posteriores deben garantizar un único valor por grupo. Si se refiere a un elemento de la lista GROUP BY (por ejemplo, country), ya tiene como garantía, de que una referencia está permitida. Sin embargo, si desea referirse a un elemento que no forma parte de su lista GROUP BY (por ejemplo, empid), debería estar contenida en una función agregada como SUM o MAX. Esto se debe a que múltiples valores son posibles en el elemento dentro de un solo grupo, y la única manera de garantizar que sólo uno será retornado es la agregación de valores.

4.    Filtrar Filas Basada en la Cláusula HAVING

Esta fase es también responsable del filtrado de datos basados ​​en un predicado, pero es evaluado después de que los datos han sido agrupados; por lo tanto, es evaluado por grupo y por grupos de filtros en conjunto. Como es habitual en T-SQL, el predicado de filtrado se puede evaluar a Verdadero, Falso o Desconocido. Sólo los grupos para el cual el predicado se evalúa a Verdadero son retornados de esta fase. En este caso, la cláusula HAVING utiliza el predicado COUNT(*) > 1, lo que significa filtrar solo los grupos país y año de contrato que tienen más de un empleado. Si nos fijamos en el número de filas que fueron asociados con cada grupo en el paso anterior, notará que sólo los grupos UK, 2003 y UK, 2004 califican. Por lo tanto, el resultado de esta fase tiene los siguientes grupos restantes, que se muestran aquí con sus filas de detalle asociados.

grupo    grupo         detalle detalle  detalle

country  YEAR(hiredate) empid  country  hiredate

-------- -------------- ------ ------- ----------

UK        2003            5      UK      2003-10-17

6      UK      2003-10-17

UK        2004            7      UK      2004-01-02

9      UK      2004-11-15

5.    Procesar la cláusula SELECT

La quinta fase es la responsable del procesamiento de la cláusula SELECT. Lo que es interesante acerca de esto, es el punto en el procesamiento de consulta lógico donde es evaluado, casi al final. Eso es interesante considerando el hecho de que la cláusula SELECT aparece primero en la consulta.

Esta fase incluye dos pasos principales. El primer paso es evaluar las expresiones en la  lista SELECT y producir los atributos resultantes. Esto incluye la asignación de atributos con nombres si son derivados de expresiones. Recuerde que si una consulta es una consulta agrupada, cada grupo es representado por una sola fila en el resultado. En la consulta, dos grupos permanecen después del procesamiento del filtro HAVING. Por lo tanto, este paso genera dos filas. En este caso, la lista SELECT retorna para cada grupo country y annocontrato, una fila con los siguientes atributos: country, YEAR(hiredate) con alias annocontrato y COUNT(*) con alias numempleados.

El segundo paso en esta fase es aplicable si se indica la cláusula DISTINCT, en cuyo caso este paso elimina duplicados. Recuerde que T-SQL está basada en la teoría de multiconjuntos más que en la teoría de conjuntos, y por lo tanto, si los duplicados son posibles en el resultado, es su responsabilidad eliminarlos con la cláusula DISTINCT. En el caso de esta consulta, este paso no es aplicable. Aquí está el resultado de esta fase en la consulta.

country annocontrato numempleados

-------- ----------  ------------

UK       2003         2

UK       2004         2

Si necesita un recordatorio de como luce la consulta, aquí está de nuevo.

SELECT country, YEAR(hiredate) AS annocontrato, COUNT(*) AS numempleados

FROM HR.Employees

WHERE hiredate >= '20030101'

GROUP BY country, YEAR(hiredate)

HAVING COUNT(*) > 1

ORDER BY country, annocontrato DESC;

La quinta fase retorna un resultado relacional. Por lo tanto, el orden de las filas no está garantizado. En el caso de esta consulta, hay una cláusula ORDER BY que garantiza el orden en el resultado, pero esto se discutirá cuando se describa la siguiente fase. Lo importante a destacar es que el resultado de la fase que procesa la cláusula SELECT es todavía relacional.

Además, recuerde que esta fase asigna alias de columna, como annocontrato y numempleados. Esto significa que los alias de columnas de nueva creación no son visibles a las cláusulas procesadas en anteriores fases, como el FROM, WHERE, GROUP BY y HAVING.

Note que un alias creado por la fase SELECT no es visible a otras expresiones que aparecen en la misma lista SELECT. Como ejemplo, la siguiente consulta no es válida.

SELECT empid, country, YEAR(hiredate) AS annocontrato, annocontrato-1 AS annoprevio

FROM HR.Employees;

Esta consulta genera el siguiente error.

Mens. 207, Nivel 16, Estado 1, Línea 1

El nombre de columna 'annocontrato' no es válido.

La razón de que esto no está permitido es que, conceptualmente, T-SQL evalúa todas las expresiones que aparecen en la misma fase de procesamiento de consulta lógico en una manera de todo a la vez. Observe el uso de la palabra conceptualmente. SQL Server no necesariamente procesa físicamente todas las expresiones en el mismo punto en el tiempo, pero tiene que producir un resultado como si lo hiciera. Este comportamiento es diferente al de muchos otros lenguajes de programación donde las expresiones por lo general son evaluadas en un orden de izquierda a derecha, haciendo un resultado producido en una expresión visible, a una que aparece a su derecha. Pero T-SQL es diferente.

6.    Manejar el Orden de Presentación

La sexta fase es aplicable si la consulta tiene una cláusula ORDER BY. Esta fase es responsable de retornar el resultado en un orden de presentación específico de acuerdo a las expresiones que aparecen en la lista ORDER BY. La consulta indica que las filas de resultado deberían ser ordenadas primero por country (en orden ascendente por defecto) y, a continuación, por numemployees, descendente, produciendo la siguiente salida.

country  annocontrato numempleados

--------  ----------  ------------

UK        2004         2

UK        2003         2

Observe que la cláusula ORDER BY es la primera y la única cláusula que le es permitida referirse a los alias de columna definidas en la cláusula SELECT. Esto se debe a que la cláusula ORDER BY es la única a ser evaluada después de la cláusula SELECT.

A diferencia de las fases anteriores, donde el resultado era relacional, la salida de esta fase no es relacional porque tiene un orden garantizado. El resultado de esta fase es lo que el SQL estándar llama un cursor. Note que el uso del término cursor aquí es conceptual. T-SQL también soporta un objeto llamado un cursor que es definido basado en un resultado de una consulta, y que le permite traer filas, de una en una, en un orden especificado.

Puede preocuparse de retornar el resultado de una consulta en un orden específico, para propósitos de presentación o si el caller necesita consumir el resultado en esa manera a través de algunos mecanismos de cursor que recupera filas de una en una. Pero recuerde que tal procesamiento no es relacional. Si necesita procesar el resultado de la consulta en una manera relacional, por ejemplo, definir una expresión de tabla como una vista basada en la consulta, el resultado necesitará ser relacional. Además, el ordenamiento de datos puede agregar costo al procesamiento de consultas. Si no le preocupa el orden en que se retornen las filas de resultados, puede evitar este costo innecesario por no añadir una cláusula ORDER BY.

Una consulta puede especificar las opciones de filtrado TOP u OFFSET-FETCH. Si lo hace, la misma cláusula ORDER BY que normalmente es utilizada para definir el orden de presentación también define que filas filtrar para estas opciones. Es importante notar que, así como un filtro es procesado después de que la fase SELECT evalúa todas las expresiones y elimina los duplicados (en caso de que una cláusula DISTINCT sea especificada). Podría incluso considerar los filtros TOP y OFFSET-FETCH como siendo procesados en su propia fase número 7. La consulta no indica un filtro de este tipo, y por lo tanto, esta fase es inaplicable en este caso.

Ejercicio 1: Corregir un Problema con Agrupaciones

En este ejercicio, se le presentará una consulta agrupada que falla cuando intenta ejecutarla. Se le proporciona instrucciones sobre cómo solucionar la consulta.

1.    Abra el SSMS y conéctese a la base de datos de ejemplo TSQL2012.

2.    Escriba la consulta siguiente en la ventana de consulta y ejecútela.

SELECT custid, orderid

FROM Sales.Orders

GROUP BY custid;

La consulta supuestamente retorna para cada cliente el ID Cliente y el máximo ID Pedido para ese cliente, pero en lugar de esto falla. Trate de determinar por qué la consulta falló y lo que necesita ser revisado para que pueda retornar el resultado deseado.

3.    La consulta falló porque orderid no aparece en la lista GROUP BY ni dentro de una función agregada. Hay varios posibles valores orderid por cliente. Para corregir la consulta, necesita aplicar una función agregada al atributo orderid. La tarea es retornar el valor orderid máximo por cliente. Por lo tanto, la función agregada debería ser MAX. Su consulta debería tener el siguiente aspecto.

SELECT custid, MAX(orderid) AS maxorderid

FROM Sales.Orders

GROUP BY custid;

Ejercicio 2: Corregir un Problema con Alias

En este ejercicio, se le presentará otra consulta agrupada que falla, esta vez debido a un problema de alias. Al igual que en el primer ejercicio, se le proporciona instrucciones sobre cómo corregir la consulta.

1.    Borre la ventana de consulta, escriba la siguiente consulta, y ejecútela.

SELECT shipperid, SUM(freight) AS totalfreight

FROM Sales.Orders

WHERE freight > 20000.00

GROUP BY shipperid;

La consulta supuestamente retorna sólo distribuidores para los que el valor total del flete es superior a 20,000, pero en su lugar, retorna un conjunto vacío. Trate de identificar el problema en la consulta.

2.    Recuerde que la cláusula de filtrado WHERE es evaluada por fila, no por grupo. La consulta filtra los pedidos individuales con un valor de flete superior a 20,000, y no hay ninguno. Para corregir la consulta, es necesario aplicar el filtro por cada grupo de distribuidores, no por cada pedido. Necesita filtrar el total de todos los valores de flete por distribuidor. Esto puede ser obtenido utilizando el filtro HAVING. Intente corregir el problema utilizando la siguiente consulta.

SELECT shipperid, SUM(freight) AS totalfreight

FROM Sales.Orders

GROUP BY shipperid

HAVING totalfreight > 20000.00;

Pero esta consulta también falla. Trate de identificar por qué falla y lo que necesita ser revisado para lograr el resultado deseado.

3.    El problema ahora es que la consulta intenta referirse en la cláusula HAVING al alias totalfreight, el cual es definido en la cláusula SELECT. La cláusula HAVING es evaluada antes de la cláusula SELECT, y por lo tanto, el alias de la columna no le es visible. Para solucionar el problema, necesita referirse a la expresión SUM (freight) en la cláusula HAVING, de la siguiente manera.

SELECT shipperid, SUM(freight) AS totalfreight

FROM Sales.Orders

GROUP BY shipperid

HAVING SUM(freight) > 20000.00;