Archive

Posts Tagged ‘Constraints’

Removendo Cursor – Dica Rápida

Ola Galera, blz? Como o tempo está um pouco corrido essa vai ser uma dica rápida que pode ser usada no dia-a-dia.
Imaginem o cenário que é necessário criar uma string com diversos ID’s. E esses ID’s estão dentro de uma tabela. A idéia inicial seria realizar um loop para incrementar uma variavel e montar a nossa string.. Vamos ao exemplo:
O script abaixo cria uma tabela com os códigos do cliente que iremos usar para montar a nossa string.
use tempdb
go
if OBJECT_ID(‘dbo.tb_cliente’) is not null
drop table dbo.tb_cliente
go
create table dbo.tb_cliente(id int identity, codigoCliente int , nome varchar(50))
go
–Bloco que popula a tabela com informações dos clientes
set nocount on
declare @i int = 1
while @i <= 100
begin
insert into dbo.tb_cliente(codigoCliente,nome)values(@i,replicate(‘Thiago’,2))
set @i = @i + 1 *(2)
end
go

Declaramos as variaveis @count, @total para realizar o nosso loop. As variáveis @idCliente e @idClienteString serão usadas para armazenar os codigos concatenados. A tabela temporaria #CodigosCliente é usada para guardar as informações pertinentes ao predicado IN. O script abaixo preenche a tabela temporária com os registros que serão montados para o loop. Após a inserção, o total de registros e colocado em uma variável para que iniciemos a montagem da string.
declare @count int , @total int
declare @idCliente varchar(max), @idClienteString varchar(max)
create table #CodigosCliente(id int identity, codigoCliente int)
–Popula a temporaria com as informacoes que sera colocadas em uma string
insert into #CodigosCliente(codigoCliente)
select codigoCliente
from dbo.tb_cliente where codigoCliente between 10 and 50

set @idCliente = ‘ ‘
set @idClienteString = ‘ ‘
select  @total =count(id) from #CodigosCliente
set @count = 1
if @total > 0
begin
set @idClienteString = ‘codigoCliente IN ( ‘
end
else
begin
set @idClienteString = ‘codigoCliente IN (0 ‘
end
while @count <= @total
begin
if  @count > 1
begin
set @idClienteString = @idClienteString + ‘, ‘
end
select @idCliente=codigoCliente from #CodigosCliente where id=@count
set @idClienteString = @idClienteString + @idCliente
set @count = @count + 1
end
set @idClienteString = @idClienteString + ‘ )’
print @idClienteString
go
Após a execução do script acima, o SQL Server irá montar o predicado in com os valores, conforme imagem:

Conforme imagem acima, temos o resultado esperado, porém, operações linha-a-linha são mais custosas para o SQL Server. Nós poderíamos obter o mesmo resultado com o script abaixo:

declare @idCliente varchar(max )
declare @idClienteString varchar(max)
SET @idCliente =
SET @idClienteString =
SET @idClienteString = ‘codigoCliente IN (‘
SELECT @idCliente=@idCliente +‘,’+CONVERT(varchar(max),codigoCliente)from#CodigosCliente
SELECT @idCliente =substring(@idCliente,2,LEN(@idCliente))
SET @idClienteString = @idClienteString + @idCliente +‘)’
PRINT @idClienteString
Como podemos ver, temos o mesmo resultado com uma menor quantidade de linhas uma instrução única evitando o uso de um loop.

Créditos: Alexandre José Malachias….Valeu Boss

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