Función Isnull - Su innecesario y perjudicial uso en sobre campos numéricos -

 

Estimados amigos, el placer de saludarlos nuevamente...

   Trabajando en tunning  sobre unos scripts y encontrando una vez más un pequeño pero perjudicial y repetido  error,  aprovecho para compartirles como trato de hacer asiduamente la experiencia,  con el objetivo de  mejorar la performance de nuestros códigos en general.

Ante todo supongo que todos estamos de acuerdo con el que el uso de funciones en campos donde es muy importante el uso de un índice invalida totalmente la utilización por parte del motor de base de datos del mismo.

 

Ejemplo:

Tenemos una tabla “CONSUMO”  de la cual tenemos que listar los campos “Id”  “FechaPresentacion” cuando la fecha sea mayor al '2020-05-20 23:59:59'

Si existe  un índice que cubra el campo  “FechaPresentación” tendremos una excelente performance a partir de la plena utilización del índice:

  Si, por el contrario, aplicamos algún tipo de función sobre el campo indexado “FechaPresentacion” vamos a dejar sin chances de ser utlizado al índice veamos:

 

Habiendo repasado lo perjudicial que son el uso de funciones en determinados campos, (sobre todo de filtro y ordenamiento, vayamos al uso de la función ISNULL

 

Es muy común ver en queries el uso de esta función ante la necesidad de validar un campo numérico que puede contener valores null

 

Ejemplo:

 

Dada la misma tabla CONSUMO, debemos listar aquellos “id” cuyo consumo,  (campo “importe” de tipo Integer),  es mayor a 5000 pesos.  (hay registros  que pueden tener a la fecha importes con valor NULL)

 

Así  es común ver este tipo de queries:

 

El uso de la función Isnull sobre el campo “Importe” inutiliza el índice sobre dicho campo…

 

Pero .. es necesario el uso de la función Isnull para evitar errores de comparación de campos numéricos con posibles valores nulos? Evitamos de ese modo un posible error?

La respuesta es “no”.   

Cualquier campo numérico null será tomado por el sql server como “0” no siendo necesario utilizar la función Isnull y así inutilizar el eventual índice dispuesto sobre dicho campo.

Utilizaremos el idx y el resultado será el mismo.

Bueno gente me despido esperando que haya sido de utlilidad el tip.

Problemas con "Huecos" o "Gaps" en Campos Identity - Posibles Motivos y Soluciones

Estimados colegas, el placer de saludarlos nuevamente.

  Cuántas veces nos hemos encontrado con el problema de "saltos" o "gaps" en el valor numérico de columnas definidas como "identity" en tablas de nuestras bases de datos?.

  Seguramente en más de una oportunidad hemos sido alertados por el equipo de desarrollo o por alertas propias sobre esta incidencia que le quita secuencialidad a nuestras tablas.

  Sobre este inconveniente trata el post del día de hoy. Asumo que todos sabemos lo que un campo "identity" signfica en una tabla y su funcionalidad y por que no, que también fue utilizado en una o más tablas de vuestros sistemas con la función de contar con un valor Unico y Secuencial, delegando estas característiscas en el motor de base de datos.

   Es así entonces que surje el primer punto a tener en cuenta y es el siguiente:

  "Un campo definido como identity NO GARANTIZA LA SECUENCIALIDAD de los valores del mismo"

 Así es colegas, tal como lo leen. Esto está expresado explícitamente por Microsoft en su documentación oficial. 

Si uds. requieren   por regla de negocios, generar secuencialidad. Por ejemplo si el campo identity sería el punto de partida de la numeración de la facturación de una empresa, deben recurrir a otros métodos alternativos de generación de Ids tales como Sequence no cacheado, (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15),  u otros generados a través de programación (eg guardar un id en una tabla isolada y sumarle 1 ante cada inserción).

  Pero por qué no podemos confiar la secuencialidad en un campo identity?

  Sencillamente, y aquí viene el primer motivo de un posible "gap" en una numeración identity, porque ante un rollback, los ids rollbackeados "se perderán" y pasarán a formar parte de un hueco de ids ya no disponibles para futuras inserciones..   Así pues, si las inserciones de dos ids cualquiera, digamos el  23 y 24,  se rollbackea, dichos ids estarán faltantes en nuestra numeración de facturas.

 Ok, pero uds. tal vez llegaron a este post buscando un motivo por el cual registraron un gap en la numeración identity y están seguros de que no se debió a un rollback....  Entonces  surge la pregunta...

 Hay otro motivo por el cual se pueden registrar gaps en una columna Identity?

 La respuesta es SI

 Si estás trabajando con un motor 2012 o superior y no has modificado el comportamiento por default del cacheo de identities en SQL Server, pueden ocurrir gaps muy grandes y no tan mensurables como el de un rollback de "x registros", ante estas situaciones:

• Caídas en General del Servicio de Motor de Base de Datos no Planificadas
• Failovers

 Efectivamente, y sin entrar en detalles técnicos, la gente de Microsoft con el noble fin de ganar performance comenzó, a partir de SQL 2012, a "cachear" los Identity como comportamiento por default de motor. 

Ventajas? ... supuestamente algunas de performance...(no comprobadas por mi nunca).

Desventajas?  las que devienen cacheados estos valores "core" en muchos sistemas de bases de datos cuyas lógicas de negocio dependen en muchos cosos de los mismos.

Cacheados = sin persistencia ante los eventos mencionados = Pérdida de correlatividad y Gaps a veces muy significativos.

En mi opinión esta fue una muy mala jugada de Microsoft quien no avisó con la debida firmeza sobre un cambio que complicó y sigue complicando la vida de muchos de quienes trabajan MSqlSrvr. Aún cuando se puede discutir sobre la mala praxys que implica el confiar la correlatividad y otros puntos importantes que hacen a la regla de negocios sobre este tipo de campos.

Pero.. hay modo de evitar este comportamiento que por defecto tiene el Sql Sever para que deje de cachear los Ids?

 Si lo hay veamos las opciones:

• Modificando el Identiy Cache a nivel Instancia de Motor de Base de Datos 

   Es es la única opción disponible si trabajás con las versiones 2012/2014 y 2016 

 

   Se trata de agregar el "TRACE flag 272" como parámetro de star-up 

   Los pasos son:

   1)  Ingresar al SQL Server Configuration Manager y editar properties

  2)  Agregar, (ver botón add), el flag "-T272" en la solapa "startup parameters" y aplicar cambios.

  

3) Restartear el servicio y para aplicar los cambios

 

• Modificando el Identiy a Nivel Base de Datos

     

 Esta opción está disponible a partir del SQL Server 2017 y es mi favorita puesto que pemite ser selectivo con la Base de Datos en la cual se desea implementar el setting y adicionalmente no requiere  del reinicio del motor una vez implementado el cambio.

   La sintaxys se debe aplicar en cada base de datos y es la siguiente:

    Use database;

  go

  ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

Amigos, esto es todo por ahora. Espero que el post les haya sido de utilidad y les prometo para un post adicional próximo una serie de scripts que les faciilitará el trabajo cuando de trabajar y controlar con tablas con campos identity se trate.

 Saludos cordiales desde Argentina, sigo esperando sus preguntas, sólo tengan paciencia y serán respondidas.