Constraints vs Query Performance

Neste post irei demonstrar a importancia de  criar constraints no SQL Server. Graças as Foreign keys e check constraints o optimizer pode criar planos mais eficientes para as querys. Dado o script abaixo da criação das tabelas temos a tabela de Customers e de Orders. Na modelagem proposta que dizer que um Customer pode ter uma ou mais Orders. Notem que o script de criacao da constraint fisica nessa tabela e feita na tabela “filha” no nosso caso a de Orders.
CREATE TABLEdbo.Customers (CustomerID INT  PRIMARYKEY)
CREATE TABLE dbo.Orders(OrderID INT  PRIMARYKEY,CustomerID INT NOT  NULL CONSTRAINT FKOrdersCustomers REFERENCES dbo.Customers(CustomerID)).
Executando a query abaixo, podemos notar que temos duas tabelas na consulta, mas se vc executar a query com o plano de execução o sql server uma apenas o operador fisico para acessar uma das tabelas. Esse compportamento acontece, pois, o otimizador sabe que não é necessario executar um teste de existencia dos registros, pois a FK garante que serao requeridas todas orders para referenciar com o customer. Conforme print abaixo:
SELECT O.OrderID,o.CustomerID
FROM dbo.Orders AS o
WHERE EXISTS(SELECT CustomerID FROM dbo.Customers AS c
WHERE c.CustomerID=o.CustomerID)

Agora o que aconteceria se desabilitarmos a constraint? Vamos aos testes (Amo muito tudo isso..rs)
ALTER TABLE dbo.Orders NOCHECK CONSTRAINT FKOrdersCustomers

Execute novamente  a query, pressione o CTRL+M antes para habilitar o Include Actual Execution Plan. Agora perceba que  o plano de execucao mudou, isso porque a constraint foi desabilitada e o SQL Server nao pode garantir que todas as Orders  tem um customer valido.
SELECT O.OrderID,o.CustomerID
FROM dbo.Orders AS o
WHERE EXISTS(SELECT CustomerID FROM dbo.Customers AS c WHERE c.CustomerID=o.CustomerID)

Para voltarmos ao plano anterior devemos habilitar novamente a constraint, conforme instrução abaixo:
ALTER TABLE dbo.Orders CHECK CONSTRAINT FKOrdersCustomers

Execute a query novamente e veja o plano de execucao. Note que o plano de execucao continua o mesmo de quando a constraint foi desabilitada.

Esse comportamento se da ao devido fato que: O SQL server nao pode garantir que nao foi inserido um registro nao valido enquanto a FK estava desabilitada. Vc pode verificar isso atraves da dmv sys.foreign_keys. A FK foi marcada como “nao confiavel“. Veja a query abaixo:
select name , is_not_trusted from sys.foreign_keys
Com o resultado da query acima  vc vera que a coluna is_not_trusted esta marcada com o valor 1  , indica que a FK nao e confiavel.
O que pode ser feito nesse caso e adicionar a opção WITH CHECK para a clausula alter table que habilita a constraint:

alter table dbo.Orders with check check constraint FKOrdersCustomers

Se a query for executada novamente vera que o plano de execucao sera o visto da primeira implementacao.

Se nesse meio tempo que a FK foi  desabilitada algum registro que nao condiz com a  implementacao da constraint fosse inserido . Uma mensagem de erro seria retornado para o SQL Server. Por exemplo se uma order fosse inserida com um CustomerID NULL.

Referencia: Microsoft Database Developer 2008 TK 70-433