SET ANSI_NULLS (Transact-SQL)

Artículo
06/18/2013

Especifica el comportamiento conforme a ISO de los operadores de comparación Es igual a (=) y No es igual a (<>) cuando se usan con valores NULL en SQL Server 2012.

Importante
En una versión futura de SQL Server, ANSI_NULLS siempre se establecerá en ON y cualquier aplicación que establezca de forma explícita la opción en OFF generará un error. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

SET ANSI_NULLS { ON | OFF }

Comentarios

Cuando SET ANSI_NULLS se establece en ON, una instrucción SELECT que utilice WHERE column_name = NULL devuelve cero filas incluso si hay valores NULL en column_name. Una instrucción SELECT que utilice WHERE column_name <> NULL devuelve cero filas incluso si hay valores no NULL en column_name.

Cuando SET ANSI_NULLS se establece en OFF, los operadores de comparación Es igual a (=) y No es igual a (<>) no siguen el estándar ISO. Una instrucción SELECT que utilice WHERE column_name = NULL devuelve las filas que tienen valores NULL en column_name. Una instrucción SELECT que utilice WHERE column_name <> NULL devuelve las filas que tienen valores no NULL en la columna. Además, una instrucción SELECT que utilice WHERE column_name <> XYZ_value devuelve todas las filas que no son XYZ_value y que no son NULL.

Cuando SET 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 acerca de la opción de base de datos ANSI_NULLS, vea ALTER DATABASE (Transact-SQL).

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.

SET ANSI_NULLS debe ser ON para ejecutar consultas distribuidas.

SET ANSI_NULLS también debe ser ON al crear o cambiar índices en columnas calculadas o vistas indizadas. 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 devolverá un error que muestra todas las opciones SET que infringen los valores requeridos. Además, al ejecutar una instrucción SELECT, si SET ANSI_NULLS es OFF, SQL Server omitirá los valores de índice en columnas calculadas o vistas y resolverá 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 se debe establecer 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 SET ANSI_DEFAULTS es ON, se habilita SET ANSI_NULLS.

La opción SET ANSI_NULLS se establece en tiempo de ejecución, no en tiempo de análisis.

Permisos

Requiere la pertenencia al rol public.

Ejemplos

En el ejemplo siguiente se utilizan los operadores de comparación Es igual a (=) y No es igual a (<>) para realizar comparaciones con valores NULL y no NULL en una tabla. En este ejemplo también se muestra que IS NULL no se ve afectado por el valor de 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

-- SET ANSI_NULLS to ON and test.
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

-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET 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;