Hola, chicos,
¡Buenas noches! Todo está bien ?

En este post comentaré un recurso utilizado por muchos desarrolladores para “resolver” rápidamente algunos problemas comunes al desarrollar consultas Transact-SQL. Esta característica está DESACTIVADA ANSI_WARNINGS.

¿Para qué sirve el comando SET ANSI_WARNINGS OFF?

La opción de control de sesión ANSI_WARNINGS cambia el comportamiento del motor de SQL Server para algunas condiciones, que son:

  • Truncamiento de cadenas (Intentando almacenar un texto de 10 caracteres en VARCHAR(9))
  • División por CERO
  • Valores NULL en funciones de agregación (MAX, SUM, COUNT, etc.)

El valor predeterminado es ON, lo que presentará el comportamiento estándar ISO para este tipo de situación, provocando que se generen mensajes de error en las dos primeras situaciones y una alerta en la última situación.

Si utiliza el comando SET ANSI_WARNINGS OFF, el motor de SQL Server reproducirá un comportamiento no estándar e intentará ejecutar el comando SELECT 10/0; en lugar de un mensaje de error, devolverá NULL y mostrará solo una alerta, lo que permitirá que sus rutinas devuelvan información y datos incorrectos.

Por esta razón, algunos desarrolladores de SQL, que desean evitar que la aplicación devuelva un error en estas situaciones, terminan optando por la forma más fácil de "resolver" errores como los enumerados anteriormente en lugar de actuar sobre el problema y resolverlo de manera efectiva. Esto suele ocurrir en las empresas por varios motivos, que destaco:

  • Falta de conocimientos técnicos para identificar y corregir el error.
  • Falta de deseo de resolver eficazmente el problema.
  • Plazos muy ajustados para entregar software que funcione sin errores

Msg 8134 Se encontró un error de división por cero

Un error común en la vida diaria de los desarrolladores de SQL, el clásico mensaje de división por cero, es una indicación de que algún valor en sus cálculos es incorrecto. Este es un error aritmético básico que provoca una excepción grave en SQL Server, lo que provoca que el comando se interrumpa y la transacción se revierta automáticamente. Este es el comportamiento predeterminado del motor SQL Server, siguiendo el estándar ISO.

El control de este comportamiento se realiza junto con el comando ARITHABORT. Cuando ANSI_WARNIGS está habilitado, ARITHABORT se habilita automáticamente, lo que hace que el motor se ejecute en su modo predeterminado (ISO).

Cuando ANSI_WARNINGS está deshabilitado, ARITHABORT se puede cambiar para cambiar el comportamiento predeterminado del motor. Si ARITHABORT está deshabilitado, cuando encuentre un error de división por cero, SQL Server ignorará el error y continuará ejecutándose normalmente, simplemente mostrando una alerta en la pestaña Mensajes.

Ejemplo:

-- Ativa o ARITHABORT automaticamente
SET ANSI_WARNINGS ON

-- Mesmo desativando manualmente, ficará ATIVADO por causa do ANSI_WARNINGS ON
SET ARITHABORT OFF

IF ((@@OPTIONS & 8) > 0) PRINT 'SET ANSI_WARNINGS is ON'

SELECT 1/0

Como vimos en el ejemplo anterior, ANSI_WARNINGS OFF permite ejecutar cálculos de división por cero de forma silenciosa, generando datos incorrectos y sin que el equipo de desarrollo lo sepa, ya que no se generará ninguna excepción en la aplicación.

Regrese con SET ANSI_WARNINGS ON (predeterminado):

Regrese con SET ANSI_WARNINGS OFF:

Msg 8152 La cadena o los datos binarios se truncarían

Este mensaje de error es muy común entre los desarrolladores de SQL y seguramente lo has visto al crear tus consultas, realizar integraciones entre sistemas, etc. Este mensaje aparece cuando intentas almacenar más caracteres de los permitidos en una columna.

El comportamiento predeterminado del motor SQL Server, siguiendo el estándar ANSI, hace que se genere una excepción al ejecutar su código Transact-SQL si
tenga un texto de 10 caracteres e intente insertar este texto en una columna que solo permita hasta 9 caracteres.

Cuando utiliza el comando SET ANSI_WARNINGS OFF, evita que el motor de SQL Server genere este error durante la ejecución, lo que provoca que el texto de 10 caracteres se trunque y se almacene en la columna de 9 caracteres. Los personajes sobrantes se descartarán silenciosamente., ignorando y enmascarando un problema al registrar datos en su sistema, sin que nadie lo sepa.

Ejemplo:

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

IF ((@@OPTIONS & 8) > 0) PRINT 'SET ANSI_WARNINGS is ON'

IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste
CREATE TABLE #Teste ( Nome VARCHAR(10) )

INSERT INTO #Teste
VALUES ('Dirceu Resende') -- 14 caracteres

SELECT * FROM #Teste

Regrese con SET ANSI_WARNINGS ON (predeterminado):

Regrese con SET ANSI_WARNINGS OFF:

El valor nulo se elimina mediante una operación agregada u otra operación SET

Mensaje de alerta que ocurre cuando se aplica una función de agregación (MAX, SUM, COUNT, AVG, etc.) a un conjunto de datos y al menos 1 registro tiene un valor nulo (NULL).

Ejemplo:

SET NOCOUNT ON
SET ANSI_WARNINGS ON

IF ((@@OPTIONS & 8) > 0) PRINT 'SET ANSI_WARNINGS is ON'
SELECT MAX(Nota)
FROM (
    SELECT 7 AS Nota
    UNION SELECT 6.5
    UNION SELECT 5.9
    UNION SELECT 4.2
    UNION SELECT NULL
    UNION SELECT 8.7
) AS Aluno

Regrese con SET ANSI_WARNINGS ON (predeterminado):

Regresar con SET ANSI_WARNINGS OFF

Ten en cuenta que en este caso, si la opción ANSI_WARNINGS está deshabilitada, puedes estar ignorando la existencia de valores nulos en tu conjunto de resultados, lo que puede indicar un posible problema, ya que en ciertas situaciones, esto puede representar un error en tu consulta que estás enmascarando.

Si quieres saber cómo eliminar correctamente el aviso “El valor nulo se elimina mediante un agregado u otra operación SET” descubre más accediendo al post SQL Server: Advertencia: el valor nulo se elimina mediante una operación agregada u otra operación SET.

INSERT falló porque las siguientes opciones SET tienen configuraciones incorrectas: 'ANSI_WARNINGS'

Otro efecto secundario grave del uso de ANSI_WARNINGS OFF es el impacto que tiene este comando en las vistas indexadas y los índices creados sobre columnas calculadas, que muchas veces están diseñados para ganar rendimiento, pero la simple creación de un índice con la intención de mejorar termina creando un gran dolor de cabeza para el DBA, ya que todas las rutinas que insertan datos en tablas que tienen columnas calculadas fallarán después de crear el índice sobre la columna calculada.

A diferencia de los errores anteriores, que pueden causar datos incorrectos e inconsistentes, este ejemplo impide que las rutinas funcionen en general, lo que ya me pasó a mí, ya que creé un índice para optimizar una consulta lenta en producción y comenzó a disparar alertas de error en uno de los módulos del sistema donde trabajo.

Ejemplo:

SET ANSI_WARNINGS ON
IF ((@@OPTIONS & 8) > 0) PRINT 'SET ANSI_WARNINGS is ON'

IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste
CREATE TABLE #Teste (
    Nota1 NUMERIC(5, 2) NOT NULL, 
    Nota2 NUMERIC(5, 2) NOT NULL, 
    Media AS ((Nota1 + Nota2) / 2)
)

CREATE NONCLUSTERED INDEX SK01_Teste ON #Teste(Media)

INSERT INTO #Teste
VALUES (7.6, 8.9)

SELECT * FROM #Teste

Regrese con SET ANSI_WARNINGS ON (predeterminado):

Regrese con SET ANSI_WARNINGS OFF:

Mensaje 1934, Nivel 16, Estado 1, Línea 12
CREATE INDEX falló porque las siguientes opciones SET tienen configuraciones incorrectas: 'ANSI_WARNINGS'. Verifique que las opciones SET sean correctas para su uso con vistas indexadas y/o índices en columnas calculadas y/o índices filtrados y/o notificaciones de consulta y/o métodos de tipo de datos XML y/u operaciones de índice espacial.

¡Eso es todo, amigos!
Espero que hayas disfrutado del post y hasta la próxima.