Blog – Fabiano Neves Amorim

Vou tentar falar de 1 operador para cada dia, vamos todos torcer para que eu lembre e consiga ter tempo para isso, vamos lá.

Para não ficar falando por 1 ano sobre os operadores, vou falar apenas daqueles operadores mais comuns, o primeiro é o Assert.

O Assert é utilizado para validar uma determinada condição, ele valida se uma Constraint não foi violada, por exemplo uma contraint Check que define que só pode haver 2 valors, o assert irá validar se o valor que foi passado como entrada está de acordo com a constraint. 

Vamos ver alguns exemplos para entendermos melhor.

Assert validando Check Constraints:

CREATE TABLE Tab1(ID Int IDENTITY(1,1) PRIMARY KEY, Sexo Char(1))

GO

ALTER TABLE TAB1 ADD CONSTRAINT ck_Sexo_M_F CHECK(Sexo IN(‘M’,‘F’))

GO 

INSERT INTO Tab1(Sexo) VALUES(‘X’)

GO

O plano de execução para o insert é o seguinte,

INSERT INTO Tab1(Sexo) VALUES(‘X’)

  |–Assert(WHERE:(CASE WHEN [rd_des_176].[dbo].[Tab1].[Sexo]<>’F’ AND [rd_des_176].[dbo].[Tab1].[Sexo]<>’M’ THEN (0) ELSE NULL END))

       |–Table Insert(OBJECT:([rd_des_176].[dbo].[Tab1]), SET:([rd_des_176].[dbo].[Tab1].[Sexo] = [Expr1004]), DEFINE:([Expr1004]=CONVERT_IMPLICIT(char(1),[@1],0)))

Como podemos observar o plano utiliza o Assert para verificar se o valor que está sendo inserido não viola a contraint,

Se o valor for diferente de ‘F’ e diferente de <> ‘M’ então retorna 0 senão retorna NULL,

O Assert é programado para gerar um erro caso o seu valor de saída seja diferente de NULL, o seja, o valor não é nem ‘F’ e nem ‘M’.

 

Assert validando Foreign Keys:

 

ALTER TABLE Tab1 ADD ID_TipoSexo Int

GO

CREATE TABLE Tab2(ID Int IDENTITY(1,1) PRIMARY KEY, Sexo Char(1))

GO

INSERT INTO Tab2(Sexo) VALUES(‘F’)

INSERT INTO Tab2(Sexo) VALUES(‘M’)

INSERT INTO Tab2(Sexo) VALUES(‘I’)

GO

ALTER TABLE TAB1 ADD CONSTRAINT fk_Tab2 FOREIGN KEY (ID_TipoSexo) REFERENCES Tab2(ID)

GO

INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES(4, ‘X’) 

INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES(4, ‘X’)

  |–Assert(WHERE:(CASE WHEN NOT [Pass1009] AND [Expr1008] IS NULL THEN (0) ELSE NULL END))

       |–Nested Loops(Left Semi Join, PASSTHRU:([tempdb].[dbo].[Tab1].[ID_TipoSexo] IS NULL), OUTER REFERENCES:([tempdb].[dbo].[Tab1].[ID_TipoSexo]), DEFINE:([Expr1008] = [PROBE VALUE]))

            |–Assert(WHERE:(CASE WHEN [tempdb].[dbo].[Tab1].[Sexo]<>’F’ AND [tempdb].[dbo].[Tab1].[Sexo]<>’M’ THEN (0) ELSE NULL END))

            |    |–Clustered Index Insert(OBJECT:([tempdb].[dbo].[Tab1].[PK__Tab1__3214EC277097A3C8]), SET:([tempdb].[dbo].[Tab1].[ID_TipoSexo] = [@1],[tempdb].[dbo].[Tab1].[Sexo] = [Expr1004],[tempdb].[dbo].[Tab1].[ID] = [Expr1003]))

            |         |–Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(char(1),[@2],0)))

            |              |–Compute Scalar(DEFINE:([Expr1003]=getidentity((1856985942),(2),NULL)))

            |                   |–Constant Scan

            |–Clustered Index Seek(OBJECT:([tempdb].[dbo].[Tab2].[PK__Tab2__3214EC27755C58E5]), SEEK:([tempdb].[dbo].[Tab2].[ID]=[tempdb].[dbo].[Tab1].[ID_TipoSexo]) ORDERED FORWARD)

Desta vez podemos ver o operador Assert duas vezes, primeiro validando a Check Constraint, e depois ele pegando o resultado do Join da Tab1 com a Tab2 e utilizando o mesmo controle de 0 continua a execução ou NULL gera erro. O interessante aqui é que o “[Expr1008] IS NULL”, pois se o valor passado para a coluna ID_TipoSexo for NULL então não pode gerar erro, neste caso ele retornaria o 0, e continuaria o processo.

Se você executar o INSERT o SQL irá reclamar do valor do X, se você alterar o X para F, ele irá reclamar do valor do 4, se você trocar o 4 para NULL, 1, 2 ou 3 o insert irá ser executado com sucesso.

Assert validando SubQuery:

O Assert também faz a validação de uma SubQuery por ex, sabemos que uma SubQuery Scalar não pode retornar mais de um valor, mas nem sempre isso acontece, o responsável por validar se a SubQuery Scalar retornou mais de um valor é o Assert.

INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES((SELECT ID_TipoSexo FROM Tab1), ‘F’)

  INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES((SELECT ID_TipoSexo FROM Tab1), ‘F’)

  |–Assert(WHERE:(CASE WHEN NOT [Pass1016] AND [Expr1015] IS NULL THEN (0) ELSE NULL END))

       |–Nested Loops(Left Semi Join, PASSTHRU:([tempdb].[dbo].[Tab1].[ID_TipoSexo] IS NULL), OUTER REFERENCES:([tempdb].[dbo].[Tab1].[ID_TipoSexo]), DEFINE:([Expr1015] = [PROBE VALUE]))

            |–Assert(WHERE:([Expr1017]))

            |    |–Compute Scalar(DEFINE:([Expr1017]=CASE WHEN [tempdb].[dbo].[Tab1].[Sexo]<>’F’ AND [tempdb].[dbo].[Tab1].[Sexo]<>’M’ THEN (0) ELSE NULL END))

            |         |–Clustered Index Insert(OBJECT:([tempdb].[dbo].[Tab1].[PK__Tab1__3214EC277097A3C8]), SET:([tempdb].[dbo].[Tab1].[ID_TipoSexo] = [Expr1008],[tempdb].[dbo].[Tab1].[Sexo] = [Expr1009],[tempdb].[dbo].[Tab1].[ID] = [Expr1003]))

            |              |–Top(TOP EXPRESSION:((1)))

            |                   |–Compute Scalar(DEFINE:([Expr1008]=[Expr1014], [Expr1009]=’F’))

            |                        |–Nested Loops(Left Outer Join)

            |                             |–Compute Scalar(DEFINE:([Expr1003]=getidentity((1856985942),(2),NULL)))

            |                             |    |–Constant Scan

            |                             |–Assert(WHERE:(CASE WHEN [Expr1013]>(1) THEN (0) ELSE NULL END))

            |                                  |–Stream Aggregate(DEFINE:([Expr1013]=Count(*), [Expr1014]=ANY([tempdb].[dbo].[Tab1].[ID_TipoSexo])))

            |                                       |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[Tab1].[PK__Tab1__3214EC277097A3C8]))

            |–Clustered Index Seek(OBJECT:([tempdb].[dbo].[Tab2].[PK__Tab2__3214EC27755C58E5]), SEEK:([tempdb].[dbo].[Tab2].[ID]=[tempdb].[dbo].[Tab1].[ID_TipoSexo]) ORDERED FORWARD)

Na consulta acima, o SQL gerou um Stream Aggregate para calcular quantas linhas a subquery irá retornar, depois o Assert pegou este resultado o fez a sua analise.

É interessante ver como o Query Optimizer é esperto o suficiente para não utilizar operadores quando eles não são necessários, por exemplo:
INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES((SELECT ID_TipoSexo FROM Tab1 WHERE ID = 1), ‘F’)

INSERT INTO Tab1(ID_TipoSexo, Sexo) VALUES((SELECT TOP 1 ID_TipoSexo FROM Tab1), ‘F’)

Nas consultas acima, o Query Optimizer é esperto o suficiente para saber que somente uma linha será retornada, portanto não há necessidade de uso do Assert.

É Isso ai pessoal por hoje é só.

Até amahã.