SET ANSI_NULLS (Transact-SQL)

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Especifica el comportamiento compatible con ISO de los operadores de comparación Equals (=) y Not Equal To (<>) cuando se usan con valores NULL en SQL Server.

Nota:

SET ANSI_NULLS OFF y la opción de base de datos OFF ANSI_NULLS están en desuso. A partir de SQL Server 2017 (14.x), ANSI_NULLS siempre se establece en ACTIVADO. Las características en desuso no se deben usar en nuevas aplicaciones. Para obtener más información, vea Características de Motor de base de datos en desuso en SQL Server 2017.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis de SQL Server y un grupo de SQL sin servidor en Azure Synapse Analytics, Microsoft Fabric

SET ANSI_NULLS { ON | OFF }

Sintaxis para Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

SET ANSI_NULLS ON

Nota:

Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.

Comentarios

Cuando ANSI_NULLS es ON, una instrucción SELECT que usa WHERE column_name = NULL devuelve cero filas incluso si hay valores NULL en column_name. Una instrucción SELECT que usa WHERE column_name <> NULL devuelve cero filas incluso si hay valores que no son NULL en column_name.

Cuando ANSI_NULLS es OFF, los operadores de comparación Equals (=) y Not Equal To (<>) no siguen el estándar ISO. Una instrucción SELECT que usa WHERE column_name = NULL devuelve las filas que tienen valores NULL en column_name. Una instrucción SELECT que usa WHERE column_name <> NULL devuelve las filas que tienen valores que no son NULL en la columna. Además, una instrucción SELECT que usa WHERE column_name <> XYZ_value devuelve todas las filas que no son XYZ_value y que no son NULL.

Cuando ANSI_NULLS es ON, todas las comparaciones con un valor NULL se evalúan como UNKNOWN. Cuando SET ANSI_NULLS es OFF, la comparación de cualquier dato con un valor NULL se evalúa como TRUE si el valor del dato es NULL. Si no se especifica SET ANSI_NULLS, se aplica el valor de la opción de base de datos ANSI_NULLS. Para obtener más información sobre la opción de base de datos ANSI_NULLS, consulte ALTER DATABASE (Transact-SQL).

En la tabla siguiente se muestra cómo el valor de ANSI_NULLS afecta a los resultados de una serie de expresiones booleanas con valores NULL y no NULL.

Expresión booleana SET ANSI_NULLS ON SET ANSI_NULLS OFF
NULL = NULL DESCONOCIDO true
1 = NULL DESCONOCIDO FALSE
NULL <> NULL DESCONOCIDO FALSE
1 <> NULL DESCONOCIDO true
NULL > NULL DESCONOCIDO DESCONOCIDO
1 > NULL DESCONOCIDO DESCONOCIDO
NULL IS NULL VERDADERO TRUE
1 IS NULL false false
NULL IS NOT NULL false false
1 IS NOT NULL VERDADERO TRUE

SET ANSI_NULLS ON solo afecta a una comparación si uno de los operandos es una variable que es NULL o un NULL literal. Si ambos lados de la comparación son columnas o expresiones compuestas, la configuración no afecta a la comparación.

Para que un script funcione como se pretende, independientemente de la opción de base de datos ANSI_NULLS o de la opción SET ANSI_NULLS, use IS NULL e IS NOT NULL en las comparaciones que puedan contener valores NULL.

ANSI_NULLS se debe establecer en ON para ejecutar consultas distribuidas.

Igualmente se debe establecer en ON al crear o cambiar índices en columnas calculadas o vistas indexadas. Si SET ANSI_NULLS es OFF, las instrucciones CREATE, UPDATE, INSERT y DELETE producirán errores en tablas con índices en columnas calculadas y vistas indizadas. SQL Server devuelve un error que muestra todas las opciones SET que infringen los valores necesarios. Además, al ejecutar una instrucción SELECT, si SET ANSI_NULLS es OFF, SQL Server omite los valores de índice en vistas o columnas calculadas y resuelve la operación de selección como si no hubiera tales índices en las tablas o vistas.

Nota

ANSI_NULLS es una de las siete opciones SET a las que se deben asignar unos valores requeridos para tratar índices en columnas calculadas o vistas indizadas. Las opciones ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, y CONCAT_NULL_YIELDS_NULL también se deben establecer en ON, y NUMERIC_ROUNDABORT en OFF.

El controlador ODBC de SQL Server Native Client y el proveedor OLE DB de SQL Server Native Client para SQL Server establecen automáticamente ANSI_NULLS en ON al conectarse. Esta opción se puede configurar en los orígenes de datos ODBC, en los atributos de conexión ODBC o en las propiedades de conexión OLE DB establecidas en la aplicación antes de conectarse a una instancia de SQL Server. El valor predeterminado de SET ANSI_NULLS es OFF.

Cuando ANSI_DEFAULTS es ON, se habilita ANSI_WARNINGS.

El valor de ANSI_NULLS se define en tiempo de ejecución, no en tiempo de análisis.

Para ver la configuración actual de este valor, ejecute la siguiente consulta:

DECLARE @ANSI_NULLS VARCHAR(3) = 'OFF';  
IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = 'ON';  
SELECT @ANSI_NULLS AS ANSI_NULLS;

Permisos

Debe pertenecer al rol public .

Ejemplos

En el ejemplo siguiente se usan los operadores de comparación Es igual a (=) y No es igual a (<>) para realizar comparaciones con valores NULL y distintos de NULL en una tabla. En este ejemplo también se muestra que IS NULL no se ve afectado por el valor SET ANSI_NULLS.

-- Create table t1 and insert values.  
CREATE TABLE dbo.t1 (a INT NULL);  
INSERT INTO dbo.t1 values (NULL),(0),(1);  
GO  
  
-- Print message and perform SELECT statements.  
PRINT 'Testing default setting';  
DECLARE @varname int;   
SET @varname = NULL;  
  
SELECT a  
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO

Ahora, establezca ANSI_NULLS en ON y pruebe.

PRINT 'Testing ANSI_NULLS ON';  
SET ANSI_NULLS ON;  
GO  
DECLARE @varname int;  
SET @varname = NULL  
  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO

Ahora, establezca ANSI_NULLS en OFF y pruebe.

PRINT 'Testing ANSI_NULLS OFF';  
SET ANSI_NULLS OFF;  
GO  
DECLARE @varname int;  
SET @varname = NULL;  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO  
  
-- Drop table t1.  
DROP TABLE dbo.t1;

Contenido relacionado