1. O que é?
Uma configuração do banco de
dados que diz se as comparações com valores nulos estarão (ON) ou não (OFF) de
acordo com o padrão ISO. A recomendação por parte da Microsoft é que esta opção
sempre esteja como ON, pois em versões futuras do SQL Server não existirá a
possibilidade de desabilitar conforme mencionado no link (http://msdn.microsoft.com/pt-br/library/ms188048.aspx).
2. Como faço para saber como está esta opção
em minha janela de conexão?
DECLARE
@ANSI_NULLS VARCHAR(3) = 'OFF';
IF
( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = 'ON';
SELECT
@ANSI_NULLS AS ANSI_NULLS;
3. Como altero esta configuração em meu
banco de dados?
-Para
alterar apenas de uma determinada sessão\conexão, utilize:
SET ANSI_NULLS
ON\OFF
-Para
alterar a configuração padrão do banco de dados, utilize:
ALTER
DATABASE [database]
SET ANSI_NULLS ON\OFF
-Objetos de banco de dados já
criados, como por exemplo stored procedures, serão executadas conforme foi
configurado em sua criação. Para consultar o padrão da criação de um
determinado objeto utilize:
select
a.* --coluna uses_ansi_nulls
from sys.sql_modules a
join sys.sysobjects b
on a.object_id = b.id
where b.name like '%NOME%'
4. Como funciona? Quais as diferenças?
Quando ANSI NULLS estiver ON,
comparações com = NULL e <> NULL nunca retornarão resultados, mesmo que
exista valores iguais a nulo e mesmo que existam valores diferentes de nulo.
Se utilizado IS NULL e IS NOT
NULL as coisas mudam. IS NULL e IS NOT NULL não são afetados pela configuração
SET ANSI NULLS ON\OFF. Neste caso serão retornadas linhas caso exista valor
nulo ou caso exista valor diferente de nulo.
Exemplo:
if OBJECT_ID('dbo.t1') IS NOT NULL
DROP
TABLE dbo.t1
GO
if OBJECT_ID('dbo.t2') IS NOT NULL
DROP TABLE
dbo.t2
GO
CREATE TABLE
dbo.t1 (a INT NULL);
CREATE TABLE
dbo.t2 (a INT NULL);
INSERT INTO dbo.t1 values (NULL);
INSERT INTO dbo.t1 values (0);
INSERT INTO dbo.t1 values (1);
INSERT INTO dbo.t2 values (NULL);
INSERT INTO dbo.t2 values (0);
GO
DECLARE @varname int;
SET @varname = NULL;
SET ANSI_NULLS ON
SELECT a FROM
dbo.t1 WHERE a = @varname;
-- 0(zero) linhas
SELECT a FROM
dbo.t1 WHERE
a <> @varname;
-- 0(zero) linhas
SELECT a FROM
dbo.t1 WHERE a IN (SELECT a FROM dbo.t2) --
1(uma)linha
SELECT a FROM
dbo.t1 WHERE a NOT IN (SELECT a FROM dbo.t2) -- 0(zero)linha
SELECT a FROM
dbo.t1 WHERE a IS NULL;
-- 1(uma) linha
SELECT a FROM
dbo.t1 WHERE a IS NOT NULL; -- 2(duas)
linhas
Quando ANSI NULLS estiver
OFF, comparações com = NULL e <> NULL retornaram linhas caso exista valor
NULL ou caso exista valor diferente de NULL. Repito que IS NULL e IS NOT NULL
não são afetados pela configuração SET ANSI NULLS ON\OFF.
Exemplo:
if OBJECT_ID('dbo.t1') IS NOT NULL
DROP TABLE
dbo.t1
GO
if OBJECT_ID('dbo.t2') IS NOT NULL
DROP TABLE
dbo.t2
GO
CREATE TABLE
dbo.t1 (a INT NULL);
CREATE TABLE
dbo.t2 (a INT NULL);
INSERT INTO dbo.t1 values (NULL);
INSERT INTO dbo.t1 values (0);
INSERT INTO dbo.t1 values (1);
INSERT INTO dbo.t2 values (NULL);
INSERT INTO dbo.t2 values (0);
GO
DECLARE @varname int;
SET @varname = NULL;
SET ANSI_NULLS OFF
SELECT a FROM
dbo.t1 WHERE a = @varname;
-- 1(uma) linhas
SELECT a FROM
dbo.t1 WHERE
a <> @varname;
-- 2(duas) linhas
SELECT a FROM
dbo.t1 WHERE a IN (SELECT a FROM dbo.t2) --
2(duas)linhas
SELECT a FROM
dbo.t1 WHERE a NOT IN (SELECT a FROM dbo.t2) -- 1(uma)linha
SELECT a FROM
dbo.t1 WHERE a IS NULL;
-- 1(uma) linha
SELECT a FROM
dbo.t1 WHERE a IS NOT NULL; -- 2(duas)
linhas
Ou seja, pra garantir, sempre use IS NULL \ IS NOT
NULL ;)
