Introdução aos índices em bancos de dados e no SQL Server

Boa noite pessoal,

Finalizando o ano de 2012, estou aqui postando meu último artigo desse ano. Hoje finalizei a edição do último vídeo de um conjunto de quatro vídeos explicando o que são índices, para que servem, sua estrutura interna entre outros aspectos.

Iniciei a elaboração desses vídeos pensando na dificuldade que é ter explicar o que são índices em curto período de tempo nos momentos como MCT, no desafio que é explicar a diferença entre índices clustered e nonclustered em sucessivas dúvidas em fórum e principalmente para montar um material de referência que sirva para detalhar melhor algumas das explicações que vejo por aí além de desmitificar alguns equívocos que vejo por aí a citar:

“Imagine uma árvore, a organização dos indices é semelhante a um formato de árvore, na estrutura da árvore os dados das tabelas resídem no nível folha, ou seja, imagine os dados espalhados por todas as folhas da árvore, para facilitar a busca nestas folhas é que existem os índices.”

“O índice Clustered, geralmente é definido juntamente com a chave primária da tabela. Uma característica importante deste tipo de indice é que ele determina a ordem da gravação (ordenação física) dos registros.”

Independente de certas ou erradas, são afirmações que dizem muito pouco ou praticamente nada para quem ainda não tenha sido apresentado ao assunto. Assim sendo, para aqueles que desejam uma introdução mais sólida sobre o assunto de forma a montar uma boa base para aprofundá-lo e ter argumentos em discussões futuras eu recomendo dar uma olhada nos vídeos abaixo:

Índices – Fundamentos, Estruturas & Usabilidade (Parte 1)

Índices – Fundamentos, Estruturas & Usabilidade (Parte 2)

Índices – Fundamentos, Estruturas & Usabilidade (Parte 3)

Índices – Fundamentos, Estruturas & Usabilidade (Parte 4)

Elogios, sugestões e críticas são muito bem vindas. Não deixe de comentar para que eu possa evoluí-los. No resto mais, um ótimo 2013 a todos.

[ ]s,

Gustavo

Índices e Foreing Keys

Boa Noite Pessoal,

Hoje vi uma dúvida interessante no fórum de SQL Server (MSDN / TechNET). A dúvida em si não era complicada. Bastava apenas identificar quais foreign keys estavam com índices criados ou não. Postarei um script para fazer isso logo mais, mas antes de propriamente apresentá-lo parei um pouco para pensar a respeito dessa necessidade. Se alguém deseja identificar quais FKs não tem índice é porque certamente pretenderá criar índices sobre elas, o que me remete a pensar se indexar todas as FKs é de fato um bom negócio. Sim, vejo muitas recomendações para fazê-lo em vários livros de performance e modelagem de dados. De fato, costuma ser uma boa coisa em muitas situações, mas se fosse bom sempre, então porque o SQL Server, o ORACLE, o DB2 e todos os outros não criam a FKs indexadas por padrão ?

Por que criar índices sobre Foreign Keys ?

A partir do momento em que uma FK é criada sobre uma coluna ou um grupo de colunas, é impositivo que essa coluna ou esse grupo possui um relacionamento com uma coluna ou grupo de colunas de outra tabela que possui essa coluna ou grupo compondo a chave primária. Uma vez que esse relacionamento é construído por essas constraints, em algum momento será necessário identificá-lo para montar as consultas e as junções (JOINs), para recuperar um determinado resultado que combine dados de duas ou mais tabelas.

As operações de JOIN de um ponto de vista sintático são bem conhecidas (INNER, OUTER, FULL, NATURAL, etc), mas fisicamente falando, elas costumam obedecer três algoritmos básicos (Nested Loops, Merge Join e Hash Join). Não vou explicar os algoritmos hoje, mas limito a dizer que não existe um melhor ou pior que o outro (embora alguns jurem que HASH JOIN não é uma boa coisa), apenas que o otimizador normalmente sabe quando é melhor utilizar um ou outro algoritmo em uma determinada situação. O fato é que a presença de índices pode auxiliar o otimizador na escolha desse algoritmo e por consequência retornar os dados mais rapidamente. Vejamos um rápido exemplo:

— Retira a nulabilidade
SET ANSI_NULL_DFLT_OFF ON

— Cria as tabelas
CREATE TABLE Pessoas (ID INT, Nome VARCHAR(50))
CREATE TABLE Saldos (DataSaldo DATE, IDPessoa INT, Valor SMALLMONEY, HashRegistro UNIQUEIDENTIFIER)

— Cria as constraints
ALTER TABLE Pessoas ADD PRIMARY KEY (ID)
ALTER TABLE Saldos ADD PRIMARY KEY (DataSaldo, IDPessoa)
ALTER TABLE Saldos ADD FOREIGN KEY (IDPessoa) REFERENCES Pessoas (ID)
ALTER TABLE Saldos ADD DEFAULT NewID() FOR HashRegistro

— Insere alguns registros de pessoas
INSERT INTO Pessoas VALUES
(1,‘Vinicius’),(2,‘Wagner’),(3,‘André’),(4,‘Gilson’),(5,‘Emerson’)

— Insere vários registros fictícios de saldos
;WITH Nums (Num) As (
    SELECT 0 As Num
    UNION ALL SELECT Num + 1 FROM Nums
    WHERE Num < 4500)

INSERT INTO Saldos (DataSaldo, IDPessoa, Valor)
SELECT DATEADD(d,N1.Num,‘2000-01-01’) As Data, N2.Num + 1 As IDPessoa, 1000 As Valor
FROM Nums As N1
CROSS JOIN Nums As N2
WHERE N2.Num <= 2
ORDER BY Data, IDPessoa
OPTION (MAXRECURSION 0)

— Insere um registro de saldo
INSERT INTO Saldos VALUES (‘2012-08-15’,4,5000, NEWID())

— Cria um índice sobre a coluna IDPessoa na tabela Saldos
CREATE INDEX IX_Pessoa ON Saldos (IDPessoa)

Agora que a tabela está criada, vejamos uma consulta que traga o nome e os registros de saldo do primeiro cliente:

SELECT P.Nome, S.* FROM Pessoas As P
INNER JOIN Saldos As S ON P.ID = S.IDPessoa
WHERE P.ID = 4

O plano de execução é exibido logo abaixo:

Podemos ver que primeiro o índice IX_Pessoa foi utilizado para pesquisar os registros na tabela de Saldo com IDPessoa = 4 retornando um ponteiro para o único registro existente na tabela de Saldos com essa condição. Posteriormente, após achar os ponteiros no índice, foi feita uma operação de Lookup contra a tabela Saldos e a junção do ponteiro do índice IX_Pessoa com os registros foram consolidadas em uma operação de junção através do algoritmo Nested Loop. Para juntar os registros da tabela de pessoas com a tabela de Saldos, foi feita uma outra operação de JOIN através do algoritmo Nested Loops e por fim o resultado foi devolvido (muito rapidamente por sinal). Podemos ver que nesse caso, o uso do índice foi muito benéfico para acelerar a pesquisa e o resultado corrobora com a tese de que indexar FKs é uma boa idéia.

Uma segunda opinião

E se mudássemos a consulta ? E se ao invés de escolher IDPessoa = 4 escolhéssemos IDPessoa = 1 ? Vejamos:

SELECT P.Nome, S.* FROM Pessoas As P
INNER JOIN Saldos As S ON P.ID = S.IDPessoa
WHERE P.ID = 1

O plano de execução é exibido logo abaixo:

Dessa vez os resultados foram um pouco diferentes da consulta anterior. Podemos ver que na tabela Pessoa, houve uma pesquisa (Seek) para recuperar a pessoa de ID = 1, mas na tabela Saldos, houve uma varredura (Scan) da tabela inteira (Clustered Index pq ela está clusterizada). O otimizador concluiu que era melhor ignorar o índice IX_Pessoa, pois, para favorecer a condição IDPessoa = 1, seria mais fácil ler a tabela inteira do que ir no índice e ir na tabela registro a registro. Se formos analisar por esse resultado, a criação de índices em FKs só tem a trazer desvantagens. O índice IX_Pessoa na tabela de saldos irá retardar as operações de escrita, irá ocupar mais espaço e não servirá para atender as consultas que utilizem a coluna IDPessoa. No entanto, será que o índice não serve mesmo para nada ?

SELECT P.Nome, COUNT(*) FROM Pessoas As P
INNER JOIN Saldos As S ON P.ID = S.IDPessoa
WHERE P.ID = 1
GROUP BY P.Nome

O plano de execução é exibido logo abaixo:

Embora o índice IX_Pessoa não seja útil para filtrar registros na tabela Saldos onde IDPessoa = 1, o fato de existir um índice  e o objetivo ser apenas uma contagem e não uma exibição das colunas, faz com que a consulta tenha de ser feita apenas sobre o índice e nesse caso, foi possível até fazer um seek e posteriormente uma agregação para posteriormente efetuar um JOIN com a tabela Pessoas. Podemos ver que o índice IX_Pessoa não é tão ruim afinal.

O papel do índice em relacionamentos fortes ou identificados

Um relacionamento é caracterizado pela presença de uma PK em sua tabela Pai e uma FK referenciado as colunas da PK em uma tabela filha. Diz-se que o relacionamento é forte ou identificado quando as colunas da FK também fazem parte da PK na tabela filha. Vejamos o papel que o índice pode desempenhar nesse caso.

— Efetua duas cópias da tabela de Saldos
SELECT IDPessoa, DataSaldo, Valor, HashRegistro
INTO Saldos2 FROM Saldos
ORDER BY IDPessoa, DataSaldo

SELECT IDPessoa, DataSaldo, Valor, HashRegistro
INTO Saldos3 FROM Saldos
ORDER BY IDPessoa, DataSaldo

— Cria as constraints (PK apenas em Saldos2)
ALTER TABLE Saldos2 ADD PRIMARY KEY (IDPessoa, DataSaldo)
ALTER TABLE Saldos2 ADD FOREIGN KEY (IDPessoa) REFERENCES Pessoas (ID)
ALTER TABLE Saldos3 ADD FOREIGN KEY (IDPessoa) REFERENCES Pessoas (ID)

— Cria um índice em Saldos3
CREATE INDEX IX_Pessoa ON Saldos3 (IDPessoa)

Agora vejamos o impacto nas consultas que fizemos anteriormente:

SELECT P.Nome, S.* FROM Pessoas As P
INNER JOIN Saldos2 As S ON P.ID = S.IDPessoa
WHERE P.ID = 1

SELECT P.Nome, S.* FROM Pessoas As P
INNER JOIN Saldos3 As S ON P.ID = S.IDPessoa
WHERE P.ID = 1

O plano de execução é exibido logo abaixo:

No primeiro caso, podemos ver que o fato da coluna IDPessoa ser a primeira coluna da chave primária (que por padrão é clusterizada) faz com que a leitura dos registros da tabela Saldos2 ainda seja uma pesquisa (Seek), pois, a chave primária composta por IDPessoa e DataSaldo impõe uma ordem dos registros por IDPessoa. Já no caso da tabela Saldos3, como não há nenhuma ordem e muitos registros para IDPessoa = 1, o índice é simplesmente ignorado e a tabela é completamente varrida (Table Scan). Proporcionalmente, podemos ver que a consulta em Saldos3 é duas vezes maior que em Saldos2 (67% vs 33% no mesmo Batch).

O papel do índice nas operações de exclusão

Até agora tivemos um foco muito grande no uso do índice nas FKs para consultas, mas ele tem papel nas operações de exclusão ou atualizações em cascata ?

— Efetua uma cópia da tabela de Saldos
SELECT * INTO Saldos4 FROM Saldos

— Cria as constraints
ALTER TABLE Saldos4 ADD PRIMARY KEY (DataSaldo, IDPessoa)
ALTER TABLE Saldos4 ADD FOREIGN KEY (IDPessoa) REFERENCES Pessoas (ID)

DELETE FROM Pessoas WHERE ID = 1

O plano de execução é exibido logo abaixo:

Podemos ver que a exclusão ocorre na tabela Pessoas representada pela operação Clustered Index Delete. Também podemos ver que é feita uma verificação da existência de registros filhos nas tabelas Saldos, Saldos2, Saldos3 e Saldos4. Podemos ver que em todas as tabelas filhas é feita uma pesquisa para checagem da existência (algo muito parecido com a consulta que fez a contagem de registros nos exemplos anteriores). Especificamente na Saldos4, podemos ver que é feita uma varredura.

A varredura na tabela Saldos4 é feita porque não há como saber se existem registros para IDPessoa = 5 senão lendo a tabela inteira uma vez que não exista nenhum índice capaz de auxiliar nessa pesquisa. Em virtude da tabela ser pequena, o custo no plano é de apenas 13% (1% a mais do que as demais tabelas de Saldo). Agora imagine se essa tabela fosse muito grande e muito larga. Uma varredura poderia tornar a exclusão absurdamente lenta, além de impor bloqueios por muito tempo e muitas vezes desnecessários.

Algumas conclusões

A conclusão que chego é provavelmente a que os fabricantes já chegaram, ou seja, como não sabemos o comportamento da seletividade dos valores nas colunas da chave estrangeira, não há como afirmar se a criação de índices em chaves estrangeiras é ou não um bom negócio. Então nesse caso, é melhor assumir a posição de não criar índices nas colunas das chaves estrangeiras e deixar a cargo do implementador efetuar essa avaliação. Excetuando as operações de exclusão e atualizações em cascata, a criação de índices está muito mais voltada para a consulta em si do que o fato de existir um relacionamento. Ainda assim algumas recomendações:

1. Se você for efetuar exclusões nas tabelas pai com frequência e as tabelas filhas forem muito volumosas, indexe as FKs
2. Se você for efetuar JOINs com o objetivo apenas de contar registros ou verificar existência (EXISTs), indexe as FKs
3. Se você utilizar relacionamentos fortes ou identificados, indexe as FKs
4. Se as ocorrência dos valores nas colunas das FKs tiverem muita repetição, ou seja, forem pouco seletivas e as condições acima não forem verdadeiras, avalie se criar índices é um bom negócio (possivelmente não será)
5. Se os três primeiros itens não forem verdadeiros, olhe a criação do índice muito mais como um índice sobre um coluna comum, pois, nesse caso, a coluna ser ou não FK não fará diferença

E como descobrir quais FKs não estão indexadas ?

Seria simples utilizar o exemplo anterior que possui colunas simples, mas vou criar mais três tabelas com chaves compostas e assim podermos avaliar a eficácia do meu script. Embora as tabelas induzam um relacionamento identificado, deixarei algumas colunas de fora propositalmente

— Retira a nulabilidade
SET ANSI_NULL_DFLT_OFF ON

— Cria mais três tabelas
CREATE TABLE SaldosSuspeitos (DataSaldo DATE, IDPessoa INT, DataInclusao DATE)
CREATE TABLE SaldosLimite (DataSaldo DATE, IDPessoa INT, DataInclusao DATE)
CREATE TABLE SaldosDevedores (DataSaldo DATE, IDPessoa INT, DataInclusao DATE)

— Adiciona as Constraints
ALTER TABLE SaldosSuspeitos ADD PRIMARY KEY (DataSaldo, IDPessoa)
ALTER TABLE SaldosSuspeitos ADD FOREIGN KEY (DataSaldo, IDPessoa) REFERENCES Saldos (DataSaldo, IDPessoa)
ALTER TABLE SaldosLimite ADD FOREIGN KEY (DataSaldo, IDPessoa) REFERENCES Saldos (DataSaldo, IDPessoa)
ALTER TABLE SaldosDevedores ADD FOREIGN KEY (DataSaldo, IDPessoa) REFERENCES Saldos (DataSaldo, IDPessoa)

— Adiciona um índice na FK para a tabela SaldosDevedores
CREATE INDEX IX_Saldo ON SaldosDevedores (DataSaldo, IDPessoa)
CREATE INDEX IX_DataSaldo ON SaldosLimite (DataSaldo)

Visualmente sabemos que a tabela Saldo4 não tem índice na coluna IDPessoa e tem FK para essa coluna. Sabemos também que a tabela SaldosLimite tem a FK com as colunas DataSaldo e IDPessoa, mas possui um índice apenas sobre a coluna DataSaldo e portanto não tem sua FK indexada. Segue a consulta:

;WITH FKsNI (ObjectId, FKId) As (
SELECT DISTINCT
    parent_object_id, constraint_object_id
FROM sys.foreign_key_columns As FC
WHERE NOT EXISTS (
    SELECT * FROM sys.index_columns As IC
    WHERE
        FC.parent_object_id = IC.object_id AND
        FC.constraint_column_id = IC.index_column_id AND
        FC.parent_column_id = IC.column_id))

SELECT
    OBJECT_NAME(FKsNI.ObjectId) As Objeto,
    OBJECT_NAME(FKsNI.FKId) As FK,
    C.Name As Coluna,
    FC.constraint_column_id As Ordem
FROM FKsNI
INNER JOIN sys.foreign_key_columns As FC ON
    FKsNI.FKId = FC.constraint_object_id AND
    FKsNI.ObjectId = FC.parent_object_id
INNER JOIN sys.columns As C ON
    FC.parent_object_id = C.object_id AND
    FC.parent_column_id = C.column_id
ORDER BY Ordem

O resultado sai exatamente conforme o esperado:

Objeto	FK	Coluna	Ordem
Saldos4	FK__Saldos4__IDPesso__25869641	IDPessoa	1
SaldosLimite	FK__SaldosLimite__2B3F6F97	DataSaldo	1
SaldosLimite	FK__SaldosLimite__2B3F6F97	IDPessoa	2

Agora vem a pergunta ? Depois que você rodar a consulta e identificar as FKs que não estão indexadas, você vai criar os índices ? Depende de cada um.

[ ]s,

Gustavo

Funções Escalares – Resusabilidade, Formatação, Preguiça vs Desempenho

Bom Dia Pessoal,

Já faz algum tempo que queria escrever sobre funções escalares e recentemente com algumas threads no MSDN, me senti ainda mais motivado a escrever algumas palavrinhas a respeito. Vejo muita gente falando, usando e recomendando mesmo sem saber exatamente as penalidades envolvidas no uso de funções escalares.

O que são funções escalares ?

Função escalares são funções que recebem um ou mais valores retornam um valor. São exemplos de funções escalares naturais LTRIM, LEFT, CAST. A partir do SQL Server 2000 é possível a construção de funções escalares customizadas (User Defined Scalar Functions) obedecendo a mesma definição, ou seja, devem receber um conjunto de valores e retornar um valor. O uso das funções escalares está em consonância com um dos principais conceitos básicos da orientação, o encapsulamento. Uma vez que seja possível codificar uma lógica dentro da função (cálculo da taxa de juros, números de pedidos por empregado, ou ainda a formatação de uma data) é possível reaproveitá-la em vários trechos do códigos. O fato da função residir dentro do banco de dados também pode evitar a troca de contexto entre banco e aplicação.

Funções escalares e desempenho

Quando funções escalares customizadas são utilizadas em queries tendo como parâmetros de entradas valores das colunas referenciadas na instrução SELECT, normalmente há um alto custo de desempenho associado, pois, essas funções tem de ser chamadas uma vez para cada linha retornada (Row based) já que esse tipo de chamada não é otimizado pelo otimizador de consulta, pois, necessita de várias checagens adicionais (permissão, análise sintática, análise semântica, etc). Se uma função escalar necessita ser utilizada para conjuntos com poucos linhas, esse custo é imperceptível, mas quando grandes quantidade de linhas são necessárias, o custo da utilização das funções escalares torna-se cada mais visível.

Para demonstrar a perda de desempenho provocada pelo uso de funções escalares, o teste a seguir mostra duas consultas: uma com o uso de uma função escalar em conjunto com uma instrução SELECT e outra com a mesma lógica direta no SELECT sem a utilização de uma função:

Para demonstrar a perda de desempenho provocada pelo uso de funções escalares, o teste a seguir mostra duas consultas: uma com o uso de uma função escalar em conjunto com uma instrução SELECT e outra com a mesma lógica direta no SELECT sem a utilização de uma função:

— Cria uma tabela com 1 milhão de números
CREATE TABLE tNums (Num INT NOT NULL)

DECLARE @i INT
SET @i = 1

WHILE @i <= 1000000
BEGIN
    INSERT INTO tNums VALUES (@i)
    SET @i = @i + 1
END

— Marca a coluna Num como chave primária
ALTER TABLE tNums ADD CONSTRAINT PK_Num PRIMARY KEY (Num)

— Cria uma função que recebe um número e mostra o mesmo número mais um
CREATE FUNCTION fnAumenta1 (@Num INT)
RETURNS INT
As
BEGIN
    RETURN(@Num + 1)
END

— Retorna os 10 primeiros números com uso da função
SELECT Num, dbo.fnAumenta1(Num) FROM tNums
WHERE Num <= 10

— Retorna os 10 primeiros números seu o uso da função
SELECT Num, Num + 1 FROM tNums
WHERE Num <= 10

Se compararmos o plano de execução, as consultas são idênticas.

Entretanto, o Profiler nos revela algumas diferenças:

A execução do primeiro comando mostra que a função fnAumenta1 foi chamada 10 vezes (uma para cada linha do SELECT) e que a execução total teve de efetuar 7 leituras. A execução do segundo comando foi mais direta e resultou em apenas 3 leituras. Como a quantidade linhas envolvidas é bem pequena (apenas 10 registros), a recuperação dos dados foi praticamente instântanea em ambos os casos. A tabela abaixo mostra a evolução à medida que a quantidade de registros aumenta. Para essa tabela foi considerada apenas a execução do comando discartando-se os resultados (Tools, Options, Query Results, Results to Grid, Discard Results After Execution)

Função ?	Registros	CPU	Leitura	Tempo(ms)
Sim	100	0	5	3
Não	100	0	3	0
Sim	1.000	16	4	6
Não	1.000	0	4	2
Sim	10.000	31	20	53
Não	10.000	0	20	5
Sim	100.000	375	164	449
Não	100.000	31	164	31
Sim	1.000.000	4102	1615	4491
Não	1.000.000	219	1615	259

As primeiras diferenças começam a aparecer acima de 1.000 registros quando o uso da função começa a gastar ciclos de CPU mais significativos (em torno de 16).  Para a quantidade 10.000 registros percebe-se que a duração do comando com a função foi 10 vezes maior (53 /5). Para os volumes de 100.000 registros e 1.000.000 registros o processamento CPU com a solução baseada em function fica entre 10 a 20 vezes maior. O tempo de execução do comando também é aumentado nessa proporção.

Alguns irão argumentar que a diferença é imperceptível já que uma consulta normalmente retorna poucos registros. Sim, é verdade que para poucos registros, a diferença é mínima, mas é preciso lembrar que o uso da função não se aplica somente ao resultado da consulta. Se funções são utilizadas em tabelas com muitos registros, joins, etc, elas irão degradar o desempenho mesmo que o resultado final seja apenas algumas poucas linhas.

Funções Escalares e Formatação

Sempre defendi que a formatação dos dados para exibição não deve ficar no banco de dados. Qualquer aplicação corretamente modulada jamais deixaria as tarefas de formatação no banco de dados já que a camada de persistência tem como objetivo gravar e recuperar dados e a formatação fica para uma camada de exibição (ou no máximo de aplicação) e não em uma camada de persistência. Ainda assim, não é difícil encontrar consultas que formatam dados. Vejamos como o uso de funções escalares possibilita essa tarefa.

— Cria uma coluna de data
ALTER TABLE tNums ADD Data DATE NULL

— Atualiza a coluna de datas
UPDATE tNums SET Data = DATEADD(D,Num % 20,‘20110522’)

— Cria uma função para formatar a data
CREATE FUNCTION dbo.fnFormataData (@Data DATE)
RETURNS VARCHAR(30)
As
BEGIN
    DECLARE @DataRet VARCHAR(30)
    SET @DataRet = CONVERT(CHAR(10),@Data,103)
    SET @DataRet = @DataRet + ‘ (‘ +
        CASE @@DATEFIRST
            WHEN 1 THEN ‘Segunda-Feira’
            WHEN 2 THEN ‘Terça-Feira’
            WHEN 3 THEN ‘Quarta-Feira’
            WHEN 4 THEN ‘Quinta-Feira’
            WHEN 5 THEN ‘Sexta-Feira’
            WHEN 6 THEN ‘Sábado’
            WHEN 7 THEN ‘Domingo’
        END + ‘ )’
    RETURN (@DataRet)
END

A função dbo.fnFormataData converte uma data para o formato 103 (dd/mm/yyyy) e em seguida adiciona um sufixo com o dia da semana. Vejamos o desempenho com o uso de uma consulta sem formatação, com formatação na consulta e com o uso da função de formatação:

— Retorna as datas distintamente
SELECT DISTINCT Data FROM tNums

— Retorna as datas distintamente com formatação
SELECT DISTINCT
        CONVERT(CHAR(10),Data,103) + ‘ (‘ +
        CASE @@DATEFIRST
            WHEN 1 THEN ‘Segunda-Feira’
            WHEN 2 THEN ‘Terça-Feira’
            WHEN 3 THEN ‘Quarta-Feira’
            WHEN 4 THEN ‘Quinta-Feira’
            WHEN 5 THEN ‘Sexta-Feira’
            WHEN 6 THEN ‘Sábado’
            WHEN 7 THEN ‘Domingo’
        END + ‘ )’ FROM tNums

— Retorna as datas distintamente com função
SELECT DISTINCT dbo.fnFormataData(Data) FROM tNums

Como podemos ver, só a consulta pura gastou 200ms. Se colocarmos a formatação na consulta, esse tempo vai para 809ms. Ao meu ver é um enorme desperdício formatar dados na camada de banco. Veja que a consulta formatada teve 609ms (809 – 200) só para formatar dados, ou seja, do seu tempo total de execução, 25% do tempo foi para recuperar os dados e 75% do tempo para formatá-los. O uso da CPU para essa formatação foi quase 5 vezes maior (e olha que ainda fizemos um SCAN em uma tabela com 1.000.000 de linhas que representam apenas 3256 blocos nesse exemplo). Se os dados fossem devolvidos para que a aplicação formatasse, teríamos gastado menos recursos do SGBD e mais recursos do servidor de aplicação. Não há como “sumir” com o processamento, mas convenhamos que é mais correto deixar a formatação onde ela deve realmente ficar e vale a pena lembrar que servidores de aplicação escalam mais facilmente que um servidor de banco de dados, pois, dispõe de facilidades como NLB, Web Farm, Web Garden, etc.

A última consulta que usa a função escalar para formatar nem merece muita apreciação. Gastou mais de 20 vezes o total de CPU e quase 50 vezes mais lenta que a consulta de dados sem formatação. Notoriamente um grande desperdício. É comum reproduzí-lo quando se usa o DISTINCT em conjunto com formatação. Se for realmente imprescindível o uso da formatação, opte por fazê-la após o DISTINCT e não antes.

— Efetua o DISTINCT antes, formata depois
;WITH DadosDistintos As (
    SELECT DISTINCT Data FROM tNums)
SELECT DISTINCT dbo.fnFormataData(Data) FROM DadosDistintos

— Retorna as datas distintamente com função
SELECT DISTINCT dbo.fnFormataData(Data) FROM tNums

 

Nesse caso, mesmo a UDF que era o pior dos casos teve seu desempenho muito reduzido tanto em termos de CPU quanto de duração do comando (algo em torno de 30 vezes mais rápido). Quando se usa DISTINCT, filtrar antes e formatar depois é uma excelente prática para ganhar desempenho (embora pouco conhecida e pouco utilizada).

Funções Escalares e Pregüiça

Alguns optam pelo uso de funções escalares para “escapar” de fazer um JOIN ou ainda “fugir” de um GROUP BY. Acredito que essa seja uma das piores utilizações para funções escalares.

— Cria uma função para contar quantas ocorrências existem por data
CREATE FUNCTION dbo.fnContaOcorrenciasPorData (@Data DATE)
RETURNS INT
As
BEGIN
    DECLARE @Total INT
    SET @Total = (
        SELECT COUNT(*) FROM tNums
        WHERE Data = @Data)
    RETURN(@Total)
END

— Cria uma tabela de eventos
CREATE TABLE Eventos (
    Data DATE,
    TipoEvento VARCHAR(50))
INSERT INTO Eventos VALUES (‘20110522’,‘Palestra Estagiários’)
INSERT INTO Eventos VALUES (‘20110523’,‘Café de Boas Vindas’)
INSERT INTO Eventos VALUES (‘20110524’,‘Oficina de Contabilidade’)
INSERT INTO Eventos VALUES (‘20110525’,‘Oficina de Contabilidade’)
INSERT INTO Eventos VALUES (‘20110526’,‘Oficina de Contabilidade’)
INSERT INTO Eventos VALUES (‘20110527’,‘Visita à Matriz’)
INSERT INTO Eventos VALUES (‘20110528’,‘Oficina de Risco Operacional’)
INSERT INTO Eventos VALUES (‘20110529’,‘Oficina de Risco Operacional’)
INSERT INTO Eventos VALUES (‘20110530’,‘Oficina de Risco Operacional’)
INSERT INTO Eventos VALUES (‘20110531’,‘Encerramento do Mês’)

— Usa a função para contar quantas ocorrências existem para cada evento
SELECT TipoEvento, dbo.fnContaOcorrenciasPorData(Data) FROM Eventos

— Faz a mesma consulta com um JOIN e um GROUP BY
SELECT TipoEvento, COUNT(Num) FROM Eventos
INNER JOIN tNums ON Eventos.Data = tNums.Data
GROUP BY TipoEvento

E novamente as funções escalares perdem no quesito desempenho. O uso da função gastou quase cinco vezes mais ciclos de CPU e a duração do comando foi quase dez vezes maior. O detalhe mais interessante está na coluna Reads. A tabela tNums possui 3256 blocos e a tabela Eventos possui apenas dois blocos. O JOIN faz um SCAN em ambas e por isso o total de leituras na implementação baseada em JOIN e GROUP BY foi de 3258 blocos. Já a função, como precisa fazer a contagem para cada linha, ela leu várias vezes o mesmo bloco fazendo com que a quantidade de leituras fosse dez vezes maior. Esse é o custo da “pregüiça”.

Mantendo a reusabilidade

Se apenas o desempenho for levado em consideração, provavelmente a recomendação geral seria não utilizar funções escalares e deixar a lógica na própria consulta ou ainda na aplicação. Entretanto, as funções escalares provêm o benefício da reusabilidade, pois, uma vez criadas, podem ser reaproveitadas em várias consultas sem a necessidade de remotar sua lógica em cada consulta. É visível que isso custa desempenho, mas é possível manter o benefício da reusabilidade reduzindo essa penalidade. Vejamos como fazê-lo.

— Cria uma tabela de cotações
CREATE TABLE Cotacoes (
    DataRef DATE NOT NULL,
    Taxa DECIMAL(7,4))

— Cadastra algumas taxas
INSERT INTO Cotacoes VALUES (‘20110501’,0.0142)
INSERT INTO Cotacoes VALUES (‘20110601’,0.0157)
INSERT INTO Cotacoes VALUES (‘20110701’,0.0167)
INSERT INTO Cotacoes VALUES (‘20110801’,0.0135)
INSERT INTO Cotacoes VALUES (‘20110901’,0.0103)
INSERT INTO Cotacoes VALUES (‘20111001’,0.0121)
INSERT INTO Cotacoes VALUES (‘20111101’,0.0158)
INSERT INTO Cotacoes VALUES (‘20111201’,0.0174)

— Monta um função de cálculo de montante final
CREATE FUNCTION dbo.FnRetornaRentabilidade (@DataInicio DATE, @Dias SMALLINT)
RETURNS DECIMAL(9,6)
As
BEGIN

— Inicializa a variável para calcular a taxa
DECLARE @Taxa DECIMAL(9,6)

— Calcula o mês de referência
DECLARE @DataRef DATE
SET @DataRef = (DATEADD(D,1-DAY(@DataInicio),@DataInicio))

— Calcula a taxa proporcional em relação ao início do mês de referência
DECLARE @DiasFimMes SMALLINT
SET @DiasFimMes = DATEDIFF(D,@DataInicio,DATEADD(M,1,@DataRef))

SET @Taxa = (SELECT Taxa FROM Cotacoes WHERE DataRef = @DataRef)
SET @Taxa = POWER(1 + @Taxa,@DiasFimMes / CAST(DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef)) As Decimal(7,4))) – 1

— Calcula o restante dos dias úteis
DECLARE @DiasRestantes SMALLINT
SET @DiasRestantes = @Dias – @DiasFimMes

— Aumenta a data de referência em um mês
SET @DataRef = DATEADD(M,1,@DataRef)

WHILE (@DiasRestantes > 0)
BEGIN
    — Verifica se irá fechar o próximo mês
    IF @DiasRestantes > DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef))
    BEGIN
        SET @Taxa = ((1 + @Taxa) * (1 + (SELECT Taxa FROM Cotacoes WHERE DataRef = @DataRef))) – 1
        SET @DiasRestantes = @DiasRestantes – DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef))
        SET @DataRef = DATEADD(M,1,@DataRef)
    END
    ELSE
    BEGIN
        SET @Taxa = (1 + @Taxa) * POWER(1 + (SELECT Taxa FROM Cotacoes WHERE DataRef = @DataRef),
        @DiasRestantes / CAST(DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef)) As Decimal(7,4)))
        SET @DiasRestantes = 0
    END
END

— Se for menos de 40 dias, não há rendimento
SET @Taxa = CASE WHEN @Dias < 40 THEN 0 ELSE @Taxa – 1 END

RETURN(@Taxa)

END

— Testa a função
SELECT dbo.FnRetornaRentabilidade(‘20110511’,40)

De acordo com as taxas oferecidas, se um investimento for feito 11/05/2011 e durar 40 dias, ele irá render 1,9592% que correspondem a 20 dias na rentabilidade de maio 1,42% e 20 dias na rentabilidade junho (1,57%). Não vou entrar nos detalhes desse cálculo (isso fica para uma outra oportunidade), mas ao contrário das abordagens anteriores, esse é o tipo de lógica que, embora possível de ser incorporada a uma consulta diretamente, normalmente ficará dentro de uma função escalar, pois, será bem oportuno que esse cálculo possa ser encapsulado e reutilizado. Será que é possível amenizar a perda de desempenho provocada pelo uso da função linha a linha ?

— Cria uma tabela de aplicações financeiras
CREATE TABLE AppFin (
    ClienteID INT NOT NULL,
    DataApp DATE NOT NULL,
    Valor MONEY NOT NULL,
    DiasCorridos SMALLINT NOT NULL)

— Restringe o investimento a no mínimo 40 dias
ALTER TABLE AppFin ADD CONSTRAINT CK_DiasCorridos CHECK (DiasCorridos >= 40)

— Insere alguns investimentos de forma aleatória
DECLARE @qtdInvestimentos INT
DECLARE @MaxClienteID INT
DECLARE @MaxDataApp TINYINT
DECLARE @MaxValor MONEY
DECLARE @MaxDiasCorridos TINYINT

SET @qtdInvestimentos = 500000
SET @MaxClienteID = 1000
SET @MaxDataApp = 5
SET @MaxValor = 1000000
SET @MaxDiasCorridos = 2

DECLARE @i INT
SET @i = 1

WHILE @i <= @qtdInvestimentos
BEGIN
    INSERT INTO AppFin VALUES (
        ABS(CHECKSUM(NEWID())) % @MaxClienteID,
        DATEADD(M,ABS(CHECKSUM(NEWID())) % 5,‘20110511’),
        ABS(CHECKSUM(NEWID())) % @MaxValor / 100.0000,
        ((ABS(CHECKSUM(NEWID())) % @MaxDiasCorridos + 1) * 30) + 10)
    SET @i = @i + 1
END

— Efetua a consulta para retornar a rentabilidade
SELECT
    ClienteID, DataApp, Valor, DiasCorridos,
    dbo.FnRetornaRentabilidade(DataApp, DiasCorridos)
FROM AppFin

Considerando que a tabela AppFin possui 1697 páginas, os resultados do cálculo da do foram expressivos. Para realizar a consulta em questão foram necessárias 3752278 páginas. Isso significa mais de 2.200 vezes o total de páginas da tabela além de um gasto de CPU e tempo muito significativos (quase 30 segundos). Será que há otimização possível ?

No exemplo, a rentabilidade é influenciada basicamente pelos parâmetros DataApp e DiasCorridos dispensado as demais colunas da tabela AppFin. Possivelmente haverá várias repetições dessa combinação. Vejamos quantas combinações distintas existem nessas duas colunas.

— Verifica quantas combinações distintas existem para o cálculo da rentabilidade
SELECT DISTINCT DataApp, DiasCorridos FROM AppFin

No meu exemplo foram retornadas apenas 10 ocorrências. Penso que calcular a rentabilidade para 10 ocorrências é possivelmente mais eficiente que calcular a rentabilidade para 500.000 ocorrências principalmente porque das 500.000 temos apenas dez tipos. Minha proposta seria efetuar o cálculo separadamente para essas dez e posteriormente efetuar um JOIN com as outras 500.000. Vejamos se essa abordagem funciona.

— Calcula as taxas distintamente para reduzir o número de cálculos
— Posteriormente efetua um JOIN do Resultados
;WITH
    AppDistintas (DataApp, DiasCorridos) As
        (SELECT DISTINCT DataApp, DiasCorridos FROM AppFin),
    RentabilidadeCalculada (DataApp, DiasCorridos, Rentabilidade) As
        (SELECT
            DataApp, DiasCorridos, dbo.FnRetornaRentabilidade(DataApp, DiasCorridos)
            FROM AppDistintas)

SELECT
    A.ClienteID, A.DataApp, A.Valor, A.DiasCorridos, R.Rentabilidade
FROM AppFin As A
INNER JOIN RentabilidadeCalculada As R ON A.DataApp = R.DataApp AND A.DiasCorridos = R.DiasCorridos

Embora as CTEs tenham me ajudado no exemplo da formatação, dessa vez elas me deixaram na mão. Os indicadores (CPU, tempo e leituras) são muito próximos com uma leve desvantagens para as CTEs. Ainda assim, a idéia de fazer 10 vezes e não 500.000 ainda me parece mais inteligente. Vejamos agora o resultado com uma tabela temporária.

— Calcula as taxas distintamente para reduzir o número de cálculos
— Posteriormente efetua um JOIN do Resultados
— Utiliza uma TMP para persistir os resultados temporariamente
;WITH
    AppDistintas (DataApp, DiasCorridos) As
        (SELECT DISTINCT DataApp, DiasCorridos FROM AppFin),
    RentabilidadeCalculada (DataApp, DiasCorridos, Rentabilidade) As
        (SELECT
            DataApp, DiasCorridos, dbo.FnRetornaRentabilidade(DataApp, DiasCorridos)
            FROM AppDistintas)

SELECT DataApp, DiasCorridos, Rentabilidade INTO #TMP FROM RentabilidadeCalculada

SELECT
    A.ClienteID, A.DataApp, A.Valor, A.DiasCorridos, R.Rentabilidade
FROM AppFin As A
INNER JOIN #TMP As R ON A.DataApp = R.DataApp AND A.DiasCorridos = R.DiasCorridos

Dessa vez os resultados foram bastante significativos, além de reduzir as consultas anteriores de 27 segundos para pouco mais de meio segundo, tivemos um ganho expressivo em termos de CPU (menos de 3% do consumo anterior) e uma economia de leituras na casa de 99% (3602 conta 3752276). Definitivamente, fico com essa implementação. Ela mantem a resusabilidade sem comprometer o desempenho. A lógica é simples, se a função precisa ser feita linha a linha, é melhor fazer o menor número de vezes possível.

Confesso que ainda não descobri o porquê das CTEs terem falhado nesse caso. Normalmente são alternativas mais interessantes que o uso de tabelas temporárias, mas enfim… Isso é assunto para uma nova pesquisa e aprendizado.

O uso de funções escalares customizadas não é ruim no SQL Server. Pode encapsular lógicas complexas, facilitar a manutenção do código, prover mecanismos de segurança entre várias outras utilidades. Entretanto, uma boa implementação não consiste no seu uso indiscriminado, mas sim na sua aplicação de forma planejada conhecendo suas vantagens, as penalidades associadas e a forma de lidar com elas.

[ ]s,

Gustavo

Consultas parametrizadas, ISNULL e SQL dinâmica

Bom Dia Pessoal,

Por várias vezes nos fóruns e em algumas situações que presencio na vida real, vejo implementações para execução de consultas parametrizadas com base em vários parâmetros. É sem dúvida uma necessidade muito comum, pois, se uma determinada entidade (tabela) possui várias características (colunas), é quase natural que se pesquise as instâncias (registros) dessa entidade com base em uma, ou duas, ou N características de forma individual ou simultânea. Não é errado necessitar de tal mecanismo de pesquisa, mas o que vejo é que muitas implementações o fazem de forma completamente equivocada e muitas vezes pouco performática. Como poucos registros em um ambiente de desenvolvimento e homologação a diferença é imperceptível, mas à medida em que o volume em produção aumenta, a dificuldade de escalar é nata e muitas vezes a solução não é aumentar a infraestrutura. Vejamos então o que as implementações de consultas parametrizadas devem e não devem fazer.

Inicialmente criarei uma tabela com várias colunas que são potenciais características de pesquisa.

— Cria uma tabela de Pessoas
CREATE TABLE Pessoas (
    ID INT, Nome VARCHAR(100), SobreNome VARCHAR(100),
    Filhos TINYINT, AnoNascimento SMALLINT,
    Cidade VARCHAR(100), UF CHAR(2))

— Insere quinze registros
INSERT INTO Pessoas VALUES (01,‘João’,‘Silva’,2,1950,‘São Paulo’,‘SP’)
INSERT INTO Pessoas VALUES (02,‘João’,‘Gonçalves’,1,1990,‘Porto Alegre’,‘RS’)
INSERT INTO Pessoas VALUES (03,‘João’,‘Leite’,0,1992,‘Natal’,‘RN’)
INSERT INTO Pessoas VALUES (04,‘Daniel’,‘Antunes’,0,1986,‘Diadema’,‘SP’)
INSERT INTO Pessoas VALUES (05,‘Daniel’,‘Mendes’,1,1979,‘Manaus’,‘AM’)
INSERT INTO Pessoas VALUES (06,‘Daniela’,‘Petrovisk’,1,1976,‘Salvador’,‘BA’)
INSERT INTO Pessoas VALUES (07,‘Danilo’,‘Silva’,3,1965,‘Brasília’,‘DF’)
INSERT INTO Pessoas VALUES (08,‘Peter’,‘Parker’,0,1989,‘Fortaleza’,‘CE’)
INSERT INTO Pessoas VALUES (09,‘Isabela’,‘Costa’,2,1984,‘São Paulo’,‘SP’)
INSERT INTO Pessoas VALUES (10,‘Regiane’,‘Meira’,5,1945,‘Recife’,‘PE’)
INSERT INTO Pessoas VALUES (11,‘Maíra’,‘Gonçalves’,4,1982,‘São Paulo’,‘SP’)
INSERT INTO Pessoas VALUES (12,‘Nayara’,‘Silva’,2,1950,‘Brasília’,‘DF’)
INSERT INTO Pessoas VALUES (13,‘Patríca’,‘Gomides’,2,1950,‘São Paulo’,‘SP’)
INSERT INTO Pessoas VALUES (14,‘Natália’,‘Arruda’,2,1950,‘Rio de Janeiro’,‘RJ’)
INSERT INTO Pessoas VALUES (15,‘Márcia’,‘Alves’,0,1983,‘Brasília’,‘DF’)

— Completa até 10.000 de registros fixos de São Paulo
DECLARE @i INT
SET @i = 16

WHILE @i <= 10000
BEGIN
    INSERT INTO Pessoas VALUES (@i,‘Fulano’,‘Silva’,0,1969,‘São Paulo’,‘SP’)
    SET @i = @i + 1
END

— Insere 200 registros com sobrenome “Mendes”
— Insere 200 pessoas que nasceram em 1979
DECLARE @i INT
SET @i = 10001

WHILE @i <= 10200
BEGIN
    INSERT INTO Pessoas VALUES (@i,‘Osvaldo’,‘Mendes’,1,1987,‘Brasília’,‘DF’)
    INSERT INTO Pessoas VALUES (@i + 200,‘Fabiano’,‘Rocha’,0,1979,‘Rio de Janeiro’,‘RJ’)
    SET @i = @i + 1
END

— Cria índices nas colunas potencialmente pesquisáveis e com seletividade
CREATE INDEX IX_SobreNome ON Pessoas (SobreNome)
CREATE INDEX IX_Filhos ON Pessoas (Filhos)
CREATE INDEX IX_AnoNascimento ON Pessoas (AnoNascimento)
CREATE INDEX IX_Cidade ON Pessoas (Cidade)
CREATE INDEX IX_UF ON Pessoas (UF)

O exemplo não retrata 100% da vida real, pois, o cadastro não seria tão concentrado assim. Entretanto, meu objetivo não é bem reproduzir um cadastro, mas sim mostrar que a eficiência dos índices pode ser comprometida por uma determinada implementação (mesmo que ele possua seletividade). Vejamos algumas consultas que possam usufruir da seletividade desses índices:

— Verifica quem nasceu em 1965
SELECT * FROM Pessoas WHERE AnoNascimento = 1965

— Verifica quem tem o sobrenome Gonçalves
SELECT * FROM Pessoas WHERE SobreNome = ‘Gonçalves’

— Verifica quem tem o sobrenome Mendes e nasceu em 1979
SELECT * FROM Pessoas WHERE SobreNome = ‘Mendes’ AND AnoNascimento = 1979

— Verifica quem tem 1 filho e nasceu em Brasília
SELECT * FROM Pessoas WHERE Filhos = 1 AND Cidade = ‘Brasília’

Como é possível perceber, todas as consultas utilizaram os índices. Algumas precisaram combiná-los (Hash Match Inner Join), mas no geral, nenhuma fez nenhuma varredura.

Tradicionalmente, uma tabela de pessoas possui muitas características que servem de argumento de pesquisa e será bem comum que uma aplicação recebe parâmetros para uma, duas ou até todas as características. O comportamento esperado é bem simples. Se a característica for informada, ela deve ser considerada como filtro. Se a característica não for informada, então ela não deve ser considerada como filtro. A seguir uma implementação típica para atender essa necessidade.

— Cria uma procedure de pesquisa
CREATE PROCEDURE UspRecuperaPessoas
    @ID INT = NULL,
    @Nome VARCHAR(100) = NULL,
    @SobreNome VARCHAR(100) = NULL,
    @Filhos TINYINT = NULL,
    @AnoNascimento SMALLINT = NULL,
    @Cidade VARCHAR(100) = NULL,
    @UF CHAR(2) = NULL
As

— Recupera Pessoas
SELECT * FROM Pessoas
WHERE
    ID = ISNULL(@ID,ID) AND
    Nome = ISNULL(@Nome,Nome) AND
    SobreNome = ISNULL(@SobreNome,SobreNome) AND
    Filhos = ISNULL(@Filhos,Filhos) AND
    AnoNascimento = ISNULL(@AnoNascimento,AnoNascimento) AND
    Cidade = ISNULL(@Cidade,Cidade) AND
    UF = ISNULL(@UF,UF)

Essa é uma implementação típica de consultas parametrizadas. Simples de construir e muito fácil de manter. Basta colocar cada coluna com a função ISNULL e a comparação com o valor da variável correspondente. Caso um coluna seja alterada, retirada ou adicionada basta mudar a cláusula WHERE para refletir essa alteração. Vejamos agora se a eficiência na criação e manutenção se mantém no desempenho.

— Verifica quem nasceu em 1965
EXEC UspRecuperaPessoas @AnoNascimento = 1965

— Verifica quem tem o sobrenome Gonçalves
EXEC UspRecuperaPessoas @SobreNome = ‘Gonçalves’

— Verifica quem tem o sobrenome Mendes e nasceu em 1979
EXEC UspRecuperaPessoas @SobreNome = ‘Mendes’, @AnoNascimento = 1979

— Verifica quem tem 0 filhos e nasceu em Brasília
EXEC UspRecuperaPessoas @Filhos = 1, @Cidade = ‘Brasília’

Como podemos ver, todas as execuções tem o mesmo plano

Ao contrário do que poderia parecer, nenhuma das consultas que anteriormente usavam índices e eram eficientes comportou-se da mesma forma dentro da Stored Procedure. Não é difícil imaginar o porquê disso acontecer. Já é conhecido que a utilização de funções sobre colunas força tende a mudar a execução para um SCAN. Ainda assim, vejamos a reescrita da procedure com uma semântica parecida, mas sem o ISNULL.

— Cria uma procedure de pesquisa
ALTER PROCEDURE UspRecuperaPessoas
    @ID INT = NULL,
    @Nome VARCHAR(100) = NULL,
    @SobreNome VARCHAR(100) = NULL,
    @Filhos TINYINT = NULL,
    @AnoNascimento SMALLINT = NULL,
    @Cidade VARCHAR(100) = NULL,
    @UF CHAR(2) = NULL
As

— Recupera Pessoas
SELECT * FROM Pessoas
WHERE
    (ID = @ID OR @ID IS NULL) AND
    (Nome = @Nome OR @Nome IS NULL) AND
    (SobreNome = @SobreNome OR @SobreNome IS NULL) AND
    (Filhos = @Filhos OR @Filhos IS NULL) AND
    (AnoNascimento = @AnoNascimento OR @AnoNascimento IS NULL) AND
    (Cidade = @Cidade OR @Cidade IS NULL) AND
    (UF = @UF OR @UF IS NULL)

A semântica dessa versão da procedure é exatamente igual à versão anterior, mas os resultados são um pouco diferentes:

— Verifica quem nasceu em 1965
EXEC UspRecuperaPessoas @AnoNascimento = 1965

— Verifica quem tem o sobrenome Gonçalves
EXEC UspRecuperaPessoas @SobreNome = ‘Gonçalves’

— Verifica quem tem o sobrenome Mendes e nasceu em 1979
EXEC UspRecuperaPessoas @SobreNome = ‘Mendes’, @AnoNascimento = 1979

— Verifica quem tem 0 filhos e nasceu em Brasília
EXEC UspRecuperaPessoas @Filhos = 1, @Cidade = ‘Brasília’

De uma forma geral, todas as execuções tiveram o mesmo plano e dessa vez não foi um TABLE SCAN

A primeira execução utilizava como argumento de pesquisa a data de nascimento. O índice IX_AnoNascimento foi utilizado, mas ao contrário da consulta, a execução da SP fez um SCAN no índice e não um Seek no índice como feito anteriormente. Já a segunda execução utiliza como argumento de pesquisa o sobrenome e não o endereço, mas mesmo assim, a procedure mostrou o plano de execução com o índice IX_AnoNascimento. Isso deve-se ao fato de que a stored procedure compilou um plano de execução e o utilizou sucessivamente para cada uma das quatro execuções. Para uma consulta trivial, esse comportamento é até benéfico, mas para consultas parametrizadas, fica evidente que essa não é uma boa implementação. Para forçar com que a stored procedure produza um novo plano a cada execução devemos usar o WITH RECOMPILE. Normalmente essa é uma cláusula esquecida no momento de se construir uma procedure, mas veremos que ela pode fazer muita diferença.

— Cria uma procedure de pesquisa
ALTER PROCEDURE UspRecuperaPessoas
    @ID INT = NULL,
    @Nome VARCHAR(100) = NULL,
    @SobreNome VARCHAR(100) = NULL,
    @Filhos TINYINT = NULL,
    @AnoNascimento SMALLINT = NULL,
    @Cidade VARCHAR(100) = NULL,
    @UF CHAR(2) = NULL
WITH RECOMPILE
As

— Recupera Pessoas
SELECT * FROM Pessoas
WHERE
    (ID = @ID OR @ID IS NULL) AND
    (Nome = @Nome OR @Nome IS NULL) AND
    (SobreNome = @SobreNome OR @SobreNome IS NULL) AND
    (Filhos = @Filhos OR @Filhos IS NULL) AND
    (AnoNascimento = @AnoNascimento OR @AnoNascimento IS NULL) AND
    (Cidade = @Cidade OR @Cidade IS NULL) AND
    (UF = @UF OR @UF IS NULL)

Agora que a stored procedure foi alterada, vamos a uma nova tentativa:

— Verifica quem nasceu em 1965
EXEC UspRecuperaPessoas @AnoNascimento = 1965

— Verifica quem tem o sobrenome Gonçalves
EXEC UspRecuperaPessoas @SobreNome = ‘Gonçalves’

— Verifica quem tem o sobrenome Mendes e nasceu em 1979
EXEC UspRecuperaPessoas @SobreNome = ‘Mendes’, @AnoNascimento = 1979

— Verifica quem tem 0 filhos e nasceu em Brasília
EXEC UspRecuperaPessoas @Filhos = 1, @Cidade = ‘Brasília’

Com exceção das duas últimas execuções que resultam em TABLE SCAN, parece que o RECOMPILE gerou um plano mais adequado para as duas primeiras execuções:

Ainda que os índices corretos tenham sido utilizados, eles ainda não foram usados de forma performática, pois, ao invés do Seek (pesquisa) utilizada nas consultas, a stored procedure preferiu fazer um Scan (varredura) tornando o resultado menos eficiente. Podemos ver isso muito claramente com o uso do SET STATISTICS IO.

— Ativa as estatísticas de IO
SET STATISTICS IO ON

— Faz a consulta via Query
SELECT * FROM Pessoas WHERE AnoNascimento = 1965

— Executa a SP
EXEC UspRecuperaPessoas @AnoNascimento = 1965

Table ‘Pessoas’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Pessoas’. Scan count 1, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Os resultados mostram o quanto a stored procedure parametrizada foi menos eficiente. Na consulta são feitas três leituras lógicas, ou seja, três páginas de dados contra 24 páginas de dados no uso da stored procedure. O Seek no índice é 8 vezes mais eficiente em termos de I/O do que o SCAN. Vejamos agora o uso de uma procedure que usa SQL dinâmica.

ALTER PROCEDURE UspRecuperaPessoas
    @ID INT = NULL,
    @Nome VARCHAR(100) = NULL,
    @SobreNome VARCHAR(100) = NULL,
    @Filhos TINYINT = NULL,
    @AnoNascimento SMALLINT = NULL,
    @Cidade VARCHAR(100) = NULL,
    @UF CHAR(2) = NULL
As

DECLARE @cmdsql As NVARCHAR(4000)

SET @cmdsql = N’SELECT * FROM Pessoas’ +
                N’ WHERE 1 = 1′
      + CASE WHEN @ID IS NOT NULL THEN
          N’ AND ID = @pID’ ELSE N” END
      + CASE WHEN @Nome IS NOT NULL THEN
          N’ AND Nome = @pNome’ ELSE N” END
      + CASE WHEN @SobreNome IS NOT NULL THEN
          N’ AND SobreNome = @pSobreNome’ ELSE N” END
      + CASE WHEN @Filhos IS NOT NULL THEN
          N’ AND Filhos = @pFilhos’ ELSE N” END
      + CASE WHEN @AnoNascimento IS NOT NULL THEN
          N’ AND AnoNascimento = @pAnoNascimento’ ELSE N” END
      + CASE WHEN @Cidade IS NOT NULL THEN
          N’ AND Cidade = @pCidade’ ELSE N” END
      + CASE WHEN @UF IS NOT NULL THEN
          N’ AND UF = @pUF’ ELSE N” END;

EXEC sp_executesql @cmdsql,
N’@pID As INT, @pNome As VARCHAR(100), @pSobreNome VARCHAR(100), @pFilhos TINYINT, @pAnoNascimento SMALLINT, @pCidade VARCHAR(100), @pUF CHAR(2)’,
    @pID = @ID, @pNome = @Nome, @pSobreNome = @SobreNome,
    @pFilhos  = @Filhos, @pAnoNascimento = @AnoNascimento,
    @pCidade = @Cidade, @pUF = @UF

O uso da SQL dinâmica faz a montagem prévia do comando como um texto e posteriormente dispara sua execução através da instrução sp_executesql ou o EXEC(). Vejamos agora os resultados:

— Ativa as estatísticas de IO
SET STATISTICS IO ON

— Faz a consulta via Query
SELECT * FROM Pessoas WHERE AnoNascimento = 1965

— Executa a SP
EXEC UspRecuperaPessoas @AnoNascimento = 1965

Table ‘Pessoas’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Pessoas’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como podemos ver, dessa vez a consulta direta e a stored procedure tiveram exatamente o mesmo plano e comportaram-se de forma eficiente, ou seja, o índice foi utilizado e foi realizado um Seek e não um Scan demandando a leitura de apenas três páginas de dados. As demais execuções também terão os mesmos resultados das consultas. Isso ocorre porque a instrução é montada e executada dinamicamente e nessa montagem, a estrutura do comando é eficiente sem fazer uso de funções como o ISNULL ou operadores como o OR. O único efeito colateral dessa implementação é que o fato da montagem ser baseada em strings pode potencialmente introduzir riscos de SQL Injection e isso demandará do implementador a cautela em validar corretamente seus parâmetros de entrada bem alguns IFs para verificar se há alguma string maliciosa dentro do código. Acredito que seja um trabalho mínimo e realizado uma única vez em benefício das milhares de execuções de uma SP.

Após a demonstração dos cuidados que uma consulta parametrizada dentro de uma SP deva ter, indico as seguintes recomendações:

• Não utilize funções ISNULL contra colunas em uma cláusula WHERE
• Não utilize comparações do tipo Coluna = @Variavel OR @Variavel IS NULL
• Se sua procedure possibilita várias combinações, coloque o WITH RECOMPILE para não gerar planos inadequados
• Dê preferência a stored procedures dinâmicas nesse tipo de situação, mas previna-se contra o SQL Injection

Há muitas informações adicionais e uma explicação mais detalhada no livro do Itzik Ben Gan (Inside SQL Server 2005: T-SQL Programming). Há inclusive uma versão desse livro para SQL Server 2008. Recomendo a leitura.

[ ]s,

Gustavo

Algumas implementações multivaloradas com XML e Table Value Parameter – Parte II

Boa Noite Pessoal,

No artigo anterior, apresentei duas alternativas para lidar com implementações "Master Detail". A primeira foi mais trivial e envolveu várias iterações com o banco de dados. A segunda baseada em XML já optou por consolidar os dados e com uma única iteração com o banco de dados enviá-los em um formato XML. O próprio banco de dados efetuou o processo de Shredding convertendo os dados em XML para um formato tabular com linhas e colunas. Na parte II desse artigo demonstrarei uma terceira alternativa baseada no SQL Server 2008.

O SQL Server 2008 disponibilizou novas funcionalidades em relação ao Transact SQL. Uma dessas melhorias foi a evolução da variável do tipo TABLE para estruturas do conhecidas como User Defined Table Type & Table-Valued Parameters. Esse recurso permite que várias linhas sejam repassadas como uma única variável para stored procedures ou functions o que em outras palavras significa repassar uma tabela ou um resultset como parâmetro. O script a seguir exemplifica a criação de tipos TABLE:

— Muda o contexto de banco de dados
USE Pedidos;

— Cria um tipo tPedidos baseado na estrutura da tabela Pedidos
CREATE TYPE dbo.tPedidos As TABLE (
    DataPedido DATETIME2(0) NOT NULL, Frete SMALLMONEY NOT NULL,
    ClienteID INT NOT NULL, EnderecoID INT NOT NULL)

— Cria um tipo tPedidos baseado na estrutura da tabela Pedidos
CREATE TYPE dbo.tItens As TABLE (
    NumPedido INT NOT NULL, ProdutoID INT NOT NULL,
    Quantidade INT NOT NULL, PrecoUnitario SMALLMONEY NOT NULL)

A criação de dois tipos (tPedidos e tItens) permite sua plena reutilização em outras sessões e batch de comandos. A seguir são criadas e populadas duas variáveis baseadas nesses dois tipos.

— Declara uma variável do tipo tPedidos
DECLARE @Pedidos dbo.tPedidos

— Insere pedidos na variável @Pedidos
INSERT INTO @Pedidos VALUES (‘2010-05-17 20:45:25’,45.39,1,2)
INSERT INTO @Pedidos VALUES (‘2010-05-17 20:57:33’,95.33,3,1)

— Declara uma variável do tipo tItens
DECLARE @Itens dbo.tItens

— Insere itens na variável @Itens
INSERT INTO @Itens VALUES (1,11,21,15.02)
INSERT INTO @Itens VALUES (1,13,15,16.05)
INSERT INTO @Itens VALUES (1,12,33,29.55)
INSERT INTO @Itens VALUES (1,15,17,32.13)
INSERT INTO @Itens VALUES (1,16,23,29.99)
INSERT INTO @Itens VALUES (2,17,25,150.31)
INSERT INTO @Itens VALUES (2,11,20,290.00)
INSERT INTO @Itens VALUES (2,18,16,324.55)
INSERT INTO @Itens VALUES (2,13,34,456.12)
INSERT INTO @Itens VALUES (2,14,43,399.99)
INSERT INTO @Itens VALUES (2,10,19,238.45)

— Exibe os pedidos
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM @Pedidos

— Exibe os itens
SELECT NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM @Itens

DataPedido	Frete	ClienteID	EnderecoID
2010-05-17 20:45:25	45,39	1	2
2010-05-17 20:57:33	95,33	3	1

NumPedido	ProdutoID	Quantidade	PrecoUnitario
1	11	21	15,02
1	13	15	16,05
1	12	33	29,55
1	15	17	32,13
1	16	23	29,99
2	17	25	150,31
2	11	20	290,00
2	18	16	324,55
2	13	34	456,12
2	14	43	399,99
2	10	19	238,45

Com exceção da palavra "reusabilidade" não há absolutamente nenhuma diferença entre User Defined Table Type e variáveis do tipo TABLE. O script poderia ser substituído sem perda de desempenho ou diferenças no resultado final por variáveis do tipo TABLE. De fato variáveis do tipo TABLE e User Defined Table Type compartilham características em comum (não é à toa que alguns costumam dizer que User Defined Table Type são nada mais que variáveis do tipo TABLE "turbinadas"), mas vejamos algumas características mais particulares a estruturas User Defined Table Type.

— Cria uma função que recebe uma "tabela" do tipo tPedidos como parâmetro
CREATE FUNCTION dbo.ExibePedidos (@Peds dbo.tPedidos READONLY)
RETURNS TABLE
RETURN (SELECT DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos)

— Declara uma variável do tipo tPedidos
DECLARE @Pedidos dbo.tPedidos

— Insere pedidos na variável @Pedidos
INSERT INTO @Pedidos VALUES (‘2010-05-17 20:45:25’,45.39,1,2)
INSERT INTO @Pedidos VALUES (‘2010-05-17 20:57:33’,95.33,3,1)

— Faz a chamada à função ExibePedidos
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM dbo.ExibePedidos(@Pedidos)

A possibilidade de passar uma tabela como parâmetro é uma característica exclusiva a User Defined Table Type. Quando essas estruturas são repassadas como parâmetros para stored procedures e functions elas são conhecidas como Table Value Parameters. O exemplo acima declarou uma variável @Pedidos do tipo tPedidos, populou-a com vários registros e repassou-a para uma função que retornou seu conteúdo. No exemplo utilizado até então, pode-se utilizar uma única stored procedure para efetuar o cadastro recebendo apenas dois parâmetros (um do tipo tPedidos e outro do tipo tItens). Algo parecido com o exemplo a seguir:

— Cria uma única Stored Procedure para inserção
CREATE PROCEDURE XXXXXX
    @Pedidos dbo.tPedidos READONLY,
    @Itens dbo.tItens READONLY
As

— Insere os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM @Pedidos

— Insere os Itens
INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
SELECT NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM @Itens

Da forma como está proposta, bastaria informar as variáveis @Pedidos e @Itens populadas para a stored procedure e ela faria a carga nas respectivas tabelas. Não faria nenhum sentido popular as variáveis com TSQL e informá-las para a stored procedure. Os parâmetros seriam populados pelo próprio ADO.NET com o uso de objetos do tipo SqlDbType.Structured. Uma pequena alteração deve ser realizada para capturar o numero do pedido gerado pela propriedade Identity.

— Muda o contexto de banco de dados
USE Pedidos;

— Elimina todos os registros de ambas as tabelas e reinicia os Identities
DELETE FROM Itens
DELETE FROM Pedidos
DBCC CHECKIDENT(Itens,‘RESEED’,0)
DBCC CHECKIDENT(Pedidos,‘RESEED’,0)

— Elimina a stored procedure UspInserePedidos
DROP PROCEDURE UspInserePedidos

— Cria uma única Stored Procedure para inserção
CREATE PROCEDURE UspInserePedidos
    @Pedidos dbo.tPedidos READONLY,
    @Itens dbo.tItens READONLY
As

— Insere os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM @Pedidos

— Insere os Itens
INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
SELECT SCOPE_IDENTITY(), ProdutoID, Quantidade, PrecoUnitario FROM @Itens

Dessa vez, ao invés de popular a aplicação a partir de um arquivo XML com vários pedidos e itens, farei a utilização de apenas um pedido e seus respectivos itens diretamente na aplicação (de forma a evitar a criação de um formulário para entrada de dados).

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;

namespace CadastraPedido
{
    class CP
    {
        static void Main(string[] args)
        {
            Console.WriteLine(@"Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (TVP)");
            Console.ReadLine();

            // Cria um DataSet
            DataSet ds = new DataSet();

            // Adiciona duas DataTables (Pedido e Itens) ao DataSet
            ds.Tables.Add("Pedidos");
            ds.Tables.Add("Itens");

            // Adiciona as colunas de Pedidos
            ds.Tables["Pedidos"].Columns.Add("DataPedido", System.Type.GetType("System.DateTime"));
            ds.Tables["Pedidos"].Columns.Add("Frete", System.Type.GetType("System.Double"));
            ds.Tables["Pedidos"].Columns.Add("ClienteID", System.Type.GetType("System.Int32"));
            ds.Tables["Pedidos"].Columns.Add("EnderecoID", System.Type.GetType("System.Int32"));

            // Adiciona as colunas de Itens
            ds.Tables["Itens"].Columns.Add("NumPedido", System.Type.GetType("System.Int32"));
            ds.Tables["Itens"].Columns.Add("ProdutoID", System.Type.GetType("System.Int32"));
            ds.Tables["Itens"].Columns.Add("Quantidade", System.Type.GetType("System.Int32"));
            ds.Tables["Itens"].Columns.Add("PrecoUnitario", System.Type.GetType("System.Double"));

            // Adiciona um pedido
            DateTime DataPedido = DateTime.Parse("2010-05-17 20:45:25");
            Double Frete = 45.39;
            Int32 ClienteID = 1;
            Int32 EnderecoID = 2;

            ds.Tables["Pedidos"].Rows.Add(DataPedido, Frete, ClienteID, EnderecoID);

            // Adiciona os itens do pedido
            // Declara e inicializa os valores
            Int32 NumPedido = 0; // Será sobrescrito no banco de dados
            Int32 ProdutoID = 0;
            Int32 Quantidade = 0;
            Double PrecoUnitario = 0;

            // Insere o primeiro Item
            ProdutoID = 11;
            Quantidade = 21;
            PrecoUnitario = 15.02;

            ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);

