En SQL Server, ¿qué significa “SET ANSI_NULLS ON”?

Solución:

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 activar 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 (Opuesto a TRUE y FALSE).

UNKNOWN los valores se propagan a través de cualquier operador booleano de combinación si aún no están decididos (p. ej. AND con un FALSE operando o OR con un TRUE operando) o negaciones (NOT).

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

---

La respuesta de @ user1227804 incluye esta cita:

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

de SET ANSI_NULLS^*

Sin embargo, no estoy seguro de qué punto está tratando de hacer, ya que si dos NULL las columnas se comparan (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. Entonces, incluso cuando ambos operandos son columnas, NULL no es igual NULL. Y la documentación para = no tiene nada que decir sobre los operandos:

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

Si ANSI_NULLS se establece en ON, el resultado es NULL¹, siguiendo la convención ANSI de que un NULL (o desconocido) el valor 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 a un noNULL el valor siempre resulta en FALSE².

Sin embargo, ambos ¹ y ² son incorrectos – el resultado de ambas comparaciones es UNKNOWN.

---

^*El significado críptico de este texto fue finalmente descubierto años después. Lo que realmente significa es que, para esas comparaciones, la configuración no tiene ningún efecto y siempre actúa como si el ajuste estuviera ENCENDIDO. Habría sido más claro si hubiera dicho que SET ANSI_NULLS OFF fue el escenario que no tuvo ningún efecto.

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

Ejemplo

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

insert into #tempTable
values (1, 'Manoj'), (2, 'Pankaj'), (3, NULL), (4, 'Lokesh'), (5, 'Gopal')

ACTIVAR ANSI_NULLS

select * from #tempTable where ename is NULL -- (1 row(s) affected)
select * from #tempTable where ename = NULL -- (0 row(s) affected)
select * from #tempTable where ename is not NULL -- (4 row(s) affected)
select * from #tempTable where ename <> NULL -- (0 row(s) affected)

APAGAR ANSI_NULLS

select * from #tempTable where ename is NULL -- (1 row(s) affected)
select * from #tempTable where ename = NULL -- (1 row(s) affected)
select * from #tempTable where ename is not NULL -- (4 row(s) affected)
select * from #tempTable where ename <> NULL -- (4 row(s) affected)

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

ANSI_NULLS solo marcará la 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 producirá un valor booleano desconocido (también conocido como null)) y ANSI_NULLS OFF devolverá cualquier fila donde el campo Región sea nulo (porque null = null rendirá true)

---

---

Utiliza Nuestro Buscador