En SQL Server, ¿qué significa "SET ANSI NULLS ON"?

La definición dice:

Cuando SET ANSI_NULLS está ACTIVADO, 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 no nulos en column_name.

¿Significa esto que no se incluirán nulos en esta consulta?

SELECT Region
FROM employees
WHERE Region = @region

O do ANSI_NULL s solo se refieren a consultas como esta (donde el WHERE incluye la palabra específica NULL)?

SELECT Region
FROM employees
WHERE Region = NULL
Author: Oreo, 2012-03-19
Source

7 answers

Significa que no se devolverán filas si @region es NULL, cuando se usa en su primer ejemplo, incluso si hay filas en la tabla donde Region es NULL.

Cuando ANSI_NULLS está activado (que siempre debe activarse de todos modos, ya que la opción de no tenerlo activado se eliminará en el futuro), cualquier operación de comparación donde (al menos) uno de los operandos sea NULL produce el tercer valor lógico - UNKNOWN (a diferencia de TRUE y FALSE).

UNKNOWN los valores se propagan a través de cualquier combinar operadores booleanos si no están ya decididos (por ejemplo, AND con un operando FALSE o OR con un operando TRUE) o negaciones (NOT).

La cláusula WHERE se usa para filtrar el conjunto de resultados producido por la cláusula FROM, de modo que el valor total de la cláusula WHERE debe ser TRUE para que la fila no se filtre. Por lo tanto, si se produce un UNKNOWN por cualquier comparación, hará que la fila se filtre.

@user1227804 la respuesta incluye esta cita:

Si ambos lados de la comparación son columnas o expresiones compuestas, la configuración no afecta a la comparación.

De SET ANSI_NULLS*

Sin embargo, no estoy seguro de qué punto está tratando de hacer, ya que si se comparan dos NULL columnas (por ejemplo, en un JOIN), la comparación aún falla: 

create table #T1 (
    ID int not null,
    Val1 varchar(10) null
)
insert into #T1(ID,Val1) select 1,null

create table #T2 (
    ID int not null,
    Val1 varchar(10) null
)
insert into #T2(ID,Val1) select 1,null

select * from #T1 t1 inner join #T2 t2 on t1.ID = t2.ID and t1.Val1 = t2.Val1

La consulta anterior devuelve 0 filas, mientras que: 

select * from #T1 t1 inner join #T2 t2 on t1.ID = t2.ID and (t1.Val1 = t2.Val1 or t1.Val1 is null and t2.Val1 is null)

Devuelve una fila. Así que incluso cuando ambos operandos son columnas, NULL no es igual a NULL. And the documentation for = no tiene nada que decir sobre los operandos:

Cuando se comparan dos expresiones NULL, el resultado depende de la configuración ANSI_NULLS:

Si ANSI_NULLS se establece en ON, el resultado es NULL1, según la convención ANSI, un valor NULL (o desconocido) no es igual a otro NULL o valor desconocido.

Si ANSI_NULLS se establece en OFF, el resultado de NULL en comparación con NULL es TRUE.

Comparando NULL con un valor no-NULL siempre resulta en FALSE2.

Sin Embargo, ambos 1 y 2 son incorrectos, el resultado de ambas comparaciones es UNKNOWN.

*El significado críptico de este texto fue finalmente descubierto años más tarde. Lo que realmente significa es que, para esas comparaciones, la configuración no tiene efecto y siempre actúa como si la configuración estuviera EN. Hubiera sido más claro si había declarado que SET ANSI_NULLS OFF era el entorno que no tenía ningún efecto.

 46
Author: Damien_The_Unbeliever,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:18:16

ESTABLECER QUOTED_IDENTIFIER ON / OFF

Especifica cómo SQL Server trata los datos que se definen entre Comillas Simples y Comillas dobles.

Cuando se establece en EN cualquier conjunto de caracteres que se define en las comillas dobles ""es tratado como un Identificador de T-SQL (Nombre de tabla, Nombre de Proc, Nombre de columna....etc)

Cuando cualquier conjunto de caracteres definido en las comillas simples " es tratado como un literal.

SET QUOTED_IDENTIFIER ON
CREATE TABLE "SELECT" ("TABLE" int)  -- SUCCESS
GO

SET QUOTED_IDENTIFIER ON
SELECT "sometext" AS Value   -- FAIL because “sometext” is not a literal

Cuando está ajustado a OFF cualquier conjunto de caracteres que se define en una Sola Comillas o entre Comillas Dobles es tratado como un literal.

SET QUOTED_IDENTIFIER OFF
CREATE TABLE "SELECT"(“TABLE” int) -- FAIL
GO

SET QUOTED_IDENTIFIER OFF
SELECT "sometext" AS Value    -- SUCCESS as “sometext” is treated litral

--The default behavior is ON in any database.

ACTIVAR/DESACTIVAR ANSI_NULLS:

La opción ANSI_NULLS especifica cómo SQL Server maneja las operaciones de comparación con valores NULL.

Cuando se establece en EN cualquier comparación con NULL usando = y dará como resultado false valor. Y es el comportamiento estándar definido ISO. Así que para hacer la comparación con valores NULL que necesitamos usar ES NULL y NO ES NULL.

Cuando se establece en OFF cualquier comparación con NULL usando = y funcionará como de costumbre, es decir, NULL = NULL devuelve true y 1 = NULL devuelve false.

SET ANSI_NULLS ON
IF NULL = NULL
 PRINT 'same'
ELSE
 PRINT 'different'
--result:  different

SET ANSI_NULLS ON
IF NULL IS NULL
 PRINT 'same'
ELSE
 PRINT 'different'
-- result: same
--==============================
SET ANSI_NULLS OFF
IF NULL = NULL
 PRINT 'same'
ELSE
 PRINT 'different'
--result:  same (now NULL = NULL works as 1=1)

--The default behavior is ON in any database.
 28
Author: Rae Lee,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-10-22 12:27:27

Si @Region no es un valor null (digamos @Region = 'South') no devolverá filas donde el campo Region sea null, independientemente del valor de ANSI_NULLS.

ANSI_NULLS solo hará una diferencia cuando el valor de @Region es null, es decir, cuando su primera consulta se convierte esencialmente en la segunda.

En ese caso, ANSI_NULLS ON no devolverá ninguna fila (porque null = null dará un valor booleano desconocido (también conocido como null))) y ANSI_NULLS OFF devolverá cualquier fila donde el campo Región esté null (porque null = null producirá true)

 4
Author: SWeko,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-03-19 08:12:50

ESTABLECER ANSI_NULLS EN

Devuelve todos los valores incluidos los valores nulos de la tabla

ESTABLECER ANSI_NULLS off

Termina cuando las columnas contienen valores nulos

 1
Author: Joseph Stalin,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-02 09:29:00

Si se desactiva ANSI NULL, la comparación NULL devuelve true . EG: 

        SET ANSI_NULLS OFF
        select * from sys.tables
        where principal_id = Null

Devolverá algún resultado como se muestra a continuación: zcwInvoiceDeliveryType 744547 NULL zcExpenseRptStatusTrack 2099048 NULL ZCVendorPermissions 2840564 NULL ZCWOrgLevelClientFee 4322525 NULL

Mientras que esta consulta no devolverá ningún resultado: 

        SET ANSI_NULLS ON 
        select * from sys.tables
        where principal_id = Null
 0
Author: ProblemSolver,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-09-04 12:40:36

Https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql

Cuando SET ANSI_NULLS está ACTIVADO, 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 nonnull en column_name.

Por ejemplo

DECLARE @TempVariable VARCHAR(10)
SET @TempVariable = NULL

SET ANSI_NULLS ON
SELECT 'NO ROWS IF SET ANSI_NULLS ON' where    @TempVariable = NULL
-- IF ANSI_NULLS ON , RETURNS ZERO ROWS


SET ANSI_NULLS OFF
SELECT 'THERE WILL BE A ROW IF ANSI_NULLS OFF' where    @TempVariable =NULL
-- IF ANSI_NULLS OFF , THERE WILL BE ROW !
 0
Author: Prasanth V J,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-04-06 06:48:49

Si ANSI_NULLS se establece en " ON " y si aplicamos = , en el valor de la columna NULL mientras escribimos la instrucción select, entonces no devolverá ningún resultado .

Ejemplo

Create table # tempTable(sn int, ename varchar (50))

Insertar en # tentable

Seleccione 1,'Manoj'

UNIÓN TODOS

Seleccione 2,'Pankaj'

UNIÓN TODOS

Seleccione 3, NULL

UNIÓN TODOS

Seleccione 4, 'Lokesh'

UNIÓN TODOS

Seleccione 5,'Gopal'

ESTABLECER ANSI_NULLS EN

Seleccione * de # tentable donde ename es NULO {(1 fila (s) afectada (s))

Seleccione * de # tempTable donde ename = NULL {(0 fila (s) afectada (s))

Seleccione * desde # tentable donde ename NULL NULL(0 fila (s) afectada (s))

SET ANSI_NULLS OFF

Seleccione * de # tentable donde ename es NULO {(1 fila (s) afectada (s))

Seleccione * de #tempTable donde ename = NULL ((1 row (s) afectados)

Seleccione * de # tentable donde ename no es NULO {(4 fila (s) afectada (s))

Seleccione * desde # tentable donde ename NULL NULL(4 fila (s) afectada (s))

 -2
Author: Pravat Behuria,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-12-13 19:05:39