            // Insere o segundo Item
            ProdutoID = 13;
            Quantidade = 15;
            PrecoUnitario = 16.05;

            ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);

            // Insere o terceiro Item
            ProdutoID = 12;
            Quantidade = 33;
            PrecoUnitario = 29.55;

            ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);

            // Insere o quarto Item
            ProdutoID = 15;
            Quantidade = 17;
            PrecoUnitario = 32.13;

            ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);

            // Insere o quinto Item
            ProdutoID = 16;
            Quantidade = 23;
            PrecoUnitario = 29.99;

            ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);

            // Comita as novas linhas
            ds.AcceptChanges();

            // Instancia uma conexão com o banco de dados para a base Pedidos no servidor Local
            SqlConnection sc = new SqlConnection(@"SERVER=LocalHost;Database=Pedidos;Integrated Security=true");

            // Instancia um comando para utilizar a stored procedure UspInserePedidos
            SqlCommand cmdPed = sc.CreateCommand();
            cmdPed.CommandType = CommandType.StoredProcedure;
            cmdPed.CommandText = "UspInserePedidos";

            // Adiciona os parâmetro Pedidos e Itens
            cmdPed.Parameters.Add("@Pedidos", SqlDbType.Structured);
            cmdPed.Parameters.Add("@Itens", SqlDbType.Structured);
            cmdPed.Parameters["@Pedidos"].Value = ds.Tables["Pedidos"];
            cmdPed.Parameters["@Itens"].Value = ds.Tables["Itens"];

            // Abre a conexão
            sc.Open();

            // Executa a Stored Procedure
            cmdPed.ExecuteNonQuery();

            // Fecha a conexão
            sc.Close();

            // Elimina a conexão
            sc.Dispose();

            Console.WriteLine(@"Os pedidos e itens foram cadastrados com êxito");
            Console.WriteLine(@"Pressione qualquer tecla para fechar esse aplicativo");
            Console.ReadLine();
        }
    }
}

Os passos para a compilação do código são exatamente os mesmos utilizados na primeira parte do artigo. Da mesma forma que a versão em XML foi alterada, fiz apenas uma modificação no nome do arquivo .cs e no .exe para não conflitar com os arquivos anteriores.

Command Prompt

C:\>cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /out:C:\CadastraPedidoTVP.exe C:\CadastraPedidoTVP.cs Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.3053 for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727 Copyright (C) Microsoft Corporation 2001-2005. All rights reserved. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>cd\ C:\>CadastraPedidoTVP.exe Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (TVP) Os pedidos e itens foram cadastrados com êxito Pressione qualquer tecla para fechar esse aplicativo C:\>

USE Pedidos;

SELECT NumPedido, DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos;
SELECT ItemID, NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM Itens;

NumPedido	DataPedido	Frete	ClienteID	EnderecoID
1	2010-05-17 20:45:25	45,39	1	2

ItemID	NumPedido	ProdutoID	Quantidade	PrecoUnitario
01	1	11	21	15,02
02	1	13	15	16,05
03	1	12	33	29,55
04	1	15	17	32,13
05	1	16	23	29,99

Essa abordagem funciona perfeitamente para estruturas onde apenas um pedido é informado ou chaves naturais são utilizadas, pois nesse caso não há grandes dificuldades no mapeamento dos relacionamentos no banco de dados. Essa é a situação mais comum, pois, normalmente em um formulário de entrada de dados informa-se um pedido de cada vez. Se os dados do XML (dois pedidos e onze itens) fossem utilizados não seria tão fácil já que colunas do tipo Identity são utilizadas. Como obter os Identities gerados na tabela de pedidos e repassá-los para os itens se as inserções são realizadas em sequência ? Da forma como foi proposto, fatalmente os relacionamentos seriam perdidos, pois, não seria possível recuperar o número do pedido e repassá-lo antes da inserção dos itens.

Resolver esse pequeno detalhe não é algo trivial. Se na implementação com XML várias soluções estavam disponíveis, o mesmo não é verdade com o uso de Table Value Parameters, pois, a inserção é direta com pouca margem a manipulação. Ao meu ver o uso de chaves alternativas seria um caminho (no caso de chaves naturais não estarem disponíveis). Seria possível por exemplo replicar na tabela de Itens do DataSet, as colunas DataPedido e ClienteID para que elas fossem submetidas junto com os demais dados. A estrutura tItens poderia contemplar essas colunas e no momento da inserção em Itens, um JOIN seria realizado para recuperar o Numero do Pedido. Algo semelhante à stored procedure abaixo:

— Recria a Stored Procedure UspInserePedidos
CREATE PROCEDURE XXXXXX
    @Pedidos dbo.tPedidos READONLY,
    @Itens dbo.tItens READONLY
As

— Insere os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM @Pedidos

— Insere os Itens
INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
SELECT NumPedido, ProdutoID, Quantidade, PrecoUnitario
FROM @Itens As I
INNER JOIN Pedidos As P ON I.DataPedido = P.DataPedido AND I.ClienteID = I.ClienteID

Nos exemplos anteriores, os dados foram extraídos de um documento XML. Nesse caso, podemos utilizar as colunas de relacionamento que são criadas automaticamente quando o XML é importado para um DataSet e possui aninhamento. Essa combinação com chaves alternativas permite o cadastro sem a perda das referências.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;

namespace CadastraPedido
{
    class CP
    {
        static void Main(string[] args)
        {
            Console.WriteLine(@"Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (TVP V2)");
            Console.ReadLine();

            // Instancia um documento XML
            XmlDocument xd = new XmlDocument();

            // Carrega o documento XML para o objeto xd
            xd.Load(@"C:\Pedidos.xml");

            // Converte o documento XML para um DataSet
            DataSet ds = new DataSet();
            ds.ReadXml(new XmlNodeReader(xd));

            // Instancia uma conexão com o banco de dados para a base Pedidos no servidor Local
            SqlConnection sc = new SqlConnection(@"SERVER=LocalHost;Database=Pedidos;Integrated Security=true");

            // Instancia um comando para utilizar a stored procedure UspInserePedidos
            SqlCommand cmdPed = sc.CreateCommand();
            cmdPed.CommandType = CommandType.StoredProcedure;
            cmdPed.CommandText = "UspInserePedidos";

            // Adiciona os parâmetro Pedidos e Itens
            cmdPed.Parameters.Add("@Pedidos", SqlDbType.Structured);
            cmdPed.Parameters.Add("@Itens", SqlDbType.Structured);
            cmdPed.Parameters["@Pedidos"].Value = ds.Tables["Pedido"];
            cmdPed.Parameters["@Itens"].Value = ds.Tables["Item"];

            // Abre a conexão
            sc.Open();

            // Executa a Stored Procedure
            cmdPed.ExecuteNonQuery();

            // Fecha a conexão
            sc.Close();

            // Elimina a conexão
            sc.Dispose();

            Console.WriteLine(@"Os pedidos e itens foram cadastrados com êxito");
            Console.WriteLine(@"Pressione qualquer tecla para fechar esse aplicativo");
            Console.ReadLine();
        }
    }
}

No SQL Server, os tipos tPedidos e tItens foram alterados para ter exatamente o mesmo esquema que as tabelas do DataSet (inclusive a mesma ordem das colunas).

— Muda o contexto de banco de dados
USE Pedidos;

— Elimina todos os registros de ambas as tabelas e reinicia os Identities
DELETE FROM Itens
DELETE FROM Pedidos
DBCC CHECKIDENT(Itens,‘RESEED’,0)
DBCC CHECKIDENT(Pedidos,‘RESEED’,0)

— Elimina a stored procedure UspInserePedidos
DROP PROCEDURE UspInserePedidos

— Elimina a function ExibePedidos
DROP FUNCTION dbo.ExibePedidos

— Elimina os tipos Tipos tPedidos e tItens
DROP TYPE dbo.tPedidos
DROP TYPE dbo.tItens

— Recria os tipos tPedidos e tItens para refletir a estrutura do XML no DataSet
CREATE TYPE dbo.tPedidos As TABLE (Pedido_ID INT NOT NULL,
    DataPedido DATETIME2(0) NOT NULL, Cliente INT NOT NULL,
    EnderecoID INT NOT NULL, Frete SMALLMONEY NOT NULL)

CREATE TYPE dbo.tItens As TABLE (
    ProdutoID INT NOT NULL, Preco SMALLMONEY NOT NULL,
    Quantidade INT NOT NULL, Pedido_ID INT NOT NULL)

— Cria uma única Stored Procedure para inserção
CREATE PROCEDURE UspInserePedidos
    @Pedidos dbo.tPedidos READONLY,
    @Itens dbo.tItens READONLY
As

— Insere os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT DataPedido, Frete, Cliente, EnderecoID FROM @Pedidos

— Insere os Itens
INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
SELECT NumPedido, ProdutoID, Quantidade, Preco
FROM @Itens As I
INNER JOIN @Pedidos As P ON I.Pedido_ID = P.Pedido_ID
INNER JOIN Pedidos As Ped ON P.DataPedido = Ped.DataPedido AND P.Cliente = Ped.ClienteID

A compilação do código é idêntica às realizadas anteriormente. A única alteração que foi feita foi nos nomes dos arquivo cs e exe bem como a chamada do executável conforme o prompt abaixo:

Command Prompt

C:\>cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /out:C:\CadastraPedidoTVP_v2.exe C:\CadastraPedidoTVP_v2.cs Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.3053 for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727 Copyright (C) Microsoft Corporation 2001-2005. All rights reserved. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>cd\ C:\>CadastraPedidoTVP_v2.exe Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (TVP) Os pedidos e itens foram cadastrados com êxito Pressione qualquer tecla para fechar esse aplicativo C:\>

Uma breve consulta a partir do Management Studio mostra que o cadastro foi feito com sucesso:

USE Pedidos;

SELECT NumPedido, DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos;
SELECT ItemID, NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM Itens;

NumPedido	DataPedido	Frete	ClienteID	EnderecoID
1	2010-05-17 20:45:25	45,39	1	2
2	2010-05-17 20:57:33	95,33	3	1

ItemID	NumPedido	ProdutoID	Quantidade	PrecoUnitario
01	1	11	21	15,02
02	1	13	15	16,05
03	1	12	33	29,55
04	1	15	17	32,13
05	1	16	23	29,99
06	2	17	25	150,31
07	2	11	20	290,00
08	2	18	16	324,55
09	2	13	34	456,12
10	2	14	43	399,99
11	2	10	19	238,45

Como pode ser observado, a presença de colunas Identity pode complicar um pouco algumas implementações e nesse ponto chave naturais pode ser mais interessante. Felizmente a presença de chaves alternativas viabiliza o uso de Identities mesmo com um pouco mais de código. O uso do tipo XML e (ou) de Table Value Parameters são soluções robustas para implementações "Master Detail", pois, estão preparadas para qualquer cardinalidade, ou seja, funcionam bem independente da quantidade de registros "Details" e conseguem repassar os dados com uma única iteração.

[ ]s,

Gustavo

Algumas implementações multivaloradas com XML e Table Value Parameter – Parte I

Boa Tarde Pessoal,

Um dia desses, um aluno me perguntou se o SQL Server disponibiliza alguma forma de tratar implementações "Master Detail" em uma única iteração sem a necessidade de loops para os registros "Detail" evitando assim várias trocas de contexto entre a aplicação e o banco de dados. Achei a pergunta bastante interessante e felizmente há algumas alternativas para lidar com essas situações. Antes de apresentá-las é importante exemplificar as dificuldades comuns de cadastro. Para isso utilizarei uma pequena aplicação em .NET. Meus conhecimentos nessa plataforma são um pouco limitados (tirei apenas o MCAD no .NET Framework 1.1), mas vejamos a construção de um aplicativo em C# (VS 2008) para simular o cadastro de um pedido e seus Itens. Para evitar a construção de um formulário de entrada de dados, farei a importação de dados a partir de um arquivo XML e a gravação através de stored procedures.

Arquivo XML de Pedidos

<?xml version="1.0"?>
 <Pedidos>
  <Pedido DataPedido="2010-05-17T20:45:25" Cliente="1" EnderecoID="2" Frete="45,39">
   <Item ProdutoID="11" Preco="15,02" Quantidade="21"/>
   <Item ProdutoID="13" Preco="16,05" Quantidade="15"/>
   <Item ProdutoID="12" Preco="29,55" Quantidade="33"/>
   <Item ProdutoID="15" Preco="32,13" Quantidade="17"/>
   <Item ProdutoID="16" Preco="29,99" Quantidade="23"/>
  </Pedido>
  <Pedido DataPedido="2010-05-17T20:57:33" Cliente="3" EnderecoID="1" Frete="95,33">
   <Item ProdutoID="17" Preco="150,31" Quantidade="25"/>
   <Item ProdutoID="11" Preco="290,00" Quantidade="20"/>
   <Item ProdutoID="18" Preco="324,55" Quantidade="16"/>
   <Item ProdutoID="13" Preco="456,12" Quantidade="34"/>
   <Item ProdutoID="14" Preco="399,99" Quantidade="43"/>
   <Item ProdutoID="10" Preco="238,45" Quantidade="19"/>
  </Pedido>
 </Pedidos>

A estrutura do arquivo XML é bem intuitiva. Há um nó root (Pedidos) que lista todos os pedidos a serem processados. Os nós do tipo "Pedido" tem os dados do Pedido e o nós do tipo "Item" possuem os itens de cada pedidos. Em princípio o arquivo foi salvo como C:\Pedidos.xml podendo ser alterado caso necessários. Os scripts abaixo criam uma base de dados, as tabelas e stored procedures necessárias.

Criação do Banco de Dados

— Cria uma base de dados
CREATE DATABASE Pedidos

— Muda o contexto de banco de dados
USE Pedidos

— Cria as tabelas de Pedidos e Itens
CREATE TABLE Pedidos (NumPedido INT NOT NULL IDENTITY(1,1),
    DataPedido DATETIME2(0) NOT NULL, Frete SMALLMONEY NOT NULL,
    ClienteID INT NOT NULL, EnderecoID INT NOT NULL,
    CONSTRAINT PK_Pedidos PRIMARY KEY (NumPedido))

CREATE TABLE Itens (
    ItemID INT NOT NULL IDENTITY(1,1), NumPedido INT NOT NULL,
    ProdutoID INT NOT NULL, Quantidade INT NOT NULL,
    PrecoUnitario SMALLMONEY NOT NULL,
    CONSTRAINT FK_Itens_Pedidos FOREIGN KEY (NumPedido)
    REFERENCES Pedidos (NumPedido))

— Cria a Stored Procedure para Inserção de Pedidos
CREATE PROCEDURE UspInserePedidos
    @DataPedido DATETIME2(0),
    @Frete SMALLMONEY,
    @ClienteID INT,
    @EnderecoID INT,
    @NumPedido INT OUTPUT
AS
    — Faz a gravação na tabela de pedidos
    INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
    VALUES (@DataPedido, @Frete, @ClienteID, @EnderecoID)

    — Atribui o valor gerado pela coluna Identity na variável @NumPedido
    SET @NumPedido = SCOPE_IDENTITY()

— Cria a Stored Procedure para Inserção de Itens
CREATE PROCEDURE UspInsereItens
    @NumPedido INT,
    @ProdutoID INT,
    @Quantidade INT,
    @PrecoUnitario SMALLMONEY
AS
    — Faz a gravação na tabela de itens
    INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
    VALUES (@NumPedido, @ProdutoID, @Quantidade, @PrecoUnitario)

Agora que o banco de dados está pronto, o próximo passo é criar uma aplicação para carregar o arquivo XML no banco de dados.

Console Application em C# para gravação de pedidos

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;

namespace CadastraPedido
{
    class CP
    {
        static void Main(string[] args)
        {
            Console.WriteLine(@"Pressione qualquer tecla para fazer o cadastro dos pedidos e itens");
            Console.ReadLine();

            // Instancia um documento XML
            XmlDocument xd = new XmlDocument();

            // Carrega o documento XML para o objeto xd
            xd.Load(@"C:\Pedidos.xml");

            // Converte o documento XML para um DataSet
            DataSet ds = new DataSet();
            ds.ReadXml(new XmlNodeReader(xd));

            // Instancia uma conexão com o banco de dados para a base Pedidos no servidor Local
            SqlConnection sc = new SqlConnection(@"SERVER=LocalHost;Database=Pedidos;Integrated Security=true");

            // Instancia um comando para utilizar a stored procedure UspInserePedidos
            SqlCommand cmdPed = sc.CreateCommand();
            cmdPed.CommandType = CommandType.StoredProcedure;
            cmdPed.CommandText = "UspInserePedidos";

            // Adiciona os parâmetros necessários
            cmdPed.Parameters.Add("@DataPedido", SqlDbType.DateTime2);
            cmdPed.Parameters.Add("@Frete", SqlDbType.SmallMoney);
            cmdPed.Parameters.Add("@ClienteID", SqlDbType.Int);
            cmdPed.Parameters.Add("@EnderecoID", SqlDbType.Int);

            // Cria uma variável para capturar o ID do Pedido
            Int32 NumPedido = 0;
            cmdPed.Parameters.Add("@NumPedido", SqlDbType.Int);
            cmdPed.Parameters["@NumPedido"].Direction = ParameterDirection.Output;

            // Instancia um comando para utilizar a stored procedure UspInsereItens
            SqlCommand cmdItem = sc.CreateCommand();
            cmdItem.CommandType = CommandType.StoredProcedure;
            cmdItem.CommandText = "UspInsereItens";

            // Adiciona os parâmetros
            cmdItem.Parameters.Add("@NumPedido", SqlDbType.Int);
            cmdItem.Parameters.Add("@ProdutoID", SqlDbType.Int);
            cmdItem.Parameters.Add("@Quantidade", SqlDbType.Int);
            cmdItem.Parameters.Add("@PrecoUnitario", SqlDbType.SmallMoney);

            // Abre a conexão
            sc.Open();

            // Para cada Pedido, registre-o
            foreach (DataRow drPed in ds.Tables["Pedido"].Rows)
            {
                cmdPed.Parameters["@DataPedido"].Value = DateTime.Parse(drPed["DataPedido"].ToString());
                cmdPed.Parameters["@Frete"].Value = Double.Parse(drPed["Frete"].ToString());
                cmdPed.Parameters["@ClienteID"].Value = Int32.Parse(drPed["Cliente"].ToString());
                cmdPed.Parameters["@EnderecoID"].Value = Int32.Parse(drPed["EnderecoID"].ToString());

                // Executa SP
                cmdPed.ExecuteNonQuery();

                // Captura o ID
                NumPedido = Int32.Parse(cmdPed.Parameters["@NumPedido"].Value.ToString());

                // Para cada Item, registre-o
                foreach (DataRow drItem in drPed.GetChildRows(ds.Relations[0]))
                {
                    cmdItem.Parameters["@NumPedido"].Value = NumPedido;
                    cmdItem.Parameters["@ProdutoID"].Value = Int32.Parse(drItem["ProdutoID"].ToString());
                    cmdItem.Parameters["@Quantidade"].Value = Int32.Parse(drItem["Quantidade"].ToString());
                    cmdItem.Parameters["@PrecoUnitario"].Value = Double.Parse(drItem["Preco"].ToString());

                    // Executa a SP
                    cmdItem.ExecuteNonQuery();
                }
            }

            // Fecha a conexão
            sc.Close();

            // Elimina a conexão
            sc.Dispose();

            Console.WriteLine(@"Os pedidos e itens foram cadastrados com êxito");
            Console.WriteLine(@"Pressione qualquer tecla para fechar esse aplicativo");
            Console.ReadLine();
        }
    }
}

Antes de efetuar a compilação do código é necessário fazer as alterações para o caminho do arquivo XML (C:\Pedidos.xml) e o servidor utilizado em questão (utilizei a instância local default). Se os parâmetros utilizados não forem os mesmos é preciso fazer alterações no código. Os passos a seguir produzem o executável.

• Abra o Notepad
• Cole o código
• Salve o arquivo em algum local com a extensão .cs (Ex: C:\CadastraPedido.cs)
• Abra um prompt de comando
• Navegue até o diretório C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 (ou superior se houver .NET Framework mais recente instalado)
• Digite csc /out:C:\CadastraPedido.exe C:\CadastraPedido.cs e pressione ENTER (pressupondo que o arquivo seja C:\CadastraPedido.cs)

Após a compilação do código e geração do executável, basta abrir um prompt de comando e executá-lo:

Command Prompt

C:\>cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /out:C:\CadastraPedido.exe C:\CadastraPedido.cs Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.3053 for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727 Copyright (C) Microsoft Corporation 2001-2005. All rights reserved. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>cd\ C:\>CadastraPedido.exe Pressione qualquer tecla para fazer o cadastro dos pedidos e itens Os pedidos e itens foram cadastrados com êxito Pressione qualquer tecla para fechar esse aplicativo C:\>

Uma breve consulta a partir do Management Studio mostra que o cadastro foi feito com sucesso:

USE Pedidos;

SELECT NumPedido, DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos;
SELECT ItemID, NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM Itens;

NumPedido	DataPedido	Frete	ClienteID	EnderecoID
1	2010-05-17 20:45:25	45,39	1	2
2	2010-05-17 20:57:33	95,33	3	1

ItemID	NumPedido	ProdutoID	Quantidade	PrecoUnitario
01	1	11	21	15,02
02	1	13	15	16,05
03	1	12	33	29,55
04	1	15	17	32,13
05	1	16	23	29,99
06	2	17	25	150,31
07	2	11	20	290,00
08	2	18	16	324,55
09	2	13	34	456,12
10	2	14	43	399,99
11	2	10	19	238,45

A aplicação fez o cadastro dos pedidos e dos respectivos itens. Normalmente essa situação pode-se utilizar do método Update de uma classe SqlAdapter passando-se um objeto do tipo DataSet como parâmetro. Entretanto a presença de valores autoincrementáveis pode trazer certa dificuldade para utilizar apenas esse método como atualização de tabelas em uma relação Master Detail. A Microsoft possui um artigo detalhando essas dificuldades e como contorná-las em: Retrieving Identity or Autonumber Values (ADO.NET), mas o mesmo mostra como recuperar colunas autoincrementáveis, mas não necessariamente como utilizar essas colunas e atualizá-las em tabelas Master Details. Existem outras alternativas com o DataSet, mas o fato é que dificilmente se escapa da necessidade de efetuar um loop para as tabelas filhas como foi feito no exemplo.

O uso do tipo XML

O uso de um tipo semiestruturado como o XML permite a representação dos dados em um formato hierárquico em oposição ao uso de tabelas "Pai" e "Filho" ligadas por um relacionamento (exatamente como demonstrado no documento XML no início do artigo). A passagem dos dados via XML permite que a aplicação envie os dados diretamente ao banco de dados com uma única passagem de parâmetro evitando assim os loops e trocas de contexto entre o banco e a aplicação. Caberá ao banco transformar o XML em um formato relacional (Shredding XML). Para isso é necessário efetuar algumas alterações no documento XML (trocar as vírgulas por pontos), nas stored procedures utilizadas e na aplicação.

<?xml version="1.0"?>
 <Pedidos>
  <Pedido DataPedido="2010-05-17T20:45:25" Cliente="1" EnderecoID="2" Frete="45.39">
   <Item ProdutoID="11" Preco="15.02" Quantidade="21"/>
   <Item ProdutoID="13" Preco="16.05" Quantidade="15"/>
   <Item ProdutoID="12" Preco="29.55" Quantidade="33"/>
   <Item ProdutoID="15" Preco="32.13" Quantidade="17"/>
   <Item ProdutoID="16" Preco="29.99" Quantidade="23"/>
  </Pedido>
  <Pedido DataPedido="2010-05-17T20:57:33" Cliente="3" EnderecoID="1" Frete="95.33">
   <Item ProdutoID="17" Preco="150.31" Quantidade="25"/>
   <Item ProdutoID="11" Preco="290.00" Quantidade="20"/>
   <Item ProdutoID="18" Preco="324.55" Quantidade="16"/>
   <Item ProdutoID="13" Preco="456.12" Quantidade="34"/>
   <Item ProdutoID="14" Preco="399.99" Quantidade="43"/>
   <Item ProdutoID="10" Preco="238.45" Quantidade="19"/>
  </Pedido>
 </Pedidos>

Os scripts a seguir excluem todos os itens e pedidos das tabelas e zera suas respectivas sequências. Posteriormente as stored procedures de inserção de pedidos e itens são excluídas e substituídas por uma única stored procedure que faz a inserção nas duas tabelas.

— Muda o contexto de banco de dados
USE Pedidos;

— Elimina todos os registros de ambas as tabelas e reinicia os Identities
DELETE FROM Itens
DELETE FROM Pedidos
DBCC CHECKIDENT(Itens,‘RESEED’,0)
DBCC CHECKIDENT(Pedidos,‘RESEED’,0)

— Elimina as duas stored procedures
DROP PROCEDURE UspInsereItens
DROP PROCEDURE UspInserePedidos

— Cria uma única Stored Procedure para inserção
CREATE PROCEDURE UspInserePedidos
    @Pedidos XML
AS

— Extrai e grava os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT
    P.p.value(‘@DataPedido’,‘DATETIME2(0)’) As DataPedido,
    P.p.value(‘@Frete’,‘SMALLMONEY’) As Frete,
    P.p.value(‘@Cliente’,‘INT’) As ClienteID,
    P.p.value(‘@EnderecoID’,‘INT’) As EnderecoID
FROM @Pedidos.nodes(‘/Pedidos/Pedido’) P(p)

— Insere e grava os itens
— Assume-se que um cliente jamais fará dois pedidos exatamente no mesmo momento
— A combinação DataPedido e ClienteID serve como chave alternativa para obter o ID do pedido
;WITH ItensColetados As (
SELECT
    P.i.value(‘../@DataPedido’,‘DATETIME2(0)’) As DataPedido,
    P.i.value(‘../@Cliente’,‘INT’) As ClienteID,
    P.i.value(‘@ProdutoID’,‘INT’) As ProdutoID,
    P.i.value(‘@Preco’,‘SMALLMONEY’) As PrecoUnitario,
    P.i.value(‘@Quantidade’,‘INT’) As Quantidade
FROM @Pedidos.nodes(‘/Pedidos/Pedido/Item’) P(i))

INSERT INTO Itens (NumPedido, ProdutoID, PrecoUnitario, Quantidade)
SELECT
    NumPedido, ProdutoID, PrecoUnitario, Quantidade
FROM ItensColetados As IC
INNER JOIN Pedidos As Ped ON
    IC.DataPedido = Ped.DataPedido AND IC.ClienteID = Ped.ClienteID

Agora que os objetos de persistência no banco de dados foram devidamente alterados, resta adaptar a aplicação para utilizá-los.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;

namespace CadastraPedido
{
    class CP
    {
        static void Main(string[] args)
        {
            Console.WriteLine(@"Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (XML)");
            Console.ReadLine();

            // Instancia um documento XML
            XmlDocument xd = new XmlDocument();

            // Carrega o documento XML para o objeto xd
            xd.Load(@"C:\Pedidos.xml");

            // Converte o documento XML para um DataSet
            DataSet ds = new DataSet();
            ds.ReadXml(new XmlNodeReader(xd));

            // Converte o DataSet para XML
            String xmlPed = ds.GetXml().ToString();

            // Instancia uma conexão com o banco de dados para a base Pedidos no servidor Local
            SqlConnection sc = new SqlConnection(@"SERVER=LocalHost;Database=Pedidos;Integrated Security=true");

            // Instancia um comando para utilizar a stored procedure UspInserePedidos
            SqlCommand cmdPed = sc.CreateCommand();
            cmdPed.CommandType = CommandType.StoredProcedure;
            cmdPed.CommandText = "UspInserePedidos";

            // Adiciona o parâmetro XML
            cmdPed.Parameters.Add("@Pedidos", SqlDbType.Xml);
            cmdPed.Parameters["@Pedidos"].Value = xmlPed;

            // Abre a conexão
            sc.Open();

            // Executa a Stored Procedure de cadastro de pedidos
            cmdPed.ExecuteNonQuery();

            // Fecha a conexão
            sc.Close();

            // Elimina a conexão
            sc.Dispose();

            Console.WriteLine(@"Os pedidos e itens foram cadastrados com êxito");
            Console.WriteLine(@"Pressione qualquer tecla para fechar esse aplicativo");
            Console.ReadLine();
        }
    }
}

Há um passo desse código que gostaria de comentar. O início do código converte o arquivo XML para um DataSet e o passo seguinte converte o DataSet para XML. Não seria mais fácil então utilizar diretamente o XML a partir do arquivo ? Sim, não parece fazer sentido extrair dados em XML para o DataSet e posteriormente convertê-los para XML novamente, mas esses passos foram colocados propositalmente. O uso de um arquivo XML deve-se apenas para evitar a criação de um formulário de entrada de dados. Em uma aplicação real, provavelmente o uso Windows Form (ou Web Form) incluiria os dados em um DataSet e posteriormente faria a conversão para XML. Os passos para a compilação do código são exatamente os mesmos utilizados no início do artigo. Fiz apenas uma alteração no nome do arquivo .cs e no .exe para não conflitar com os arquivos anteriores. Os resultados são os mesmos da implementação anterior:

Command Prompt

C:\>cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /out:C:\CadastraPedidoXML.exe C:\CadastraPedidoXML.cs Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.3053 for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727 Copyright (C) Microsoft Corporation 2001-2005. All rights reserved. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>cd\ C:\>CadastraPedidoXML.exe Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (XML) Os pedidos e itens foram cadastrados com êxito Pressione qualquer tecla para fechar esse aplicativo C:\>

USE Pedidos;

SELECT NumPedido, DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos;
SELECT ItemID, NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM Itens;

NumPedido	DataPedido	Frete	ClienteID	EnderecoID
1	2010-05-17 20:45:25	45,39	1	2
2	2010-05-17 20:57:33	95,33	3	1

ItemID	NumPedido	ProdutoID	Quantidade	PrecoUnitario
01	1	11	21	15,02
02	1	13	15	16,05
03	1	12	33	29,55
04	1	15	17	32,13
05	1	16	23	29,99
06	2	17	25	150,31
07	2	11	20	290,00
08	2	18	16	324,55
09	2	13	34	456,12
10	2	14	43	399,99
11	2	10	19	238,45

O uso XML mostrou-se eficaz no cadastro de pedidos e seus respectivos itens. Além de produzir o mesmo resultado, foi capaz de fazer todo o procedimento com uma dose menor de código na aplicação (só foi necessário identificar o nome da SP e do parâmetro) e o deixou bem versátil (ainda que um overhead adicional seja concentrado no banco de dados).

A construção da stored procedure utilizou uma chave alternativa para identificar o pedido como único através da combinação DataPedido e ClienteID. Esse artifício foi utilizado uma vez que a chave dos pedidos é artificial (Identity) e não seria possível capturá-la e vinculá-la aos itens. Existem outras alternativas baseadas em TSQL como confiar em uma recuperação com base em posição (os primeiros itens serão dos primeiros pedidos) ou ainda utilizar cursores para gravar de forma alternada os pedidos e os itens mas não julgo ser uma solução muito performática. Outras alternativas incluem um ID do pedido no próprio XML, sua transformação com XSLT ou ainda o uso do atributo Nested em uma Data Relation para que o XML veja aninhado e os relacionamentos identificados. Enfim, são algumas das possibilidades quando se usa esse formato.

De forma a não tornar o artigo mais extenso do que eu gostaria e não me deparar com limitações ao tamanho do post, a parte I desse artigo finaliza aqui. A parte II pode ser conferida logo a seguir.

[ ]s,

Gustavo

Criptografia, Autenticadores e Ataques de Inferência no SQL Server

Boa Noite Pessoal,

A primeira vez que fui apresentado aos recursos de criptografia do SQL Server em meados de 2006, ficou uma certa curiosidade sobre o parâmetro authenticator dos métodos EncryptByKey e EncryptByPassPhrase. As formas mais seguras de criptografia utilizam a combinação de chaves públicas e privadas e são representadas pelos métodos EncryptByAsymKey e EncryptByCert que por sua vez dispensam em sua assinatura o uso de autenticadores. Qual será então a utilidade desse parâmetro ? Se os métodos mais fortes o dispensam e os métodos mais fracos o utilizam como argumento opcional em que situação ele é necessário ?

Passei algum tempo sem encontrar a resposta, mas depois de estudar um pouco mais sobre segurança acabei descobrindo a sua real utilidade. Algumas formas de ataque como negação de serviço, sql injection, etc sempre me foram familiares, pois, já trabalhei muito para prevení-las e eventualmente já tive que ajudar algumas vítimas de ataques nessa modalidade. Entretanto, o termo "ataque de inferência" não é um dos que costumo observar no dia a dia e tampouco vejo mencionado nas bibliografias de SQL Server e nem sequer no Books OnLine. O uso de autenticadores nos métodos EncryptByKey e EncryptByPassPhrase serve justamente para prevenir esse tipo de ataque a dados criptografados.

Ao invés de me prender a explicações mais formais desse termo, vejamos em uma situação prática, porque apenas a criptografia sozinha talvez não seja eficaz na garantia da integridade e confidencialidade dos dados. O script a seguir cria uma pequena tabela de empregados com algumas colunas cadastrais clássicas e alguns dados mais sensitivos a exemplo da coluna salário.

— Cria uma tabela de empregados
CREATE TABLE Empregados (
    EmpregadoID INT IDENTITY(1,1) NOT NULL,
    CPF CHAR(11) NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    Funcao VARCHAR(50) NOT NULL,
    Salario VARBINARY(256) NULL)

— Adiciona as constraints
ALTER TABLE Empregados ADD CONSTRAINT PK_Empregado PRIMARY KEY (EmpregadoID)

— Insere três empregados
INSERT INTO Empregados (CPF, Nome, Funcao, Salario)
VALUES (‘70036543921’,‘Ademilda Souza Gonçalves’,‘Diretora de TI’, NULL)

INSERT INTO Empregados (CPF, Nome, Funcao, Salario)
VALUES (‘51034681233’,‘Alexandre Pereira Neto’,‘Analista Sênior’, NULL)

INSERT INTO Empregados (CPF, Nome, Funcao, Salario)
VALUES (‘25341677542’,‘Edmilson Alves Medeiros’,‘Desenvolvedor Júnior’, NULL)

Os dados cadastrais estão concluídos, agora resta informar os salários para cadastro. Podemos utilizar várias opções no próprio SQL Server como certificados, chaves simétricas, as funções pwdEncrypt e pwdCompare, etc. Não entrarei no mérito do método de criptografia mais forte. Para o propósito desse artigo uma criptografia simétrica baseada em frase já é suficiente.

— Declara uma string para montar a chave simétrica
DECLARE @pwd NCHAR(8)
SET @pwd = N’ds-ospc!’

— Declara strings para armazenar os salários (tem que ser texto para atender a criptografia)
DECLARE @Salario1 VARCHAR(8), @Salario2 VARCHAR(8), @Salario3 VARCHAR(8)
SET @Salario1 = ‘10256.78’ — Salário da Ademilda
SET @Salario2 = ‘5360.29’ — Salário do Alexandre
SET @Salario3 = ‘2230.55’ — Salário do Edmilson

— Declara strings para armazenar as senhas criptografadas
DECLARE @Salario1C VARBINARY(256), @Salario2C VARBINARY(256), @Salario3C VARBINARY(256)
SET @Salario1C = ENCRYPTBYPASSPHRASE(@pwd,@Salario1)
SET @Salario2C = ENCRYPTBYPASSPHRASE(@pwd,@Salario2)
SET @Salario3C = ENCRYPTBYPASSPHRASE(@pwd,@Salario3)

— Atualiza as senhas dos clientes
UPDATE Empregados SET Salario = @Salario1C WHERE EmpregadoID = 1
UPDATE Empregados SET Salario = @Salario2C WHERE EmpregadoID = 2
UPDATE Empregados SET Salario = @Salario3C WHERE EmpregadoID = 3

Edmilson é um desenvolvedor que conseguiu uma credencial para acesso a tabela de empregados. Essa credencial possui permissão de SELECT e UPDATE na tabela de Empregados. Ele não sabe exatamente o salário de Ademilda, mas a julgar pela sua posição na empresa, ele sabe que trata-se de um salário muito superior ao seu. Assim sendo, ele efetua um UPDATE para igualar o seu salário ao dela.

UPDATE Empregados SET Salario = (SELECT Salario FROM Empregados WHERE EmpregadoID = 1)
WHERE EmpregadoID = 3

— Verifica as colunas de nome e salário
SELECT Nome, Salario FROM Empregados

Após a execução do comando, o salário de Ademilda e de Edmilson realmente são iguais (ainda que criptografados). Os códigos HASH podem divergir, mas o importante é que os salários da Ademilda e do Edmilson são iguais.

Nome	Salário
Ademilda Souza Gonçalves	0x01000000CF13BEDF13CFC59006793E775C19BACA7605877816E31CB8D63DC548CCBDC56A
Alexandre Pereira Neto	0x010000001DCCD902BF968B61514AF6AF0DA3510D4DDD6F001A83D149
Edmilson Alves Medeiros	0x01000000CF13BEDF13CFC59006793E775C19BACA7605877816E31CB8D63DC548CCBDC56A

O que ocorreu foi uma inferência por parte do usuário Edmilson. Ele não sabe o salário da Ademilda, mas baseando-se na sua posição, ele consegue inferir que o salário é superior e portanto efetuou a atualização. No momento que ele visualizar o contracheque, ele conseguirá descobrir o salário da Ademilda. Podemos constatar que eles são iguais através da consulta abaixo:

— Declara uma string para montar a chave simétrica
DECLARE @pwd NCHAR(8)
SET @pwd = N’ds-ospc!’

SELECT Nome,
    CAST(
        CAST(DECRYPTBYPASSPHRASE(@pwd,Salario) As VARCHAR(8))
    As SMALLMONEY) As Salario
FROM Empregados

A consulta retorna os empregados e seus salários descriptografados.

Nome	Salário
Ademilda Souza Gonçalves	10256,78
Alexandre Pereira Neto	5360,29
Edmilson Alves Medeiros	10256,78

De fato, a consulta confirma que Edmilson "promoveu-se" aumentando o seu salário. Vejamos agora como o uso de autenticadores pode prevenir esse problema.

— Declara uma string para montar a chave simétrica
DECLARE @pwd NCHAR(8)
SET @pwd = N’ds-ospc!’

— Declara strings para armazenar os salários (tem que ser texto para atender a criptografia)
DECLARE @Salario1 VARCHAR(8), @Salario2 VARCHAR(8), @Salario3 VARCHAR(8)
SET @Salario1 = ‘10256.78’ — Salário da Ademilda
SET @Salario2 = ‘5360.29’ — Salário do Alexandre
SET @Salario3 = ‘2230.55’ — Salário do Edmilson

— Declara strings para armazenar as senhas criptografadas com o uso de autenticadores
DECLARE @Salario1C VARBINARY(256), @Salario2C VARBINARY(256), @Salario3C VARBINARY(256)
SET @Salario1C = (SELECT ENCRYPTBYPASSPHRASE(@pwd,@Salario1,1,CAST(EmpregadoID As SYSNAME)) FROM Empregados WHERE EmpregadoID = 1)
SET @Salario2C = (SELECT ENCRYPTBYPASSPHRASE(@pwd,@Salario2,1,CAST(EmpregadoID As SYSNAME)) FROM Empregados WHERE EmpregadoID = 2)
SET @Salario3C = (SELECT ENCRYPTBYPASSPHRASE(@pwd,@Salario3,1,CAST(EmpregadoID As SYSNAME)) FROM Empregados WHERE EmpregadoID = 3)

— Atualiza as senhas dos clientes
UPDATE Empregados SET Salario = @Salario1C WHERE EmpregadoID = 1
UPDATE Empregados SET Salario = @Salario2C WHERE EmpregadoID = 2
UPDATE Empregados SET Salario = @Salario3C WHERE EmpregadoID = 3

Supondo que Edmilson irá tentar novamente efetuar um ataque por inferência, vejamos a consulta abaixo:

UPDATE Empregados SET Salario = (SELECT Salario FROM Empregados WHERE EmpregadoID = 1)
WHERE EmpregadoID = 3

— Verifica as colunas de nome e salário
SELECT Nome, Salario FROM Empregados

A consulta retorna os empregados e seus salários criptografados (parte do resultado foi truncado na exibição)

Nome	Salário
Ademilda Souza Gonçalves	0x010000000D436D519FF7094F209CBA414C63D48C95DBCC868919A2AECEFDEBC53EC67…
Alexandre Pereira Neto	0x0100000041A7183124B568B47C5945C168C13382DBD8577476E3BE800F060F8CE220D…
Edmilson Alves Medeiros	0x010000000D436D519FF7094F209CBA414C63D48C95DBCC868919A2AECEFDEBC53EC67…

Realmente o salário de Ademilda e Edmilson continuam iguais na sua forma criptografada, agora vejamos se é possível descriptografá-los.

— Declara uma string para montar a chave simétrica
DECLARE @pwd NCHAR(8)
SET @pwd = N’ds-ospc!’

SELECT Nome,
    CAST(
        CAST(DECRYPTBYPASSPHRASE(@pwd,Salario,1,CAST(EmpregadoID As SYSNAME)) As VARCHAR(8))
    As SMALLMONEY) As Salario
FROM Empregados

A consulta mostra o resultado abaixo:.

Nome	Salário
Ademilda Souza Gonçalves	10256,78
Alexandre Pereira Neto	5360,29
Edmilson Alves Medeiros	NULL

De fato embora o salário criptografado seja exatamente o mesmo no momento de efetuar o retorno, o salário de Edmilson não pode ser descriptografado. Isso ocorreu porque embora a chave de criptografia utilizada seja exatamente a mesma (ds-ospc!), houve um autenticador a mais no processo de autenticação. Esse autenticador foi o EmpregadoID. O valor de EmpregadoID para Ademilda é 1 e para Edmilson é 2. Como ele usado como autenticador, não será possível que o salário de Edmilson seja corretamente descriptografado, pois, o EmpregadoID será diferente.

Para que o ataque tivesse êxito, seria necessário que Edmilson atualizasse também o seu valor para EmpregadoID, mas nesse caso, além dele não ter as permissões necessárias, a tabela possui uma unicidade em torno do ID que impediria que ele efetuasse a troca. Mesmo que ele conseguisse fazer isso, trocar o seu ID é semelhante a trocar sua identidade. Se esse ID for utilizado em outras tabelas ou for emitido no contracheque, ou ainda, for utilizado em outras tabelas, haverá vários indícios de fraude e certamente ele seria descoberto muito rapidamente. Imagine por exemplo que o ID seja utilizado para rastrear despesas históricas de viagem, reuniões de compromissos, etc. Nesse caso Edmilson teria que literalmente se responsabilizado por todas as atividades de Ademilda e esse tipo de fraude seria descoberto muito rapidamente.

Esse tipo de ataque não é exclusivo do SQL Server e tampouco de banco de dados. O fato de um dado ser criptografado não necessariamente significa que ele está protegido contra todos os tipos de violações de integridade e confiabilidade. O uso de autenticadores pode reduzir esse tipo de ameça embora não dispense uma boa gestão de permissões e guarda das chaves de criptografia utilizadas.

[ ]s,

Gustavo

Hash Indexes – Uma implementação no SQL Server – Parte I

Boa Noite Pessoal,

Hoje vou pagar uma das promessas antigas de postagem aqui no blog. A primeira vez que comentei sobre o assunto hash indexes foi no community zone de 2008 para alguns profissionais, que assim como eu, trabalham com o SQL Server, participam de eventos relacionado a esse produto, ajudam comunidades e respondem as perguntas nos principais fóruns. Na época esse assunto despertou uma certa curiosidade, já que os termos ligados a índices no SQL Server sempre rodeam a famosa B-TREE quer seja em implementações clusterizadas ou não clusterizadas. Estou certo de que aqueles que ficaram curiosos não esperaram para ver o artigo que nunca saiu, mas acho a implementação baseada em hash muito útil e estou certo que outros poderão se beneficiar para algumas situações de negócio pouco comuns mas que cedo ou tarde acontecem. Como o artigo é consideravelmente grande, o spaces não permitirá que eu o poste de uma só vez, forçando-me dividí-lo em duas partes. Nessa primeira parte falarei apenas das bases teóricas para entender o funcionamento de índices baseados em Hash e alguns scripts necessários para o devido entendimento. Na segunda parte do artigo, a ser publicada futuramente, demonstrarei uma aplicação prática com variações de volume e a mensuração de desempenho entre os índices tradicionais, um índice baseado em hash e outras soluções pertinentes. Embora o SQL Server 2008 tenha sido utilizado, boa parte dos artigos é aplicável às versões anteriores desde que feita as devidas adaptações.

Uma situação de negócio

Imagine que você seja o responsável por elaborar um modelo de dados que representa um sistema de pagamentos de títulos. Nesse sistema de pagamentos teremos as seguintes características:

• O título possui um código de barras com 44 posições sempre fixas (ex: 23892000000000000001042065252722910301128000)
• O título possui um valor em moeda corrente
• O título pode ser pago com um valor a menor
• O título pode ser pago com atraso
• O título pode ser pago em outra moeda

O código de barras do título é único e geralmente carrega intrisicamente diversos detalhes do título em questão. Embora o título normalmente seja pago uma única vez e dentro da data de vencimento (quando há) e na mesma moeda em que foi emitido essa não é um regra que se aplique em 100% dos casos.

Há bancos que permitem o pagamento de títulos em duplicidades (é um absurdo mais existem), há títulos que podem ser pagos após o vencimento e existem aqueles que permitem a digitação do valor o que pode fazer com que sejam pagos no valor integral ou um valor a menor (típico de cartões de crédito) e talvez um brasileiro no exterior possa pagar um título brasileiro em moeda estrangeira (com as devidas conversões).

Assim sendo, dadas essas possibilidades, manter em uma única tabela de títulos os dados referentes a pagamento pode não ser interessante. Na proposta para o modelo, considerei duas tabelas conforme demonstrado abaixo:

O código do boleto com 44 posições é um identificador único e poderia assumir o papel de chave primária. No entanto, ele possui 44 posições e repassá-lo como FK para uma tabela de pagamentos é aumentar significativamente a largura do registro e conseqüentemente o tamanho da tabela e dos índices existentes. Por isso a utilização de uma chave artificial para tornar o registro menor (um INT possui 4 bytes) economiza espaço e proporciona desempenho (quanto menor for o registro mais rápida será sua recuperação).

Colunas textuais geralmente estão presentes em tabelas pequenas e possuem uma busca não exata sendo típicas de predicados usando o operador LIKE que normalmente ignoram a utilização de índices. Não há problema em usar um operador LIKE contra uma tabela relativamente pequena, assim como não há problema em usá-lo para tabelas muito grandes, desde que na cláusula WHERE haja algum outro predicado que seja mais seletivo e efetivo que o LIKE.

Uma coluna que representa o código de barra (embora textual) normalmente é resultado de situações um pouco mais atípicas. Esse tipo de coluna é utilizada para buscas exatas e utilizam o operador de igualdade para que se encontre o título que tem exatamente aquele código de barra. Para situações de igualdade, os índices tradicionais baseados em B-TREE (Clustered e NonClustered) são bem eficientes.

O problema com índices baseados em B-TREE é que eles contém em sua composição uma ou mais colunas chave acompanhadas de ponteiros e quanto maior for a coluna chave, maior será o tamanho do índice e por conseqüência mais lenta será a navegação por esse índice e recuperação dos dados. Indexar uma coluna textual como um UF, CPF ou um CNPJ pode ser aceitável, mas um coluna textual com muitos caractéres pode ser desastroso.

Em nosso exemplo, a coluna de código do boleto ocupa 44 bytes por registro enquanto as demais colunas juntas ocupam juntas ocupam 11 bytes por registro (desconsiderados os controles internos de quantidade de colunas, nulabilidade, offset, etc). A coluna código do boleto é responsável por 80% do espaço do registro e indexá-la irá produzir um índice tão grande quanto praticamente a própria tabela incorrendo em muito espaço e pouca eficiência.

O tal do HASH

O termo HASH significa espalhar ou embaralhar e é utilizado em diversas situações no mundo da tecnologia de informação (embora existam situações do uso de hash na antiguidade). Uma implementação bem conhecida do uso do hash é para criptografia de dados (em especial o "inquebrável" hash MD5). O objetivo do hash é normalmente aplicar um algoritmo (função de hash ) sobre um determinado dado e retornar um valor normalmente menor do o dado original. Quando uma função de hash é aplicada sobre um determinado dado ela deve produzir sempre o mesmo valor (ou seja é determinística), mas é possível que dados diferentes submetidos a uma função de hash retornem o mesmo valor. Vejamos um exemplo de uma função de hash bem típica do dia a dia nos concursos públicos e vestibulares. A lista abaixo foi obtida na internet e representa a lista de aprovados no processo seletivo de graduação em direito na FGV de São Paulo na fase 2. A lista foi retirada do link http://educaterra.terra.com.br/vestibular/aprovados_direito.pdf (apenas os nomes):

Nome	Balcão
Alexandre Rebêlo Ferreira	A – C
Amanda Cunha e Mello Smith Martins	A – C
Ana Paula Chican e Oliveira	A – C
Andre Gomes Montilha	A – C
Antônio Augusto Pereira dos Santos	A – C
Beatriz Sampaio Barros	A – C
Cindy Scofano Takahashi	A – C
Diana Barlem dos Santos	D – F
Erik Fontenele Nybo	D – F
Estêvão Nascimento Orcini	D – F
Felipe Nutti Giannattasio	D – F
Felipe Tucunduva Van Den Berch Van Heemstede	D – F
Francisco Ribeiro de Magalhães Neto	D – F
Gabriel Henrique Montera Lucilio	G – I
Gabriel Landi Fazzio	G – I
Gabriel Oura Chiang	G – I
Gustavo Abrahamsson Marcondes Pereira	G – I
Henrique Tetsuaki Matsura Misawa	G – I
Isabella Bancovsky Becker	G – I
Isabelle Glezer	G – I
Jayme Polachini Neto	J – L
Joao Guimaraes Cozac	J – L
Joao Gustavo De Rezende Lima	J – L
João Luis Monteiro Piassi	J – L
Joao Vicente Lapa de Carvalho	J – L
Julia Aoki Chao	J – L
Laura Costa Gibin	J – L
Livia Bragança Claudio	J – L
Lucas de Araujo Gomes de Castro	J – L
Maira Machado Frota Pinheiro	M – N
Marcela Gaspar Pedrazzoli	M – N
Marcela Mattiuzzo	M – N
Marcela Rissato Ferreira	M – N
Mariana Marangoni de Paulo	M – N
Mariana Ribeiro Cardoso	M – N
Mariel Linda Safdie	M – N
Nathalia Arcencio de Marchi Dos Santos	M – N
Olivia Do Amaral Mesquita	O – R
Paulo Eduardo de Lima Pacheco	O – R
Pedro Gallas Prellwitz	O – R
Pedro Henrique Wieck Gonçalves	O – R
Pedro Ivo Gil Zanetti	O – R
Rafael Campedelli Andrade	O – R

Supondo que os aprovados tenham de ir a um balcão específico para matricular-se, e supondo que existam seis balcões (AC, DF, GI, JL, MN e OR), para que o aprovado saiba em qual balcão ele deve procurar atendimento, basta ele verificar a primeira letra do nome e comparar com o intervalo de letras entre a letra inferior e a letra superior de cada balcão existente. Um aprovado com o nome Pedro deveria procurar o balcão (O – R), pois, esse balcão atenderá a todos os aprovados cuja primeira letra do nome esteja entre O e R. A conversão de um nome para localizar um balcão específico é uma função de hash . O menor dos nomes da lista possui 15 caractéres, o maior possui 44 caractéres e a média é de 25 caractéres. Já a identificação baseada em hash possui apenas dois caractéres (considerando apenas as letras inferior e superior). Pode-se perceber que cada aprovado só poderá dirigir-se a um único balcão, mas um balcão poderá atender mais de uma pessoa.

O uso do hash têm várias aplicações em banco de dados. O hash está presente em algoritmos de junção baseados em hash (hash joins). A própria criptografia de dados e compactação também são aplicações de hash. O Analysis Services utiliza uma função de hash para agrupamento automático de membros formando um hierarquia. Funções de hash podem ser aplicadas sobre uma determinada coluna e mapear o registro para um determinado bloco de dados. Existem várias variações do uso de hash para esse mapeamento (hash simples, linear, extensível, particionado, etc).

Tanto no exemplo real quanto em diversas outras aplicações, uma função de hash é ótima quando mapeia poucas entradas para um determinado resultado ou faz o mapeamento de forma a distribuir os resultados com equivalência. O uso do hash no exemplo da lista de aprovados é adequado, pois, mapeia os candidatos aos balcões mantendo uma quantidade entre seis e nove candidatos. Se fosse utilizado apenas dois intervalos (AM e NZ), haveria 36 aprovados no primeiro intervalo e apenas 7 no segundo intervalo deixando-os bem desbalanceados.

CHECKSUM e BINARY_CHECKSUM – Duas funções de hash nativas no SQL Server

CHECKSUM significa checagem e possui diversas utilizações. No protocólo TCP/IP por exemplo, quando dados são quebrados em pacotes e enviados via rede, ao término do envio dos pacotes é feita uma checagem para verificar se algum pacote foi alterado. Essa checagem se dá por uma combinação através do cabeçalho dos pacotes, ou seja, se algo foi violado, a checagem inicial irá dar um valor da checagem final.

O SQL Server também utiliza mecanismos de CHECKSUM para verificar a integridade de suas páginas de dados. Nesse caso, cada byte da página é combinado com o byte subseqüente produzindo um valor. No momento em que a página é escrita, o valor obtido da combinação entre os bytes da página é gravado no cabeçalho da página. Quando a página for lida novamente, o SQL Server irá combinar os bytes novamente para obter o valor e comparar com o valor armazenado no cabeçalho da página. Se houver uma diferença entre esses valores, a checagem terá falhado e a página será marcada como corrompida. Se o valor calculado for igual ao armazenado no cabeçalho da página então a página será considerada íntegra. Um raciocínio semelhante é aplicado ao backup quando a opção CHECKSUM é utilizada junto ao comando BACKUP DATABASE.

Além das aplicações citadas, o SQL Server dispõe de uma função chamada CHECKSUM. Essa função é utilizada para computar um valor inteiro independente do parâmetro passado. Ex: SELECT CHECKSUM(‘Um parâmetro de entrada’)

Após aplicar a função CheckSum sobre a string "Um parâmetro de entrada", podemos perceber que o retorno da função foi -1752065197. Isso significa que a string sofreu uma transformação e retornou um valor inteiro. É interessante notar que uma string como "Um parâmetro de entrada" possui 23 caractéres e isso significa 23 bytes. O valor após a utilização do CHECKSUM foi um inteiro e valores inteiros ocupam 4 bytes. Se o mapeamento de uma string de 23 bytes resulta em um inteiro de 4 bytes é natural que diferentes valores resultem em um mesmo CHECKSUM já que não é possível um valor exclusivo para cada parâmetro. Podemos evidenciar isso através dos comandos SELECT abaixo:

• SELECT CHECKSUM(‘ABC’), CHECKSUM(‘abc’) retornam 1132495864
• SELECT CHECKSUM(1), CHECKSUM(0x01) retornam 1

Há uma variação da função CHECKSUM conhecida como BINARY_CHECKSUM que poderia resolver esse problema. A função abaixo não incorre no mesmo valor. Ex:

• SELECT BINARY_CHECKSUM(‘ABC’), BINARY_CHECKSUM(‘abc’) retornam respectivamente 17763 e 26435

Ainda assim, a função BINARY_CHECKSUM também não irá gerar um valor exclusivo já que similar ao CHECKSUM também retorna um inteiro. É possível que valores diferentes gerem o mesmo valor através da função BINARY_CHECKSUM. Ex:

• SELECT BINARY_CHECKSUM(‘2q’), BINARY_CHECKSUM(‘3a’) retornam 849

Se fizermos uma analogia à descrição do hash, as funções CHECKSUM e BINARY_CHECKSUM são um tipo de funções de hash. Elas recebem um parâmetro de entrada e retornam um valor e esse valor é menor que o parâmetro original. De forma análoga à aplicação de funções de hash na aplicabilidade de mapeamentos, as funções CHECKSUM e BINARY_CHECKSUM também funcionam como uma mapa, na qual o parâmetro original é mapeado para o retorno da função.

Colunas calculadas, indexação e funções de hash

Uma das funcionalidades disponíveis a partir do SQL Server 2000 é a possibilidade de criar colunas calculadas e indexá-las. Essa funcionalidade permite estratégias de indexação bem interessantes. É possível indexar o resultado de uma função e utilizá-lo como critério de busca ao invés de utilizar conversões em uma cláusula WHERE (o que normalmente desperdiça o potencial de recuperação de um índice). Os artigos "Index Seek em um Where com função ? Como ?" e "Otimizando Performance Com Colunas Computadas" são bem interessantes para compreender como o uso de colunas calculadas em combinação com a indexação podem prover desempenho.

Através do uso das colunas calculadas, pode-se criar uma coluna calculada que seja o resultado de uma função de hash como a checksum. O exemplo abaixo demonstra como fazer isso:

— Criação da tabela de títulos
CREATE TABLE Titulos (
    TituloID INT,
    TituloCodigo CHAR(44) NOT NULL,
    TituloValor SMALLMONEY NOT NULL,
    TituloDataVencimento DATE NOT NULL,
    TituloCodigoHash As CHECKSUM(TituloCodigo)
    CONSTRAINT PK_Titulo PRIMARY KEY CLUSTERED (TituloID))

— Criação do Índice baseado na função de hash
CREATE INDEX IX_TituloCodigo ON Titulos (TituloCodigo)
CREATE INDEX IX_TituloCodigoHash ON Titulos (TituloCodigoHash)

Consultas através de índices baseados em hash

Agora que a coluna existe e que os índices foram criados, é necessário popular a tabela de títulos para que o uso do índice hash possa ser evidenciado. O script abaixo populará 10 milhões de linhas nessa tabela de forma aleatória. Não vou entrar nos detalhes de como o script faz essa carga, mas ao final haverá 10 milhões de títulos carregados (aproximadamente umas duas horas).

— Desabilita os índices para acelerar a carga
ALTER INDEX IX_TituloCodigo ON Titulos DISABLE
ALTER INDEX IX_TituloCodigoHash ON Titulos DISABLE

— Declara uma variável e a inicializa
DECLARE @i INT = 1

— Declara demais variáveis necessárias
DECLARE @TituloCodigo CHAR(44), @TituloValor SMALLMONEY, @TituloDataVencimento DATE

WHILE @i <= 10000000
BEGIN

    SET @TituloCodigo =
        CAST(((ABS(CHECKSUM(NEWID())) % 89999999999) + 10000000000) As CHAR(11)) +
        CAST(((ABS(CHECKSUM(NEWID())) % 89999999999) + 10000000000) As CHAR(11)) +
        CAST(((ABS(CHECKSUM(NEWID())) % 89999999999) + 10000000000) As CHAR(11)) +
        CAST(((ABS(CHECKSUM(NEWID())) % 89999999999) + 10000000000) As CHAR(11))

    SET @TituloValor =
        CAST((ABS(CHECKSUM(NEWID())) / 800000.00) As DECIMAL(10,2))

    SET @TituloDataVencimento =
        CAST((DATEADD(D,ABS(CHECKSUM(NEWID())) % 120,GETDATE())) As DATE)

    INSERT INTO Titulos (TituloID, TituloCodigo, TituloValor, TituloDataVencimento)
    VALUES (@i, @TituloCodigo, @TituloValor, @TituloDataVencimento)

    SET @i += 1

END

— Reabilita os Índices
ALTER INDEX IX_TituloCodigo ON Titulos REBUILD
ALTER INDEX IX_TituloCodigoHash ON Titulos REBUILD

Após a inserção de 10 milhões de registros de forma aleatória, é necessário inserir um registro conhecido para que as pesquisas possam ser realizadas.

— Insere um título conhecido
INSERT INTO Titulos (TituloID, TituloCodigo, TituloValor, TituloDataVencimento)
VALUES (10000001,‘23892000000000000001042065252722910301128000’,300,‘20091101’)

A consulta abaixo irá pesquisar o título recém inserido com base no seu código que normalmente será o elemento conhecido. Para verificar o desempenho, utilizarei as estatísticas de IO, tempo e o plano de execução.

— Ativa as estatísticas de IO e tempo
SET STATISTICS IO ON
SET STATISTICS TIME ON

— Pesquisa o título com o código 23892000000000000001042065252722910301128000
SELECT
    TituloID, TituloCodigo, TituloValor, TituloDataVencimento
FROM Titulos
WHERE TituloCodigo = ‘23892000000000000001042065252722910301128000’

As estatísticas de tempo e IO são detalhadas abaixo:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 135 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table ‘Titulos’. Scan count 1, logical reads 7, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 69 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

O plano de execução, como era de se esperar, fez uma pesquisa no índice nonclustered (IX_TituloCodigo) para encontrar o código do título e posteriormente acessar as demais colunas:

Agora vejamos a consulta reescrita para utilizar o índice com base na função de hash.

— Ativa as estatísticas de IO e tempo
SET STATISTICS IO ON
SET STATISTICS TIME ON

— Pesquisa o título com o código 23892000000000000001042065252722910301128000
SELECT
    TituloID, TituloCodigo, TituloValor, TituloDataVencimento
FROM Titulos
WHERE TituloCodigoHash = CHECKSUM(‘23892000000000000001042065252722910301128000’)

As medidas de desempenho em tempo e IO foram:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 16 ms, elapsed time = 60 ms.

(1 row(s) affected)
Table ‘Titulos’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

O plano de execução é idêntico, a exceção de que o índice baseado na coluna hash  foi utilizado para fazer a pesquisa (seek):

O primeiro plano leu 7 páginas em memória (logical reads) enquanto o segundo leu 6 páginas que representa uma página a menos (vou desconsiderar as leituras físicas, pois, quando a segunda consulta foi executada, as páginas já estavam em memória). Em termos de velocidade, é até covardia comparar 120ms contra 2ms. Ainda que a medida de tempo seja imprecisa quando representa frações muito pequenas, a execução consecutiva dessas consultas (com variações de parâmetros) mostrará que a segunda é com certeza mais rápida. É de se esperar, pois, há pelo menos uma página a menos para ser lida.

A segunda consulta (embora mais eficiente) não está correta. O uso da função CHECKSUM pode fazer com que títulos diferentes obtenham o mesmo valor de CHECKSUM e isso pode fazer com que a busca por um determinado título através dessa função de hash retorne mais de um resultado já que embora o código do título seja único, não necessariamente o resultado da função de hash trará valores únicos. A consulta abaixo mostra que existem repetições para um mesmo valor de hash (minha consulta retornou 11717 registros).

— Encontra os códigos de hash duplicados
SELECT TituloCodigoHash, COUNT(*) FROM Titulos
GROUP BY titulocodigohash
HAVING COUNT(*) > 1

Para evitar que isso aconteça, é necessário adaptar a segunda consulta de forma que após o uso do índice hash, ela filtre o título correto.

— Pesquisa o título com o código 23892000000000000001042065252722910301128000
SELECT
    TituloID, TituloCodigo, TituloValor, TituloDataVencimento
FROM Titulos
WHERE TituloCodigoHash = CHECKSUM(‘23892000000000000001042065252722910301128000’)
AND TituloCodigo = ‘23892000000000000001042065252722910301128000’

Nesse caso a consulta está correta e o otimizador pode escolher tanto o índice normal quando o índice baseado em hash. Embora o índice baseado em hash e o índice sobre a coluna TituloCodigo tenham praticamente o mesmo desempenho, curiosamente o SQL Server escolheu o índice sobre a coluna TituloCodigo ao invés do índice baseado em hash conforme a figura abaixo:

De fato, como há os dois predicados (o baseado em hash e o baseado em código) e o desempenho é semelhante, não é uma escolha ruim optar pelo índice tradicional. Não acho que a escolha do otimizador esteja errada, mas a consulta abaixo mostra o tamanho dos índices:

— Mostra o espaço ocupado
SELECT
    Object_Name(p.Object_Id) As Tabela,
    I.Name As Indice, Total_Pages As Paginas,
    Total_Pages * 8 / 1024.00 As TamanhoMB
FROM sys.Partitions As P
INNER JOIN sys.Allocation_Units As A ON P.Hobt_Id = A.Container_Id
INNER JOIN sys.Indexes As I ON P.object_id = I.object_id AND P.index_id = I.index_id
WHERE p.Object_Id = Object_Id(‘Titulos’)

O resultado da consulta é expresso conforme tabela abaixo:

Tabela	Índice	Páginas	Tamanho (MB)
Titulos	PK_Titulo	79673	622.44
Titulos	IX_TituloCodigo	63755	498.08
Titulos	IX_TituloCodigoHash	17365	135.66

O índice IX_TituloCodigo representa 498MB de espaço contra 136MB do índice IX_TituloCodigoHash. Se o índice IX_TituloCodigo fosse eliminado, o desempenho das consultas ainda se manteria o mesmo, e haveria um espaço de 498MB liberados. Pode não parecer muito, mas vale a pena lembrar que 498MB representa 80% do tamanho total da tabela (622MB). Se a tabela tivesse por exemplo 2TB, esse índice iria representar 1,6TB e isso é bem considerável. Sobre esse ponto de vista, o índice IX_TituloCodigoHash é certamente muito mais indicado, pois, representa 22% do tamanho da tabela (algo bem inferior a 80%). À medida que o número de caractéres for aumentando, o uso do índice sobre a coluna de título tornar-se-á cada vez mais oneroso do ponto de vista de espaço e manutenção e com o tempo o próprio otimizador poderá descartá-lo.

