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
   WHEN 'Precio' THEN Precio
   WHEN 'Pendiente' THEN ImportePagado
   WHEN '   
   ELSE FechaCrea 
 END;  

Anuncios

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

SQL-Server: Reiniciar el valor de una columna de identidad

Imaginemos que hemos borrado todos los registros de una tabla, en SQL-Server, que tenía definida una columna de identidad como clave primaria. Ahora queremos que las nuevas inserciones no comiencen desde el último Id borrado sino desde el principio ¿Cómo lo hacemos? Es muy simple:

DBCC CHECKIDENT ('NuestraTabla', RESEED, 1);

Vale, pero ¿y si no hemos borrado todos los valores sino, por ejemplo, solo un 20% de ellos? ¿Cómo hacemos para que empiece desde el valor máximo? Pues con este método lo haríamos:

DBCC CHECKIDENT ('NuestraTabla', RESEED, 1);
DBCC CHECKIDENT ('NuestraTabla', RESEED);

De esta forma le ponemos el valor a 1 y luego el segundo comando pondrá automáticamente el valor máximo de la tabla.

¿Y si hemos metido manualmente un valor en la Id, desactivando el chequeo de identidad para la inserción, mayor que el que tocaba y queremos que siga desde ahí?

DBCC CHECKIDENT ('NuestraTabla', RESEED);

Pues de nuevo invocamos la función sin ningún valor definido.

SQL-Server: Usar Try…Catch en una transacción.

Desde SQL-Server 2008 es posible utilizar la instrucción Try…Catch. Estas instrucciones nos permiten implementar un mecanismo de control de errores: metemos un bloque de código dentro de la instrucción Try, para intentar ejecutarlo, y dentro de Catch metemos las instrucciones para controlar la respuesta en caso de error.

La sintaxis básica de un Try…Catch en SQL-Server es la siguiente:

BEGIN TRY  
     ---Bloque de código 
END TRY  
BEGIN CATCH  
     --Código en caso de error. 
END CATCH  

Cuando realizamos una transacción podemos exprimir al 100% la instrucción Try…Catch. La idea es la siguiente: Comenzamos la transacción, intentamos una acción en un bloque Try. En caso de que falle mostramos el error y ejecutamos un rollback para anular la transacción. En caso de éxito confirmamos la ejecución de la transacción.

Veamos un código de ejemplo:

BEGIN TRANSACTION;  --Comienza

BEGIN TRY  --Aquí empieza el try
    Insert into Ejemplo(Id,Nombre) values(22,'Manuel');
    Delete From Espera Where Nombre = 'Manuel' 
--Intentaremos esas dos acciones
END TRY  
BEGIN CATCH  
--El primer paso en el Catch
--Será recoger y mostrar
--Todos los errores
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
--Si hay transacción abierta
--Hacemos un rollback sobre ella
--Para anularla
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;  
--Fuera del bloque vamos a
--comprobar que haya transacción abierta.
--Si la hay es que no tuvo que ir por el CATCH
--Por tanto confirmamos.
IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO