La función lógica CHOOSE() en SQL-Server

La función lógica CHOOSE() se añadió a SQL-Server desde la versión 2012, y su funcionamiento es similar a recuperar valores de un array. Es una función que recibe al menos tres valores: el primero, que será un valor índice, y tras él una serie de valores separados por comas, requeríendose al menos dos. La función devolverá el valor de la lista que coincida posicionalmente con el valor del índice.

Veámoslo con un ejemplo simple:

--Esto devolvería "Pringao" por ser el tercer valor:
SELECT CHOOSE ( 3, 'Jefazo', 'Jefecillo', 'Pringao', 'Becario' ) AS TuPuesto  

Lógicamente el primer valor no tiene por qué ser una constante, puede salir de una consulta. Veámoslo en un ejemplo similar al de arriba:

--Si el IdPuesto es 1 devuelve "Jefazo", si es 4 devuelve "Becario"
SELECT IdPuesto, CHOOSE ( IdPuesto, 'Jefazo', 'Jefecillo', 'Pringao', 'Becario' ) AS NombrePuesto from Plantilla  

O de una variable:

SELECT @Puesto, CHOOSE ( @Puesto, 'Jefazo', 'Jefecillo', 'Pringao', 'Becario' ) AS NombrePuesto from Plantilla 


Anuncios

Common Table Expression (CTE): la sentencia WITH de SQL-Server

Las expresiones de tabla común (common table expression o CTE) fueron añadidas por Microsoft a SQL-Server a partir de la versión 2008. Cuando hablamos de CTE hablamos de un artefacto que nos mantiene en memoria el resultado de una consulta, que podremos llamar luego dentro de esa misma consulta. Esta cláusula también se puede utilizar en una instrucción CREATE VIEW como parte de la instrucción SELECT que la define.

Sintaxis de una CTE

La sintaxis básica de una CTE sería la siguiente:

WITH nombreDeNuestra_CTE (Columna1, Columna2, Columna3)  
AS  
-- Aquí definimos la consulta que la crea.  
(  
    SELECT Columna1, Columna2, Columna3
    FROM BaseDatos.dbo.Tabla
    Where Condicion=Condicion
)  

Por ejemplo, imagina que tenemos una tabla con vendedores y otra con sus ventas, donde el Id del vendedor actúa como clave externa en cada línea de ventas. Podemos usar una CTE para tener un resultado temporal con su total de ventas de 2018. Algo así:

WITH Ventas_CTE (IdVendedor, NombreVendedor, TotalVendido)  
AS  
(  
    SELECT 
      v.Id as IdVendedor, 
      v.Nombre as NombreVendedor, 
      SUM(vt.Importe) AS TotalVentas 
    FROM Vendedores v inner join ventas vt on v.Id = vt.IdVendedor
    WHERE YEAR(vt.Fecha)=2018
)  

Ahora podríamos usar la CTE del ejemplo de arriba en una consulta como si de una tabla normal se tratase. Por ejemplo, podríamos sacar todos los departamentos donde haya vededores que hayan superado los 100.000 euros en ventas haciendo un join contra una tabla de departamentos:

SELECT Distinct
  d.Nombre
FROM
  Departamentos d
inner join
  Departamentos_Vendedores vd
on
  d.Id = vd.Idep
inner join
  Ventas_CTE v
on
  v.IdVendedor = vd.IdVend
where
  vd.TotalVendido > 100000

Hay que recordar que las cláusulas INTO, ORDER BY, FOR BROWSE y OPTION no están permitidas en una consulta de definición de una CTE. Sí permite el uso de operadores de conjuntos como UNION ALL, UNION, INTERSECT o EXCEPT y también permite referencias a tablas externas, e incluso a tablas situadas en servidores remotos.

En el ejemplo hemos usado un SELECT después de crear nuestra CTE, pero podríamos haber usado una instrucción INSERT, DELETE o UPDATE también. Incluso la podemos utilizar para definir un cursor.

¿Por qué usar una CTE?

Aparte del potencial que tienen las CTE por permitir crear una CTE recursiva, cosa de la que ya hablaremos más adelante en otro artículo, las CTE principalmente nos permiten sustituir a subconsultas y a variables de tabla.

En el caso de las subconsultas realmente la CTE no nos da ninguna ventaja en el rendimiento, pero sí nos permitirá tener un código más ordenado y más manejable, lo que facilita la legibilidad del mismo y las tareas de mantenimiento.

En el caso de la variables de tipo tabla ahí sí que las CTE nos dan un rendimiento mejor en la consulta, por lo que siempre serán una opción más recomendable.

También uno podría pensar en cambiar una Vista por una CTE. Si se trata de conjuntos de datos muy grandes, con muchas líneas, la Vista tendrá un rendimiento mayor por tratarse de un objeto creado en la base de datos que no permite definir índices (ya hablamos aquí sobre cuándo esto es recomendable y cuándo no).

SQL-Server: Usar CASE en una cláusula ORDER BY

La ordenación de resultados en una consulta SQL suele ralentizar la respuesta de la consulta, aunque en muchos casos necesitamos tener nuestro resultado ordenado. Hoy me veía con este caso particular, en una aplicación que conecta con una base de datos de SQL-Server: en una pantalla se muestran una serie de líneas de deuda, algunas haciendo referencia a tratamientos ya realizados y otras a tratamientos pendientes. En caso de que estén realizados habría que ordenarlos por fecha de realización, en caso de que no estén realizados sería por fecha de creación.

Para eso podemos utilizar una sentencia CASE en la cláusula ORDER BY:

SELECT 
  FechaCrea,   
  Concepto,
  Precio,
  ImportePagado,
  Realizado,
  FechaRealizado 
FROM 
  vistaPagos  
WHERE 
  Precio > 0 
ORDER BY 
 CASE Realizado WHEN True THEN FechaRealizado  
 ELSE FechaCrea END;  

Veamos ahora otra posibilidad de uso del CASE: cuando queremos que la consulta reciba un parámetro con el campo por el que ordenar los resultados:

SELECT 
  FechaCrea,   
  Concepto,
  Precio,
  ImportePagado,
  Realizado,
  FechaRealizado 
FROM 
  vistaPagos  
WHERE 
  Precio > 0 
ORDER BY 
CASE @OrdenaPor 
   WHEN 'Fecha' THEN FechaCrea
   WHEN 'FReal' THEN FechaRealizado       
 END,
CASE @OrdenaPor 
   WHEN 'Precio' THEN Precio
   WHEN 'Pendiente' THEN ImportePagado   
 END
;  

¿Por qué hay dos CASE separados en el segundo ejemplo? Bueno, CASE necesita que los tipos devueltos en la expresión sean compatibles. En el primer caso devolverá fechas, en el segundo devolverá importes. En caso de que no se cumpla ninguna de las condiciones devolverá un null, así que no debería hacer fallar la consulta.

Algunos consejos para optimizar consultas en SQL-Server

Vamos con una serie de pequeños trucos para optimizar nuestras consultas en SQL-Server y conseguir un mejor rendimiento, como ya habíamos hecho en el pasado con MySQL.

  • Añadir al nombre de la tabla el propietario y el esquema hará más rápida nuestra consulta. Si no lo ponemos SQL-Server buscará en todos los esquemas hasta encontrar el objeto.
  • Un clásico: No usar el comodín * en las consultas, poner el nombre de las columnas que queremos traer ayudará a ahorrar tiempo y memoria.
  • Como ya dijimos en un artículo anterior, NOT EXISTS tiene un mejor rendimiento que NOT IN.
  • No uses el prefijo sp_ para nombrar tus procedimientos almacenados. Aunque suene raro, al ser el prefijo que SQL-Server usa para los procedimientos almacenados por defecto siempre que se invoque uno que empieza por sp_ primero lo buscará en la base de datos maestra.
  • Comprueba que tus índices sean eficientes y que no estén demasiado saturados. Recuerda usar las claves primarias y externas de forma adecuada y tener la bases de datos bien normalizadas
  • Recuerda que las vistas con índices, como ya comentamos en otro artículo, mejoran la velocidad de consulta pero penalizan las operaciones de borrado, lectura y escritura.
  • No uses variables de tipo table en los joins de las consultas. Una tabla temporal o una expresión de tabla común (CTE) te darán un mejor rendimiento.
  • No utilices las cláusulas Distinct, Group By y Order By si no es indispensable. Consumen mucha memoria.
  • SQL-Server siempre devuelve la cuenta del número de filas afectado por las consultas de INSERT, DELETE, UPDATE y SELECT. Utilizar la cláusula SET NOCOUNT ON evitará esto ahorrando memoria y tiempo. En una consulta simple apenas se nota, pero en consultas con muchos joins o subconsultas ahorra mucho tiempo.
  • Las funciones son uno de los puntos débiles de SQL-Server, ralentizan enormemente las consultas. La ventaja que aportan es la reutilización de código, pero tienes que ver si te compensa su uso por lo que penalizan el rendimiento. En muchos casos una subconsulta o una tabla temporal serán más rápidas.

Borrar un trigger solo si este existe, en SQL-Server

El otro día vimos cómo hacerlo con una tabla, hoy vamos a ver cómo borrar un trigger de una base de datos, pero solo en caso de que exista (para evitar mensajes de error en nuestros scripts).

De nuevo, como en el ejemplo anterior, antes de SQL-Server 2016 se hacía de una forma y a partir de esa versión tenemos un sintaxis simplificada:

Versiones anteriores a SQL-Server 2016:

Tendríamos que hacer una consulta para ver si existe el trigger y luego lo eliminaríamos:

IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trelTriggerQueBorraremos')  DROP TRIGGER trelTriggerQueBorraremos

De SQL-Server 2016 en adelante:

DROP TRIGGER IF EXISTS trelTriggerQueBorraremos

Borrar una tabla solo si esta existe, en SQL-Server

El caso que vamos a tratar es el siguiente. Estamos creando un script para SQL-Server y queremos que si existe una tabla esta se borre. ¿Cómo lo hacemos? Dependiendo de la versión de nuestro SGBD lo haremos de una forma u otra:

SQL-Server anterior a la versión 2016:

Si trabajamos con una versión de SQL-Server anterior a 2016 (yo normalmente trabajo con SQL-Server 2008 R2 en la mayoría de los clientes de mi empresa) la sintaxis más correcta sería la siguiente:

IF OBJECT_ID('dbo.TablaQueQueremosBorrar', 'U') IS NOT NULL 
  DROP TABLE dbo.TablaQueQueremosBorrar; 

Como puedes ver, comprobamos si existe el nombre de la tabla a borrar y si la consulta devuelve algo distinto de NULL ejecutamos el borrado.

De SQL-Server 2016 en adelante:

Para SQL-Server 2016 se simplificó este paso con una nueva instrucción, que podríamos resumir como DIE (Drop If Exists)

DROP TABLE IF EXISTS dbo.TablaQueQueremosBorrar


Calcular la mediana en SQL-Server

Como no me apetece meterme en una discusión infructuosa con un individuo *troll ultraderechista sexualmente frustrado* voy a aprovechar la hora de comer en el trabajo para comentaros cómo podemos calcular la mediana de una serie de números en SQL-Server. Ya vimos no hace mucho el significado de este término y también cómo podemos calcularlo en Libre Office Calc.

Si vamos a trabajar con SQL-Server 2012 o superior la función PERCENTILE_DISC() nos servirá, sin tener que hacer nada más. Es una función que calcula un percentil concreto para una serie de valores ordenados de un conjunto de filas, y su sintaxis es:

PERCENTILE_DISC ( número ) WITHIN GROUP ( ORDER BY exp_ordenación [ ASC | DESC ] )
OVER ( [ partido_por ] )

Aquí os copio sus argumentos de la web de documentación de Microsoft directamente, donde también tenéis ejemplos de código para SQL-Server y para Azure:

  • número :El percentil que se va a calcular. El valor debe estar entre 0,0 y 1,0.
  • WITHIN GROUP ( ORDER BY exp_ordenación[ ASC | DESC ]):Especifica una lista de valores para ordenar y cuyo percentil se va a calcular. Solo se permite una exp_ordenación. El criterio de ordenación predeterminado es ascendente. La lista de valores puede ser de cualquiera de los tipos de datos válidos para la operación de ordenación.
  • OVER ( partido_por ):Divide el conjunto de resultados generado por la cláusula FROM en particiones a las que se aplica la función de percentil. Para más información, vea Cláusula OVER (Transact-SQL). Las cláusulas y no se pueden especificar en una función PERCENTILE_DISC.

De esta forma PERCENTILE_DISC(0.5) calculará la mediana del cojunto de filas que estamos analizando.

Pero ¿qué pasa si trabajamos con una versión anterior a SQL-Server 2012? Te parecerá algo prehistórico, pero en muchos sitios sigue funcionando SQL-Server 2008 o 2005. ¿Cómo lo hago ahí donde no dispongo de la función PERCENTILE_DISC()?

Bueno, ahí he hecho una solución un poco picapedrera, porque según el conjunto de datos que tengamos las hay bastante mejores. Esta realmente consume muchos recursos, pero por otra parte es universal. Si la tabla cuenta con una columna de Identidad la cosa puede ser más eficiente:

Básicamente he usado una tabla llamada Actividades, donde hay una columna numérica llamada Importe. Para calcular la mediana de este importe vamos a sacar los dos valores centrales dividiendo las filas en dos mitades, ordenando una de forma descendente y otra ascendente. Tras eso los sumamos y hacemos la media de la suma.

Select ((
    Select Top 1 Importe
    From   (
                    Select  Top 50 Percent Importe
                    From    Actividades
                    Where   Importe Is NOT NULL
                    Order By Importe
                    ) As A
    Order By Importe DESC) + 
    (
    Select Top 1 Importe
    From   (
                    Select  Top 50 Percent Importe
                    From    Actividades
                    Where   Importe Is NOT NULL
                    Order By Importe DESC
                    ) As A
    Order By Importe Asc)) / 2 as MedianaImportes