Se os planos têm desempenho semelhante, ocupar 80% do tamanho da tabela parece ser um completo desperdício e de fato é. Se há como fazer a pesquisa através do índice hash e liberar o espaço certamente essa é a escolha certa.

— Retira o Índice NonClustered IX_TituloCodigo
DROP INDEX Titulos.IX_TituloCodigo

— Pesquisa o título com o código 23892000000000000001042065252722910301128000
SELECT
    TituloID, TituloCodigo, TituloValor, TituloDataVencimento
FROM Titulos
WHERE TituloCodigoHash = CHECKSUM(‘23892000000000000001042065252722910301128000’)
AND TituloCodigo = ‘23892000000000000001042065252722910301128000’

A consulta possui excelentes tempos de execução.

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 3 ms.

(1 row(s) affected)
Table ‘Titulos’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Embora o plano de execução seja visualmente parecido com o plano anterior, há um detalhe a mais para efetuar o filtro e remover os registros que tem o mesmo CHECKSUM, mas não possuem o valor do título correto. Após o índice hash ter sido utilizado (Index Seek), as entradas da tabela já foram recuperadas (Key Lookup). O CHECKSUM pode não recuperar exatamente um único registro já que é passível de título com códigos diferentes possuírem o mesmo valor de CHECKSUM. O predicado adicional para filtrar a ocorrência correta não representa perda de desempenho, pois, o índice hash já fez boa parte dos filtros.

Quando não utilizar índices baseados em Hash

O uso de hash indexes representa uma solução de busca alternativa com base em uma correspondência única. Sua principal vantagem é que o tempo de pesquisa é semelhante ao uso de índices baseados em btree, mas com um espaço de armazenamento e manutenção muito superior. Em contrapartida, soluções de índices baseadas em funções de hash são excelentes para pesquisas de identificação única. Esse mecanismo não poderia ser utilizado para pesquisar um range de títulos que definidos entre 23892000000000000001042065252722910301128000 e 34152000000000000001042065252722910301128000 por exemplo. A idéia de economia de espaço também só é válida se o parâmetro de entrada (no caso o código do título) tiver um comprimento muito grande em relação ao resultado da função de hash. Não seria muito vantajoso por exemplo utilizar uma função de hash se o tamanho do código título fosse algo entre 6 ou 10 caractéres.

Na parte II desse artigo, a ser publicada futuramente, apresentarei alguns testes comparativos mais interessantes abordando a utilização de hash indexes.

[ ]s,

Gustavo

Unique Constraints – Aplicações, Alternativas e um lapso “justificável” do SQL Server

Bom Dia Pessoal,

Continuo muito enrolado e com dificuldades de aparecer nos fóruns, mas tento contribuir como posso. No caso dos fóruns do MSDN e do TechNet, tenho entrado, mas apenas com o intuito de moderar, pois, está impossível conseguir responder (as responsabilidades não deixam…). Um dia desse vi uma questão bastante interessante intitulada "Como criar um index para não duplicar". Em um artigo "Mitos do SQL Server – Será que a opção IGNORE_DUP_KEY permite entradas duplicadas na chave primária e índices únicos ?" demonstrei que uma Unique Constraint cria inevitavelmente um índice unique para garantir fisicamente a unicidade. A grande questão é como tratar a presença de valores nulos e essa unicidade. Vejamos um exemplo prático.

Uma situação de negócio

Suponha que seja necessário modelar um sistema de vendas de produtos a consumidores finais. Inevitavelmente irá aparecer alguma entidade "Cliente" nesse modelo. Supondo ainda que os atributos relevantes consistam em Nome, CPF, RG, Renda e Classe, e que os atributos CPF e RG consistam em identificadores únicos, existem decisões a serem tomadas sobre qual dessas colunas irá constituir a chave primária. Pode-se adotar o uso de chaves artificiais adicionando-se um outro identificador como IDCliente, CodCliente, etc ou utilizar-se as chaves naturais como o próprio CPF ou o RG. Não irei discorrer nesse artigo o que é melhor (se utilizar chaves artificiais ou naturais), mas independente da escolha, os atributos CPF e RG continuam sendo únicos e no máximo um deles poderá compor a chave primária.

Na elaboração lógica e (ou) conceitual de um modelo de dados, quando todos os identificadores únicos são levantandos, eles são ditos chaves candidatas, ou seja, todas são candidatas em potencial para se tornar a chave primária. Após a escolha de uma chave candidata para exercer o papel de chave primária, as demais chaves candidatas são ditas chaves secundárias (ou chaves alternativas), pois, embora não sejam chaves primárias podem servir para identificação do registro assim como a chave primária faz. A principal diferença é que a chave primária, além de identificar registros como as chaves secundárias o fazem, ela também será necessária para viabilizar os relacionamentos.

Adotando a escolha de uma chave artificial, temos a seguinte implementação física:

— Cria a tabela de Clientes
CREATE TABLE Clientes (
    ClienteID INT NOT NULL, ClienteNome VARCHAR(50) NOT NULL,
    ClienteRG VARCHAR(15) NOT NULL, ClienteCPF CHAR(11) NOT NULL)

— Adicionar o ClienteID como chave primária
ALTER TABLE Clientes ADD CONSTRAINT PKCliente PRIMARY KEY (ClienteID)

Uma boa prática de administração de dados é garantir que as chaves secundárias tenham uma constraint unique para garantir a unicidade. Embora o CPF e o RG não sejam a chave primária da tabela, não é interessante que eles se repitam. Para garantir essa integridade, é adicionada logo a seguir as constraints unique nessas colunas.

— Adiciona constraints unique para restringir as chaves secundárias
ALTER TABLE Clientes ADD CONSTRAINT UQClienteRG UNIQUE (ClienteRG)
ALTER TABLE Clientes ADD CONSTRAINT UQClienteCPF UNIQUE (ClienteCPF)

Dessa forma, mesmo que a chave primária seja única, não será tolerada a repetição de CPFs e RGs conferindo a essas colunas as características físicas de unicidade. Isso é particularmente importante quando chaves artificiais são escolhidas como chaves primárias. O script abaixo mostra a efetividade dessas constraints.

— Tenta efetuar alguns cadastros
INSERT INTO Clientes VALUES (1,‘Natanael’,‘14538543’,‘75712230466’)
INSERT INTO Clientes VALUES (2,‘Marcelo’,‘9845423’,‘82013423920’)
INSERT INTO Clientes VALUES (3,‘Jonas’,‘557023’,‘82013423920’)

(1 row(s) affected)

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint ‘UQClienteCPF’. Cannot insert duplicate key in object ‘dbo.Clientes’.
The statement has been terminated.

Como era de se esperar, mesmo não havendo duplicidade de chaves primárias, o fato do CPF estar repetido impede o cadastro do terceiro registro. Nada mais justo, pois, se o CPF é uma chave secundária e identifica o registro de forma única ele também não pode ser duplicado. Enquanto as chaves secundárias forem obrigatórias, a constraint unique serve perfeitamente aos propósitos de identificar registros de forma secundária, bem como garantir a qualidade dos dados impedindo que chaves secundárias se repitam.

O dilema da Unique Constraint para campos não obrigatórios

Alguns problemas e contradições surgem quando colunas não obrigatórias utilizam Unique Constraints. O exemplo abaixo demonstra uma dessas possibilidades.

— Exclui os registros
DELETE FROM Clientes

— Altera as colunas para permitir valores nulos
ALTER TABLE Clientes ALTER COLUMN ClienteRG VARCHAR(15) NULL
ALTER TABLE Clientes ALTER COLUMN ClienteCPF CHAR(11) NULL

— Adiciona três clientes
INSERT INTO Clientes VALUES (1,‘Natanael’,‘14538543’,‘75712230466’)
INSERT INTO Clientes VALUES (2,‘Marcelo’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (3,‘Jonas’,NULL,NULL)

(1 row(s) affected)

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint ‘UQClienteRG’. Cannot insert duplicate key in object ‘dbo.Clientes’.
The statement has been terminated.

Embora não tenha existido nenhuma "repetição" de fato, a presença de dois valores nulos no campo RG fez com que o terceiro registro fosse rejeitado. No primeiro exemplo, a repetição do CPF "82013423920" justifica uma violação, mas será que a presença de um registro nulo deveria sinalizar uma rejeição ? Para o SQL Server parece que sim, mas para o ORACLE parece que não. Antes que algum xiita comece um bombardeio de críticas ao SQL Server, vejamos alguns pontos de vista.

Razões para bloquear registros nulos repetidos

Se considerarmos que a coluna CPF e a coluna RG correspondem a uma chave secundária, ou seja, tem capacidades de identificar registros como únicos então faz de fato sentido deixar um valor nulo apenas. Afinal dado um determinado CPF é esperado retornar apenas um único registro e se o valor nulo puder se cadastrado várias vezes estaremos violando essa característica da chave secundária e ela perde sua capacidade de identificar registros de forma única. Esse é o comportamento do SQL Server em relação a constraint Unique.

Razões para não bloquear registros nulos repetidos

Se considerarmos a lógica baseada em três valores (verdadeiro, falso ou nulo) temos algumas premissas básicas:

A	B	A = B ?
Verdadeiro	Verdadeiro	Verdadeiro
Verdadeiro	Falso	Falso
Verdadeiro	Nulo	Nulo
Falso	Verdadeiro	Falso
Falso	Falso	Falso
Falso	Nulo	Nulo
Nulo	Verdadeiro	Nulo
Nulo	Falso	Nulo
Nulo	Nulo	Nulo

Se essa lógica for completamente aplicada ao raciocínio das chaves secundárias, não podemos admitir que um valor nulo seja exatamento igual a outro valor nulo. Toda vez que algo conhecido (NOT NULL) é comparado com algo desconhecido (NULL) o resultado é desconhecido. Para tornar isso implementável, admite-se que se uma comparação é desconhecida ela não pode ser considerada como verdade e portanto convenciona-se que ela é falsa. É por essa razão que em qualquer banco de dados a comparação de valores nulos com outros valores nulos é avaliada como falsa por padrão.

Se a comparação entre um valor nulo e outro valor nulo é avaliada como falsa, então o comportamento esperado (e em conformidade com o comitê ANSI) é de que quando houver valores conhecidos (NOT NULL), as repetições não são permitidas e quando houver valores desconhecidos (NULL) as repetições são permitidas. Esse é o comportamento do ORACLE em relação a constraint Unique e que na minha opinião parece mais coerente. Entretanto existem algumas outras considerações a serem feitas.

A indexação e a Nulabilidade

No artigo "Mitos do SQL Server – Será que a opção IGNORE_DUP_KEY permite entradas duplicadas na chave primária e índices únicos ?" demonstrei a relação entre constraints unique e a indexação. Essa relação não é exclusiva do SQL Server. Outros SGBDs também a utilizam. Ter um índice na coluna da constraint unique é uma estratégia básica de garantir fisicamente a unicidade e permitir um mecanismo de consulta mais eficiente para impedir que a unicidade seja violada. É justamente nesse ponto que reside a explicação do porque o Oracle e o SQL Server divergem no comportamento em relação às constraints unique.

Por padrão, nos SGBDs o valor nulo significa ausente e por isso alguns deles não incluem entradas nulas na árvore de índices. Isso pode parecer mais eficiente por um lado, pois a árvore será mais compacta, mas em contrapartida irá limitar sensivelmente o desempenho de consultas que façam pesquisas em um coluna que aceite valores nulos. Se por um lado o ORACLE parece mais coerente com a lógica de três valores para constraints unique, por outro lado ele não será capaz de utilizar um índice para a consulta abaixo:

SELECT ClienteID, ClienteNome, ClienteRG, ClienteCPF
FROM Clientes WHERE ClienteRG IS NULL

Considerando a presença de valores nulos repetidos e a característica opcional do CPF, se esse tipo de pesquisa for feito com freqüência, haverá uma queda sensível no desempenho por parte do Oracle por exemplo (ainda que em coerência com a lógica de três valores). Normalmente para anular esse efeito, é comum colocar um valor padrão como XXX, mas nesse caso, essa alternativa não faz sentido, pois a repetição de XXX seria vetada já que se trata de um valor conhecido.

O SQL Server em contrapartida é capaz de adicionar entradas nulas na árvore de indexação (independente da constraint unique estar ou não presente) e caso lhe venha a ser solicitado a consulta citada, ele poderá utilizar o índice de forma mais eficiente.

As diferenças estruturais na forma como o ORACLE e o SQL Server trabalham a indexação é a grande responsável pelos seus comportamentos em relação a constraint unique já que indiretamente essa depende de um índice. É por isso que por um lado o SQL Server pode ter índices mais eficientes mas por outro pode apresentar um comportamento equivocado em relação a lógica de três valores. Ainda assim, o ORACLE pode levar uma certa vantagem, pois, é necessário antes de mais nada garantir a consistência dos dados antes de seu desempenho nas consultas. Apenas por curiosidade, se esse exemplo fosse utilizado em DB2 não funcionaria, pois, ele não permite a criação de constraints unique em colunas que não sejam NOT NULL.

Solução Alternativa 1 – Revisão da modelagem

No próprio post no fórum do MSDN, foi solicitado a revisão da modelagem de forma a eliminar esse problema. Concordo que a utilização de técnicas de modelagem baseadas em especialização podem eliminar a questão da nulabilidade. Uma implementação alternativa poderia ser feito com os scripts abaixo:

— Não execute esse script. Ele é apenas educacional
— Cria tabelas adicionais (relacionamentos não inclusos)
CREATE TABLE ClientesRG (
    ClienteID INT NOT NULL,
    ClienteRG VARCHAR(15) NOT NULL)

CREATE TABLE ClientesCPF (
    ClienteID INT NOT NULL,
    ClienteCPF CHAR(11) NOT NULL)

Nesse caso, sempre que um cliente tivesse um RG, o ID desse cliente e seu RG seriam cadastrados e o mesmo raciocínio valeria para o CPF. Embora do ponto de vista lógico seja uma boa alternativa, do ponto de vista físico essa alternativa provocaria diversos problemas com a necessidade frequente de vários joins o que possivelmente a inviabilizaria dependendo do volume de registros e da quantidade de chaves secundárias. Se aparecem outras chaves alternativas como o PIS ou NIS, seriam mais duas implementações. Acredito que a modelagem pode resolver muitos problemas, mas em minha opinião esse não é um deles. O uso da modelagem seria muito bom caso houvesse dependências funcionais que violassem a 3FN por conta das dependências transitivas, ou seja, a presença de atributos que sejam determinados por outros atributos que não os participantes da chave primária. Nesse caso, o uso de tabelas auxiliares seria muito bem vindo. Ex:

— Não executar esse script. Ele é apenas educacional
— Cria uma tabela auxiliar para armazenar o RG
CREATE TABLE ClientesRG (
    ClienteID INT NOT NULL,
    ClienteRG VARCHAR(15) NOT NULL,
    OrgaoExpedidor VARCHAR(7) NOT NULL,
    DataExpedicao SMALLDATETIME NOT NULL)

Nessa implementação, se um cliente não possuir RG, automaticamente as demais colunas serão nulas e portanto pode não fazer sentido armazenar todas essas colunas em uma tabela a parte. No caso de apenas o atributo RG, talvez a criação de uma tabela a parte não seja interessante pelas razões já expostas.

Solução Alternativa 2 – O uso de triggers

Normalmente, quando a chave secundária é representada por uma coluna opcional (NULL), o SQL Server não irá obedecer corretamente a lógica de três valores. Nessas situações, é muito comum que o implementador recorra à utilização de uma trigger. Ex:

— Cria uma trigger para checar a repetição
CREATE TRIGGER trgCadastraCliente ON Clientes
INSTEAD OF INSERT
AS
BEGIN

    — Informa os erros
    IF EXISTS (SELECT * FROM Clientes As C
        INNER JOIN INSERTED As I ON C.ClienteRG = I.ClienteRG)
    BEGIN
        RAISERROR(‘Há RGs duplicados nessa operação’,16,1)
        ROLLBACK
        RETURN
    END

    IF EXISTS (SELECT * FROM Clientes As C
        INNER JOIN INSERTED As I ON C.ClienteCPF = I.ClienteCPF)
    BEGIN
        RAISERROR(‘Há CPFs duplicados nessa operação’,16,1)
        ROLLBACK
        RETURN
    END

    — Cadastra todos os registros
    INSERT INTO Clientes
    SELECT * FROM INSERTED

END
GO

— Exclui os registros
DELETE FROM Clientes

— Efetua alguns cadastros (Há repetição de nulos no RG e no CPF)
INSERT INTO Clientes VALUES (1,‘Natanael’,‘14538543’,‘75712230466’)
INSERT INTO Clientes VALUES (2,‘Marcelo’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (3,‘Jonas’,NULL,NULL)
INSERT INTO Clientes VALUES (4,‘Luíza’,‘785123’,NULL)

— Verifica as inserções
SELECT ClienteID, ClienteNome, ClienteRG, ClienteCPF
FROM Clientes

— Tenta inserir registros com o CPF e o RG repetidos (valores conhecidos)
INSERT INTO Clientes VALUES (5,‘Juliana’,‘14538543’,‘82013423920’)
INSERT INTO Clientes VALUES (6,‘Joyce’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (7,‘Marta’,‘14538543’,NULL)

Msg 50000, Level 16, State 1, Procedure trgCadastraCliente, Line 11
Há RGs duplicados nessa operação
Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

Através da trigger, será feita uma checagem se o RG ou o CPF já estão cadastrados na tabela. O uso do EXISTS irá descartar registros com RG ou CPF nulos e portanto já descarta automaticamente registros cujo o RG ou o CPF sejam nulos dispensando-os da validação. Caso exista algum RG ou CPF preenchido, a transação é revertida e os registros não são cadastros. É necessário utilizar o EXISTS em oposição à captura de valores diretamente da tabela INSERTED. A trigger da forma que foi criada está preparada para INSERTs de conjuntos (INSERT com SELECT) e a captura direta dos valores da INSERTED com variáveis não trata essa situação. Se registros com RG e CPFs duplicados (desde que esses RGs e CPFs não sejam nulos) aparecerem, a trigger lançará uma exceção rejeitando-os.

Para que a trigger ganhe eficiência, é recomendável a criação de índices sobre as colunas consultadas (no caso o RG e o CPF). Ex:

— Cria índices sobre o RG e o CPF
CREATE INDEX IXClienteRG ON Clientes (ClienteRG)
CREATE INDEX IXClienteCPF ON Clientes (ClienteCPF)

— Elimina os objetos utilizados nessa solução
DROP TRIGGER trgCadastraCliente
DROP INDEX Clientes.IXClienteRG
DROP INDEX Clientes.IXClienteCPF

O uso de triggers é interessante para permitir que o SQL Server trabalhe "corretamente" com a lógica de três valores "emulando" unique constraints. O problema reside no desempenho. Já é sabido que triggers são péssimas escolhas quando o assunto é desempenho. Os índices podem ajudar, mas essa não seria a minha primeira escolha a menos que uma trigger já estivesse presente e a lógica de unicidade apenas fosse adicionada a essa trigger.

Solução Alternativa 3 – O uso de colunas calculadas

O SQL Server tem a capacidade de indexar colunas calculadas. Pressupondo que a chave primária nunca irá se repetir, é possível fazer uma combinação entre a chave primária e o RG para criar uma coluna de unicidade para o RG e utilizar a mesma lógica para o CPF. Ex:

— Cria duas colunas calculadas
ALTER TABLE Clientes ADD UCClienteRG As IsNull(ClienteRG,ClienteID)
ALTER TABLE Clientes ADD UCClienteCPF As IsNull(ClienteCPF,ClienteID)

— Cria dois índices únicos para as colunas recém criadas
CREATE UNIQUE INDEX IXUCClienteRG ON Clientes (UCClienteRG)
CREATE UNIQUE INDEX IXUCClienteCPF ON Clientes (UCClienteCPF)

— Exclui os registros
DELETE FROM Clientes

— Efetua alguns cadastros (Há repetição de nulos no RG e no CPF)
INSERT INTO Clientes VALUES (1,‘Natanael’,‘14538543’,‘75712230466’)
INSERT INTO Clientes VALUES (2,‘Marcelo’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (3,‘Jonas’,NULL,NULL)
INSERT INTO Clientes VALUES (4,‘Luíza’,‘785123’,NULL)

— Verifica os registros nas colunas calculadas
SELECT ClienteID, ClienteRG, ClienteCPF, UCClienteRG, UCClienteCPF FROM Clientes

Podemos visualizar o resultado da consulta conforme a tabela abaixo:

Cliente	ClienteRG	ClienteCPF	UCClienteRG	UCClienteCPF
1	14538543	75712230466	14538543	75712230466
2	NULL	82013423920	2	82013423920
3	NULL	NULL	3	3
4	785123	NULL	785123	4

Como a chave primária nunca irá se repetir e ela não pode ser nula, é possível utilizar as colunas calculadas de forma que se o RG ou o CPF forem nulos, utiliza-se a chave primária. Se eles não forem nulos, utiliza-se os próprios. Isso irá permitir a repetição de nulos, mas irá impedir a repetição de registros não nulos.

— Tenta inserir registros com o CPF e o RG repetidos (valores conhecidos)
INSERT INTO Clientes VALUES (5,‘Juliana’,‘14538543’,‘82013423920’)
INSERT INTO Clientes VALUES (6,‘Joyce’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (7,‘Marta’,‘14538543’,NULL)

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUCClienteRG’.
The statement has been terminated.
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUCClienteCPF’.
The statement has been terminated.
Msg 2601, Level 14, State 1, Line 3
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUCClienteRG’.
The statement has been terminated.

Como esses registros tentam inserir valores conhecidos de RG e CPF (NOT NULL) que já estão previamente cadastrados, um erro é retornado impedindo o cadastro.

Essa solução já é bem mais eficiente que as anteriores. A criação de índices sobre as colunas ClienteRG e ClienteCPF já era esperada, pois, dada a caraterística dessas colunas serem chaves secundárias é bem provável que sejam ótimas candidatas a indexação. Expandir a indexação para colunas calculadas não impõe overheads significativos e além de auxiliar a pesquisa de RG e CPF, também garante a unicidade.

As desvantagens sobre essa implementação residem no fato de que cada chave secundária significa em uma coluna a mais na tabela. Mesmo a coluna sendo calculada, o fato dela ser indexada fará com que ela exista fisicamente incorrendo em mais espaço. Há um problema adicional no caso de sobreposição de valores. Se existir uma chave primária 70035478765 e um CPF 70035478765 por exemplo, haverá problemas em relação a unicidade da coluna UCClienteCPF. Um pouco de criatividade pode facilmente superar essa limitação. Caso a chave primária tenha tipos diferentes das colunas supostamente únicas é recomendável utilizar um CAST para manter a tipagem correta.

O script abaixo retira as mudanças dessa implementação.

— Retira os índices
DROP INDEX Clientes.IXUCClienteRG
DROP INDEX Clientes.IXUCClienteCPF

— Elimina as colunas
ALTER TABLE Clientes DROP COLUMN UCClienteRG
ALTER TABLE Clientes DROP COLUMN UCClienteCPF

Solução Alternativa 4 – O uso de Views Indexadas

Essa alternativa é de excelente custo-benefício. Ela consiste basicamente em adotar um comportamento semelhante ao ORACLE, ou seja, impedir a repetição de valores conhecidos, permitir a repetição de valores desconhecidos, mas abrir mão das pesquisas com base em valores desconhecidos (mesmo que um índice esteja disponível). Vejamos como fazer isso:

— Cria uma View Indexada para impedir RGs nulos
CREATE VIEW vRG WITH SCHEMABINDING
AS SELECT ClienteRG FROM dbo.Clientes
WHERE ClienteRG IS NOT NULL

— Cria um índice sobre a View
CREATE UNIQUE CLUSTERED INDEX IXUCClienteRG ON vRG (ClienteRG)

— Cria uma View Indexada para impedir CPFs nulos
CREATE VIEW vCPF WITH SCHEMABINDING
AS SELECT ClienteCPF FROM dbo.Clientes
WHERE ClienteCPF IS NOT NULL

— Cria um índice sobre a View
CREATE UNIQUE CLUSTERED INDEX IXUCClienteCPF ON vCPF (ClienteCPF)

— Exclui os registros
DELETE FROM Clientes

— Efetua alguns cadastros (Há repetição de nulos no RG e no CPF)
INSERT INTO Clientes VALUES (1,‘Natanael’,‘14538543’,‘75712230466’)
INSERT INTO Clientes VALUES (2,‘Marcelo’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (3,‘Jonas’,NULL,NULL)
INSERT INTO Clientes VALUES (4,‘Luíza’,‘785123’,NULL)

— Tenta inserir registros com o CPF e o RG repetidos (valores conhecidos)
INSERT INTO Clientes VALUES (5,‘Juliana’,‘14538543’,‘82013423920’)
INSERT INTO Clientes VALUES (6,‘Joyce’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (7,‘Marta’,‘14538543’,NULL)

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.vCPF’ with unique index ‘IXUCClienteCPF’.
The statement has been terminated.
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object ‘dbo.vCPF’ with unique index ‘IXUCClienteCPF’.
The statement has been terminated.
Msg 2601, Level 14, State 1, Line 3
Cannot insert duplicate key row in object ‘dbo.vRG’ with unique index ‘IXUCClienteRG’.
The statement has been terminated.

Um dos pré-requisitos para indexar uma view é criar um índice único. O RG e o CPF sempre serão únicos, desde que os valores nulos fiquem ausentes (considerando a não conformidade com a lógica de três valores). Como as views ignoraram os valores nulos, o índice será único e impedirá o cadastros de RGs e CPFs repetidos desde que esses sejam conhecidos (NOT NULL).

O script a seguir elimina as views indexadas e seus índices

DROP VIEW vRG
DROP VIEW vCPF

Solução Alternativa 5 – O uso da cláusula WHERE no índice

Essa alternativa é praticamente igual a alternativa anterior. Penso que a possibilidade de utilizar a cláusula WHERE em um índice no SQL Server 2008 não é um nenhuma grande novidade. É apenas um "atalho" para evitar a criação da View, porém com uma sintaxe menor e mais simples. Enfim, valem as mesmas vantagens e desvantagens. Segue abaixo o script

— Criação de Índices com filtros
CREATE UNIQUE INDEX IXUClienteRG ON Clientes (ClienteRG) WHERE ClienteRG IS NOT NULL
CREATE UNIQUE INDEX IXUClienteCPF ON Clientes (ClienteCPF) WHERE ClienteCPF IS NOT NULL

— Exclui os registros
DELETE FROM Clientes

— Efetua alguns cadastros (Há repetição de nulos no RG e no CPF)
INSERT INTO Clientes VALUES (1,‘Natanael’,‘14538543’,‘75712230466’)
INSERT INTO Clientes VALUES (2,‘Marcelo’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (3,‘Jonas’,NULL,NULL)
INSERT INTO Clientes VALUES (4,‘Luíza’,‘785123’,NULL)

— Tenta inserir registros com o CPF e o RG repetidos (valores conhecidos)
INSERT INTO Clientes VALUES (5,‘Juliana’,‘14538543’,‘82013423920’)
INSERT INTO Clientes VALUES (6,‘Joyce’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (7,‘Marta’,‘14538543’,NULL)

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUClienteRG’.
The statement has been terminated.
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUClienteCPF’.
The statement has been terminated.
Msg 2601, Level 14, State 1, Line 3
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUClienteRG’.
The statement has been terminated.

O uso da cláusula WHERE nos índices elimina os valores nulos. A propriedade unique diz que não deve haver repetições. O conjunto da cláusula com a propriedade faz com que os valores conhecidos (NOT NULL) não possam se repetir, mas em contrapartida não impõe nenhuma regra para os valores desconhecidos (NULL).

Como pode ser observado, o SQL Server considera valores nulos como iguais na utilização de constraints unique em oposição a lógica de três valores. A justificativa reside no fato de que a implementação física dessa constraint é baseada em índices e como o SQL Server tem a habilidade de utilizar o índice para valores nulos, o efeito colateral é o comportamento adverso nas unique constraints. Isso não significa que não seja possível manter a unicidade e ignorar os nulos em uma coluna. Há várias alternativas para isso. Só é preciso procurar a implementação correta.

[ ]s,

Gustavo

Como realizar cálculos com horas no SQL Server – Parte IV

Boa Tarde Pessoal,

No último artigo abordei mais uma situação envolvendo cálculos horários no SQL Server. Creio que os três primeiros artigos relacionados a esse assunto já sejam subsídio suficiente para boa parte das dúvidas que aparecem nos fóruns, grupos de usuários e comunidades. Na parte final desse tema, farei uso do CLR para resolver algumas das situações postadas no último artigo. A idéia não é resolver os problemas com o CLR já que o TSQL é uma melhor alternativa para os exemplos abordados até então. O objetivo é exemplificar algumas utilizações do CLR (dedico aos meus alunos que sempre reclamam que os MOCs não tem algo muito prático nesse assunto). Não vou discorrer a respeito do que seja o CLR (isso já foi tratado em vários Webcasts e artigos anteriores). Quem desejar conhecer mais sobre CLR, recomendo uma leitura do artigo "Como e quando programar em CLR no Microsoft SQL Server 2005". Também já postei alguns outros artigos que utilizam o CLR ("Utilização de Matrizes", "Importação de Arquivos XML" por exemplo e mais recentemente "Como realizar cálculos com horas no SQL Server").

No último exemplo de cálculos horários foi exposta uma situação em que era necessário informar a quantidade de horas e minutos gasta em um determinado processo. Conforme apresentado, os tipos SMALLDATETIME (ou DATETIME) e tipos inteiros eram adequados enquanto o tipo de dados TIME era inadequado. Que outros tipos poderiam ser utilizados fora os apresentados ? O CHAR até poderia ser utilizado se o objetivo fosse apenas apresentar os dados, mas considerando que cálculos serão realizados, o tipo de dados CHAR não é uma escolha factível. Os tipos nativos ficam portanto esgotados restando portanto tipos próprios quer sejam User Defined Types (CLR) ou User Defined Data Types (TSQL). A utilização de User Defined Data Types não representa nenhuma alternativa visto que os tipos base (System Data Types) não oferecem nenhuma outra possibilidade que já não tenha sido apresentada (SMALLDATETIME, DATETIME, TINYINT e TIME). Resta então a implementação de tipos utilizando o CLR (User Defined Types).

Através do CLR, pode-se implementar um tipo de dados próprio para viabilizar as operações desejadas. O código abaixo cria esse tipo de dados que chamarei de CargaHoraria.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct CargaHoraria : INullable
{
    private SqlInt32 vHoras;
    private SqlInt32 vMinutos;
    private bool Nulo;

    public override string ToString()
    {
        // Retorna o horário formatado "hh:mm"
                string chr = Horas.ToString() + ":" + Minutos.ToString();

        if (Horas < 10)
        {
            chr = "0" + chr;
        }

        if (Minutos < 10)
        {
            chr = chr.Replace(":", ":0");
        }

        return chr;

    }

    // Implementa a propriedade de verificação de valor nulo
    public bool IsNull
    {
        get
        {
            return Nulo;
        }
    }

    // Retorna a quantidade de horas
    public SqlInt32 Horas
    {
        get
        {
            return vHoras;
        }
        set
        {
            vHoras = value;
        }
    }

    // Retorna a quantidade de minutos
    public SqlInt32 Minutos
    {
        get
        {
            return vMinutos;
        }
        set
        {
            //Se houver mais de sessenta minutos um erro é gerado
            if (value > 60)
            {
                throw new ArgumentException("A quantidade máxima de minutos é de 60");
            }
            else
            {
                vMinutos = value;
            }
        }
    }

    // Tratamento para uma atribuição de valor nulo
    public static CargaHoraria Null
    {
        get
        {
            CargaHoraria ch = new CargaHoraria();
            ch.Nulo = true;
            return ch;
        }
    }

    // Inicializa a classe
    public static CargaHoraria Parse(SqlString Valor)
    {
        // Se o valor informado for nulo retorna nulo
        if (Valor.IsNull)
            return Null;

        // Instancia uma nova classe (o formato hh:mm é esperado)
        try
        {
            CargaHoraria ch = new CargaHoraria();

            int Result;

            // Verifica se o parâmetro é numérico
            if (Int32.TryParse(Valor.ToString(), out Result))
            {
                ch.Horas = Result / 60;
                ch.Minutos = Result % 60;
            }

            else
            {
                // Localiza o caractér ":"
                int Pos = Valor.ToString().IndexOf(":");

                // Captura o total de horas
                SqlInt32 Horas = SqlInt32.Parse(Valor.ToString().Substring(0, Pos));

                // Captura o total de minutos
                SqlInt32 Minutos = SqlInt32 .Parse(
                    Valor.ToString().Substring(Pos + 1, (Valor.ToString().Length – Pos – 1)));

                ch.Horas = Horas;
                ch.Minutos = Minutos;
            }
            return ch;
        }

        catch
        {
            throw new ArgumentException("A inicialização do tipo carga horária está incorreta");
        }
    }

    // Método para retorno do total em minutos
    public SqlInt32 TotalMinutos()
    {
        return (Horas * 60) + Minutos;
    }
}

Esse tipo de dados possui duas propriedades inteiras (Horas e Minutos). Após instanciá-lo é possível também utilizar dois métodos. O primeiro retorna o dados formatado e o segundos mostra o total de minutos computados. É possível também inicializar o tipo com o formato hh:mm ou com uma quantidade fixa de minutos. Antes que ele possa ser utilizado é necessário compilá-lo. Para não haver dependência do Visual Studio, o procedimento para compilar esse código envolve os seguintes passos:

• Copiar e colar o código no bloco de notas
• Salvar o arquivo como CalculaCargaHoraria.cs em algum diretório (ex: G:\CLR\)
• Abrir um prompt de comando
• Navegar até o diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou superior se desejar)
• Rodar o comando csc /target:library G:\CLR\CalculaCargaHoraria.cs (supondo que o arquivo esteja em G:\CLR\)
• Localizar o arquivo CalculaSomaHoras.dll na pasta utilizada no prompt
• Mover a DLL para um local mais apropriado (ex: G:\CLR\)

O próximo passo é registrar o ASSEMBLY para que o código possa ser utilizado e posteriormente a criação do novo tipo de dados.

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY CH FROM ‘G:\CLR\CalculaCargaHoraria.dll’

— Cria um novo tipo CargaHoraria a partir do Assembly
CREATE TYPE dbo.CargaHoraria
EXTERNAL NAME CH.CargaHoraria

Agora que o tipo de dados está devidamente registrado, é possível utilizá-lo. O script abaixo mostra alguma de suas potencialidades (dessea vez a quantidade de horas não está restrita a 24).

— Declara uma variável do tipo "CargaHoraria"
DECLARE @CH dbo.CargaHoraria

— Inicializa a variável com um valor no formato hh:mm
SET @CH = ’50:30′

— Mostra as propriedades e os métodos
SELECT @CH.Horas As Horas, @CH.Minutos As Minutos,
    @CH.TotalMinutos() As TotalMinutos, @CH.ToString() As Horario

— Inicializa a variável com um valor em quantidade de minutos
SET @CH = ‘635’

— Mostra as propriedades e os métodos
SELECT @CH.Horas As Horas, @CH.Minutos As Minutos,
    @CH.TotalMinutos() As TotalMinutos, @CH.ToString() As Horario

Após o teste, é possível visualizar que o tipo de dados está totalmente funcional e pode ser utilizado não somente na declaração de variáveis, mas como parâmetros de Stored Procedures, Functions e colunas de tabelas. A última possibilidade é a que mais interessa para esse artigo. Abaixo o script de criação de tabelas e devidos registros com esse novo tipo de dados.

CREATE TABLE Deptos (IDDepto INT, NomeDepto VARCHAR(50))
CREATE TABLE Processos (IDProcesso INT, IDDepto INT, NomeProcesso VARCHAR(200))
CREATE TABLE Medicoes (IDMedicao INT, IDProcesso INT, Duracao dbo.CargaHoraria)

INSERT INTO Deptos VALUES (1,‘RH’)
INSERT INTO Deptos VALUES (2,‘Financeiro’)

INSERT INTO Processos VALUES (1,1,‘Avaliar Currículos’)
INSERT INTO Processos VALUES (2,1,‘Efetuar Cálculos Trabalhistas’)
INSERT INTO Processos VALUES (3,1,‘Realizar Provas Práticas’)
INSERT INTO Processos VALUES (4,2,‘Conciliar Pagamentos Bancários’)
INSERT INTO Processos VALUES (5,2,‘Conferir Ordens de Pagamento’)

INSERT INTO Medicoes VALUES (01,1,’04:00′)
INSERT INTO Medicoes VALUES (02,1,’05:30′)
INSERT INTO Medicoes VALUES (03,1,’03:45′)
INSERT INTO Medicoes VALUES (04,2,’15:00′)
INSERT INTO Medicoes VALUES (05,2,’13:35′)
INSERT INTO Medicoes VALUES (06,3,’08:00′)
INSERT INTO Medicoes VALUES (07,4,’40:00′)
INSERT INTO Medicoes VALUES (08,4,’36:00′)
INSERT INTO Medicoes VALUES (09,4,’37:25′)
INSERT INTO Medicoes VALUES (10,4,’38:15′)
INSERT INTO Medicoes VALUES (11,5,’12:10′)
INSERT INTO Medicoes VALUES (12,5,’13:20′)

Após inserir as linhas no novo tipo de dados é perfeitamente possível utilizar alguns dos métodos propostos.

SELECT
    NomeDepto, NomeProcesso, Duracao,
    Duracao.ToString() As ToString,
    Duracao.TotalMinutos() As TotalMinutos
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Medicoes ON Processos.IDProcesso = Medicoes.IDProcesso

O resultado é exposto na tabela abaixo (apenas parte dos registros)

NomeDepto	NomeProcesso	Duracao	ToString	TotalMinutos
RH	Avaliar Currículos	0x0180000004018000000000	04:00	240
RH	Efetuar Cálculos Trabalhistas	0x018000000F018000000000	15:00	900
RH	Realizar Provas Práticas	0x0180000008018000000000	08:00	480
Financeiro	Conciliar Pagamentos Bancários	0x0180000028018000000000	40:00	2400
Financeiro	Conferir Ordens de Pagamento	0x018000000C018000000A00	12:10	730

Os curiosos para saber o que tem na coluna podem ver que se trata de um binário difícil de decifrar, mas que se utilizarmos os métodos podemos extrair os dados que realmente interessam. O método "ToString" mostra exatamente o valor no formato hh:mm enquanto o método "TotalMinutos" mostra a quantidade de minutos presente em cada registro. De posse desses valores é perfeitamente possível efetuar somatórios.

SELECT
    NomeDepto, NomeProcesso, COUNT(Duracao) As Quantidade,
    AVG(Duracao.TotalMinutos()) As DuracaoMedia
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Medicoes ON Processos.IDProcesso = Medicoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso

O resultado é exposto na tabela abaixo:

NomeDepto	NomeProcesso	Quantidade	DuracaoMedia
Financeiro	Conciliar Pagamentos Bancários	4	2275
Financeiro	Conferir Ordens de Pagamento	2	765
RH	Avaliar Currículos	3	265
RH	Efetuar Cálculos Trabalhistas	2	857
RH	Realizar Provas Práticas	1	480

É possível converter a coluna "DuracaoMedia" para o formato hh:mm através das fórmulas anteriores. Ex:

;WITH Duracoes (NomeDepto, NomeProcesso, Quantidade, Media)
As (
SELECT
    NomeDepto, NomeProcesso, COUNT(Duracao) As Quantidade,
    AVG(Duracao.TotalMinutos()) As DuracaoMedia
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Medicoes ON Processos.IDProcesso = Medicoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso)

SELECT NomeDepto, NomeProcesso, Quantidade,
    RIGHT(‘0’ + CAST((Media / 60) As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST((Media % 60) As VARCHAR(2)),2) As DuracaoMedia
FROM Duracoes

Essa fórmula foi usada nos artigos anteriores, mas ela possui algumas limitações. Ele funcionará bem para formatos como hh:mm mas basta que algum processo supere a quantidade de 99 horas e o formato hh:mm irá falhar. É possível rearranjar a formatação, mas como há um tipo em CLR que pode ser inicializado com uma quantidade de minutos, ele também pode ser utilizado.

;WITH Duracoes (NomeDepto, NomeProcesso, Quantidade, Media, Total)
As (
SELECT
    NomeDepto, NomeProcesso, COUNT(Duracao) As Quantidade,
    AVG(Duracao.TotalMinutos()) As DuracaoMedia,
    SUM(Duracao.TotalMinutos()) As DuracaoTotal
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Medicoes ON Processos.IDProcesso = Medicoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso),

Resultado (Depto, Processo, Quantidade, DMedia, DTotal)
As (
SELECT
    NomeDepto, NomeProcesso, Quantidade,
    CONVERT(dbo.CargaHoraria,CAST(Media As VARCHAR(7))),
    CONVERT(dbo.CargaHoraria,CAST(Total As VARCHAR(7)))
FROM Duracoes)

SELECT
    Depto, Processo, Quantidade,
    DMedia.ToString() As DuracaoMedia,
    DTotal.ToString() As DuracaoTotal
FROM Resultado

O resultado final é exposto na tabela abaixo:

NomeDepto	NomeProcesso	Quantidade	DuracaoMedia	DuracaoTotal
Financeiro	Conciliar Pagamentos Bancários	4	37:55	151:40
Financeiro	Conferir Ordens de Pagamento	2	12:45	25:30
RH	Avaliar Currículos	3	04:25	13:15
RH	Efetuar Cálculos Trabalhistas	2	14:17	28:35
RH	Realizar Provas Práticas	1	08:00	08:00

Se a fórmula que utilizou o RIGHT fosse aplicada, a situação da coluna "Duração Total" do primeiro registro não será apresentada corretamente. Com as conversões para o tipo "CargaHoraria" é possível repassar como parâmetro uma quantidade de minutos através de uma string. Poderia ser utilizado o tipo INT diretamente, mas provocar uma sobrecarga do método no CLR é "trabalho demais". Abaixo a consulta utilizando o RIGHT.

;WITH Duracoes (NomeDepto, NomeProcesso, Quantidade, Media, Total)
As (
SELECT
    NomeDepto, NomeProcesso, COUNT(Duracao) As Quantidade,
    AVG(Duracao.TotalMinutos()) As DuracaoMedia,
    SUM(Duracao.TotalMinutos()) As DuracaoTotal
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Medicoes ON Processos.IDProcesso = Medicoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso)

SELECT NomeDepto, NomeProcesso, Quantidade,

    CASE WHEN (Media / 60) <= 10 THEN
        RIGHT(‘0’ + CAST((Media / 60) As VARCHAR(7)),7)
    ELSE
        CAST((Media / 60) As VARCHAR(7))
    END + ‘:’ +

    CASE WHEN (Media % 60) <= 10 THEN
        RIGHT(‘0’ + CAST((Media % 60) As VARCHAR(7)),7)
    ELSE
        CAST((Media % 60) As VARCHAR(7))
    END As DuracaoMedia,

    CASE WHEN (Total / 60) <= 10 THEN
        RIGHT(‘0’ + CAST((Total / 60) As VARCHAR(7)),7)
    ELSE
        CAST((Total / 60) As VARCHAR(7))
    END + ‘:’ +

    CASE WHEN (Total % 60) <= 10 THEN
        RIGHT(‘0’ + CAST((Total % 60) As VARCHAR(7)),7)
    ELSE
        CAST((Total % 60) As VARCHAR(7))
    END As DuracaoTotal

FROM Duracoes

Após essa última consulta é possível ver que o uso do CLR também é muito bem vindo em situações do dia-a-dia. Normalmente esse caso se beneficiaria melhor das construções e tipos nativos do SQL Server, mas o intuito é demonstrar que o CLR também pode ser utilizado.

[ ]s,

Gustavo