Colunas UNIQUE e NULLs

Se quiser baixar o PDF e o script que utilizei, clique aqui.

Durante o último treinamento do SQL Server 2008 Internals, tive mais uma vez o prazer de usufruir de uma das grandes vantagens de ser instrutor, que é aprender com os alunos, então compartilho com vocês.
Estávamos discutindo sobre a utilização do NULL e eu joguei na sala a pergunta: Como fazemos para manter a unicidade de uma coluna e ainda permitirmos diversos valores nulos?

(Pausa para respirar e pensar um pouquinho)

No SQL Server a unicidade dos valores em uma coluna é garantida através de índices marcados como UNIQUE (cluster ou não) e uma vez inserido um NULL, nenhum outro NULL pode ser adicionado a tabela, pois é um valor duplicado.
É interessante ver esse comportamento de igualdade de nulos em uma constraint, pois se testarmos a igualdade de um nulo através de uma consulta, veremos que NULL é diferente de NULL (ele é desconhecido).

SELECT 'Comparando'
WHERE 1 = 1

SELECT 'Comparando'
WHERE NULL = NULL
go

Então como você resolve esse problema?

- Uma abordagem seria trabalhar com triggers na tabela, garantindo a unicidade dos valores não nulos.
- Particularmente não gosto dessa abordagem, por prolongar a transação e, se necessário, efetuar um rollback da mesma.

- Poderíamos garantir a unicidade através da aplicação ou SPs, mas aí temos que garantir que ninguém vai conseguir inserir um registro "por fora".
- Essa é uma abordagem interessante por evita o rollback, mas a falta de controle e de informações para o query optimizer (como no primeiro caso) não é legal.

- Outra abordagem que mostro no treinamento, seria criarmos uma coluna computada que em combinação com a coluna original (que precisa garantir unicidade para não-nulos) deve ser única. Essa coluna computada condicionalmente recebe um valor único (o campo da PK, por exemplo) caso o campo original seja nulo ou recebe NULL caso ele não seja nulo. Dessa forma poderíamos criar uma constraint UNIQUE nas colunas original e calculada, garantindo assim a unicidade não-nula.
- Gosto dessa abordagem porque trabalhamos com constraints e não preciso confiar em terceiros para que a regra seja respeitada.

Entendeu a explicação da terceira solução? Bem, eu já li vinte vezes o que escrevi e não entendi nada, então segue um exemplo para exemplificar melhor o que eu disse. :-)

USE tempdb
go

-- Criando a tabela de teste
IF (OBJECT_ID('Funcionario') IS NOT NULL)
DROP TABLE Funcionario
go

CREATE TABLE Funcionario
(
Codigo INT IDENTITY NOT NULL,
Nome VARCHAR(200) NOT NULL,
CNPJ CHAR(14) NULL)
go

ALTER TABLE Funcionario
ADD CONSTRAINT UNQ_Funcionario_CNPJ
UNIQUE (CNPJ)
go

ALTER TABLE Funcionario
ADD CONSTRAINT PK_Funcionario
PRIMARY KEY (Codigo)
go

-- Inserts OK
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Ronaldo Fenômeno', NULL)
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Nilmar', '000.000.000-00')
go

-- Ambos os INSERTs abaixo irão trazer problema por conta da constraint UNIQUE
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Ronaldo Fenômeno 2', NULL)
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Nilmar 2', '000.000.000-00')
go

-- Reconstruindo a tabela com a coluna computada
IF (OBJECT_ID('Funcionario') IS NOT NULL)
DROP TABLE Funcionario
go

CREATE TABLE Funcionario
(
Codigo INT IDENTITY NOT NULL,
Nome VARCHAR(200) NOT NULL,
CNPJ CHAR(14) NULL,
CNPJNulo AS (CASE WHEN CNPJ IS NULL THEN Codigo ELSE -1 END)
)
go

ALTER TABLE Funcionario
ADD CONSTRAINT PK_Funcionario
PRIMARY KEY (Codigo)
go

ALTER TABLE Funcionario
ADD CONSTRAINT UNQ_Funcionario_CNPJ
UNIQUE (CNPJ, CNPJNulo)
go

-- Inserts OK
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Ronaldo Fenômeno', NULL)
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Nilmar', '000.000.000-00')
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Caio do Botafogo', '000.000.000-01')
go

-- Vai funcionar
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Ronaldo Fenômeno 2', NULL)
go

-- Não vai funcionar
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Nilmar 2', '000.000.000-00')
go

Melhorou?
Dessa forma conseguimos garantir a unicidade antes que o valor seja inserido na tabela, sem a necessidade de criação de triggers.


SQL Server 2008

Agora que vem a sacada, enquanto estava falando sobre isso o amigo Burgos me perguntou: eu não conseguiria resolver esse problema utilizando índices com filtro?

(Momento de silêncio na sala)

Caramba! Se a unicidade é garantida através de índices e eu posso criar um índice com o predicado "IS NOT NULL", então provavelmente filtered index deve resolver o problema! Testamos e bang! Na mosca.

Eu tinha ficado tão focado nos ganhos de desempenho e tamanho dos índices com filtro que nunca tinha parado para pensar nessa utilização! Nada melhor do que dar aula e aprender também = Doscendo discimus.

Vamos ao exemplo…

-- Somente para SQL Server 2008
-- Resolução com filtered indexes
IF (OBJECT_ID('Funcionario') IS NOT NULL)
DROP TABLE Funcionario
go

CREATE TABLE Funcionario
(
Codigo INT IDENTITY NOT NULL,
Nome VARCHAR(200) NOT NULL,
CNPJ CHAR(14) NULL)
go

ALTER TABLE Funcionario
ADD CONSTRAINT PK_Funcionario
PRIMARY KEY (Codigo)
go

CREATE UNIQUE NONCLUSTERED INDEX idx_CNPF
ON Funcionario (CNPJ)
WHERE CNPJ IS NOT NULL
go

-- Inserts OK
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Ronaldo Fenômeno', NULL)
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Nilmar', '000.000.000-00')
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Caio do Botafogo', '000.000.000-01')
go

-- Vai funcionar
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Ronaldo Fenômeno 2', NULL)
go

-- Não vai funcionar
INSERT INTO Funcionario (Nome, CNPJ) VALUES ('Nilmar 2', '000.000.000-00')
go

Viu que realmente funciona?!
Espero que a solução pré-SQL Server 2008 e a nova abordagem possam ajudar você no dia-a-dia.

Só fiquei agoniado com uma coisa nessa abordagem, relacionado com o Query Optimizer, mas vou fazer alguns testes e depois coloco aqui minhas considerações.

[]s
Luciano Caixeta Moreira - {Luti}
Chief Innovation Officer
Sr. Nimbus Serviços em Tecnologia Ltda - www.srnimbus.com.br
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm