Arquivo da categoria: Implementação (SQL Server)

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

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

Olá Pessoal,

Nos artigos anteriores demonstrei como realizar cálculos com horas no SQL Server. Os dois artigos tinham uma característica em comum. Todas as situações expostas informavam determinados momentos no tempo e era necessário calcular a diferença entre esses tempos. Existem algumas situações em que o momento no tempo é desconhecido ou ainda irrelevante. Se um serviço é contratado para durar quarenta horas ou ainda se dispõe de 40 horas de um determinado profissional, o único dado conhecido é a quantidade de 40 horas. Nessa situação, a quantidades de horas é diretamente lançada e as abordagens anteriores não podem ser aplicadas (ou tem de ser pelo menos adaptadas). Embora talvez pareça um problema simples, complexidades podem surgir quando existe a presença de outras unidades (minutos ou segundos por exemplo). Nesse artigo trabalharei essas situações.

Os exemplos irão avaliar um projeto da empresa XYZ Ltda. Essa empresa está realizando um estudo externo para medir a eficiência de alguns departamentos e seus processos internos. Antes de propriamente realizar qualquer proposta ou intervenção, é necessário saber as principais tarefas de cada um dos departamentos avaliados bem como o tempo gasto nesses processos. Para evitar possíveis distorções, os processos avaliados são repetidos e seu tempo contabilizado diversas vezes. Com isso pode ser obtido um tempo médio, desvios padrões, etc. Os processos avaliados são o de curta duração e não é admissível que demorem mais do que um dia. A proposta de modelagem física é exposta abaixo:

CREATE TABLE Deptos (IDDepto INT, NomeDepto VARCHAR(50))
CREATE TABLE Processos (IDProcesso INT, IDDepto INT, NomeTarefa VARCHAR(200))
CREATE TABLE Medicoes (IDMedicao INT, IDProcesso INT, Duracao ?)

A escolha do tipo de dados

Se observarmos o script proposto veremos que a coluna "Duracao" está com um tipo dados indeterminado (inclusive o script falha se for executado). Qual seria o tipo de dados correto ? Já vi implementações do tipo CHAR(8), DATETIME ou no caso do SQL Server 2008 a utilização do tipo TIME. O tipo CHAR(8) representa o formato hh:mm:ss mas definitivamente é uma péssima escolha. Armazenar uma duração de um processo com o tipo CHAR(8) irá causar vários problemas uma vez que embora o formato seja perfeitamente compreendido por pessoas, nenhum banco de dados saberá que trata-se de uma duração. Todos os cálculos irão provocar uma conversão desse tipo de dados para outro tipo de dados de forma a tornar os cálculos possíveis (não é possível fazer cálculos matemáticos com CHAR diretamente) e isso provocará um overhead de conversões além de possíveis problemas com a indexação. Assim sendo, tendo em vista os problemas estou desconsiderando essa possibilidade. Resta então o clássico SMALLDATETIME, tipos inteiros e o TIME do SQL Server 2008. Vejamos então as possibilidades:

Horários com base em colunas do tipo SMALLDATETIME

O tipo de dados SMALLDATETIME armazena uma data seguida de um horário. Como deseja-se apenas o horário, ignora-se a data (possivelmente será assumido 01/01/1900) e lança-se o horário diretamente. Ex:

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 SMALLDATETIME)

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

Ao executar o script, algumas registros retornam a seguinte mensagem de erro

Msg 296, Level 16, State 3, Line 20
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

The statement has been terminated.

O primeiro registro a provocar essa mensagem de erro é o referente a 40 horas. A string "40:00:00" é um horário válido e perfeitamente aceitável (a medição retornou 40 horas), mas o tipo de dados é SMALLDATETIME e nesse caso, não é tolerado a existência de um valor de 40 horas. É necessário entender que se trata de 1 dia (24 horas) e mais 16 horas restantes. Esse é um dos problemas graves de se utilizar SMALLDATETIME (ou DATETIME) para essa situação. Tais tipos de dados são excelentes para registrar-se um momento, mas não um intervalo. Para que esses tipos continuem a ser utilizados, é necessário algumas adaptações.

— Exclui os registros anteriores
DELETE FROM Medicoes

— Insere os registros no novo formato
INSERT INTO Medicoes VALUES (01,1,‘19000101 04:00:00’)
INSERT INTO Medicoes VALUES (02,1,‘19000101 05:30:00’)
INSERT INTO Medicoes VALUES (03,1,‘19000101 03:45:00’)
INSERT INTO Medicoes VALUES (04,2,‘19000101 15:00:00’)
INSERT INTO Medicoes VALUES (05,2,‘19000101 13:35:00’)
INSERT INTO Medicoes VALUES (06,3,‘19000101 08:00:00’)
INSERT INTO Medicoes VALUES (07,4,‘19000102 16:00:00’)
INSERT INTO Medicoes VALUES (08,4,‘19000102 12:00:00’)
INSERT INTO Medicoes VALUES (09,4,‘19000102 13:25:00’)
INSERT INTO Medicoes VALUES (10,4,‘19000102 14:15:00’)
INSERT INTO Medicoes VALUES (11,5,‘19000101 12:10:00’)
INSERT INTO Medicoes VALUES (12,5,‘19000101 13:20:00’)

O novo formato permite que a duração das medições seja inserida, mas já é possível perceber alguns inconvenientes. Durações superiores a 24 horas irão exigir uma certa inteligência para "calcular" o dia e a hora correta de forma a representar a duração desejada. É um problema considerável, mas ainda assim não inviabiliza completamente a utilização desse tipo de dados. Se a referência (data zero) é 01/01/1900, pode-se subtrair essa data da coluna duração e obter uma diferença em minutos através da função DATEDIFF.

— Calcula duração em minutos
;WITH Duracoes (IDMedicao, IDProcesso, DuracaoMin)
AS (
    SELECT IDMedicao, IDProcesso, DATEDIFF(MI,‘19000101’,Duracao)
    FROM Medicoes)

— Mostra o total de contagens e uma média
SELECT
    NomeDepto, NomeProcesso, COUNT(IDMedicao) As Quantidade,
    AVG(DuracaoMin) As Media
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Duracoes ON Processos.IDProcesso = Duracoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso

Os resultados são expressos em minutos. Pode ser desejável apresentá-los no formato hh:mm por exemplo. Algumas adaptações podem tornar isso possível:

— Calcula duração em minutos
;WITH Duracoes (IDMedicao, IDProcesso, DuracaoMin)
AS (
    SELECT IDMedicao, IDProcesso, DATEDIFF(MI,‘19000101’,Duracao)
    FROM Medicoes),

Resultados As (

— Mostra o total de contagens e uma média
SELECT
    NomeDepto, NomeProcesso, COUNT(IDMedicao) As Quantidade,
    AVG(DuracaoMin) As Media
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Duracoes ON Processos.IDProcesso = Duracoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso)

— Formata os resultados
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 Resultados

O resultado é exibido na tabela abaixo:

Depto

Processo

Quantidade

Duração Média

Financeiro

Conciliar Pagamentos Bancários

4

37:55

Financeiro

Conferir Ordens de Pagamento

2

12:45

RH

Avaliar Currículos

3

04:25

RH

Efetuar Cálculos Trabalhistas

2

14:17

RH

Realizar Provas Práticas

1

08:00

Horários com base em colunas do tipo inteiro

Essa abordagem é simples, direta e não sei porque razão é a menos escolhida por muitos administradores de dados (às vezes sequer é pensada por eles). Parece que os tipos de dados relacionados a tempo são preferidos e representam automaticamente a única alternativa (sem dúvida uma visão bem distorcida). Vejamos a abordagem com base em inteiros.

— Exclui a tabela de Medicoes
DROP TABLE Medicoes

— Recria a tabela
CREATE TABLE Medicoes (IDMedicao INT, IDProcesso INT, Horas TINYINT, Minutos TINYINT)

— Insere os registros
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)

Os cálculos ficam muito mais simples que a abordagem anterior (até o armazenamento fica mais reduzido, pois, o TINYINT ocupa um byte).

;WITH Resultados As (
SELECT
    NomeDepto, NomeProcesso, COUNT(IDMedicao) As Quantidade,
    AVG((Horas * 60) + Minutos) As Media
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Medicoes ON Processos.IDProcesso = Medicoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso)

— Formata os resultados
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 Resultados

Horários com base no tipo de dados TIME do SQL Server 2008

A abordagem com o tipo de dados TIME é bem semelhante a do tipo SMALLDATETIME.

— Exclui a tabela
DROP TABLE Medicoes

— Recria a tabela
CREATE TABLE Medicoes (IDMedicao INT, IDProcesso INT, Duracao TIME(0))

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

Entretanto, embora sintaticamente correto, a execução desse script gera um erro

Msg 241, Level 16, State 1, Line 11
Conversion failed when converting date and/or time from character string.

O erro é gerado na linha cujo ID é 7 e duração de 40 horas. A string "40:00:00" representa 40 horas, mas o tipo de dados TIME não foi preparado para essa representação. Não se trata de uma limitação, mas sim de um propósito. O tipo de dados TIME deve representar um momento no tempo (apenas da parte horária desconsiderando a data) e não uma quantidade de horas. A abordagem com o tipo de dados SMALLDATETIME possui o mesmo sentido, mas caso a quantidade de horas fosse superior a 24, bastaria adicionar um dia a mais. A abordagem com o tipo de dados TIME é análoga, mas não pode utilizar esse artifício e portanto, caso haja uma quantidade de horas superior à 24, esse tipo de dados é inapropriado. Supondo a retirada do processo 4, é possível verificar como se comporta a utilização do tipo de dados TIME.

— Exclui os registros anteriores
DELETE FROM Medicoes

— Insere os registros
INSERT INTO Medicoes VALUES (01,1,’04:00:00′)
INSERT INTO Medicoes VALUES (02,1,’05:30:00′)
INSERT INTO Medicoes VALUES (03,1,’03:45:00′)
INSERT INTO Medicoes VALUES (04,2,’15:00:00′)
INSERT INTO Medicoes VALUES (05,2,’13:35:00′)
INSERT INTO Medicoes VALUES (06,3,’08:00:00′)
INSERT INTO Medicoes VALUES (11,5,’12:10:00′)
INSERT INTO Medicoes VALUES (12,5,’13:20:00′)

As consultas utilizam a mesma lógica do tipo de dados SMALLDATETIME, ou seja, utiliza-se um valor base para calcular a diferença. Com o tipo de dados SMALLDATETIME, utilizou-se a data de 01/01/1900 (mais precisamente 01/01/1900 00:00). No caso do tipo de dados TIME, utilizá-se como referência o horário 00:00:

— Calcula duração em minutos
;WITH Duracoes (IDMedicao, IDProcesso, DuracaoMin)
AS (
    SELECT IDMedicao, IDProcesso, DATEDIFF(MI,’00:00:00′,Duracao)
    FROM Medicoes),

Resultados As (

— Mostra o total de contagens e uma média
SELECT
    NomeDepto, NomeProcesso, COUNT(IDMedicao) As Quantidade,
    AVG(DuracaoMin) As Media
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Duracoes ON Processos.IDProcesso = Duracoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso)

— Formata os resultados
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 Resultados

O resultado é exibido na tabela abaixo:

Depto

Processo

Quantidade

Duração Média

Financeiro

Conferir Ordens de Pagamento

2

12:45

RH

Avaliar Currículos

3

04:25

RH

Efetuar Cálculos Trabalhistas

2

14:17

RH

Realizar Provas Práticas

1

08:00

Como pode ser percebido, o tipo de dados TIME retorna os mesmos dados do SMALLDATETIME, mas com a limitação de 23:59 no máximo antes de gerar uma falha de conversão.

Juntamente com os dois artigos anteriores, a idéia é realmente fazer um overview de como realizar cálculos que envolvam o tempo no SQL Server. De forma nenhuma o assunto está esgotado, até porque existem outras unidades temporais que não foram trabalhadas (dias, segundos, etc) e algumas outras alternativas que não foram apresentadas. Os artigos já servem como um ponto de partida para cálculos horários mais complexos.

[ ]s,

Gustavo

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

Boa Tarde Pessoal,

No artigo anterior, demonstrei como realizar cálculos com horas no SQL Server. Havia duas abordagens, uma baseada em colunas e outra baseada em linhas. Nesse artigo continuarei a explorar o exemplo anterior baseado em linhas bem como mostrar outras soluções e considerações em relação ao SQL Server 2008. Segue o script a ser utilizado (caso as tabelas do artigo anterior não estejam mais disponíveis):

CREATE TABLE tblRegHorarios (
    Matricula CHAR(5),
    Data SMALLDATETIME,
    Horario SMALLDATETIME,
    Tipo CHAR(1))

INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090330’,‘20090330 08:00’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090330’,‘20090330 12:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090330’,‘20090330 14:00’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090330’,‘20090330 18:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090331’,‘20090331 08:00’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090331’,‘20090331 12:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090331’,‘20090331 14:00’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090331’,‘20090331 16:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090331’,‘20090331 16:45’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090331’,‘20090331 19:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090401’,‘20090401 08:00’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090401’,‘20090401 10:13’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090401’,‘20090401 11:45’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090401’,‘20090401 12:45’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090402’,‘20090402 09:07’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090402’,‘20090402 12:31’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090402’,‘20090402 14:11’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090402’,‘20090402 18:19’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090403’,‘20090403 07:43’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090403’,‘20090403 12:37’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090403’,‘20090403 13:28’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090403’,‘20090403 15:03’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090403’,‘20090403 17:14’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090403’,‘20090403 20:38’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 08:30’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 12:17’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 13:32’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 16:21’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 17:53’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 18:30’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 19:30’,‘E’)

INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 20:30’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090331’,‘20090331 07:51’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090331’,‘20090331 12:03’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090331’,‘20090331 13:58’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090331’,‘20090331 18:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090401’,‘20090401 08:00’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090401’,‘20090401 10:13’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090401’,‘20090401 11:45’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090401’,‘20090401 12:45’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090402’,‘20090402 08:37’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090402’,‘20090402 11:31’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090402’,‘20090402 13:24’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090402’,‘20090402 19:19’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090403’,‘20090403 09:32’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090403’,‘20090403 12:49’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090403’,‘20090403 13:51’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090403’,‘20090403 17:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090403’,‘20090403 17:45’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090403’,‘20090403 19:38’,‘S’)

A solução em TSQL apontada foi.

;WITH LancamentosHorarios As (
SELECT
    Matricula, Data, Horario, Tipo,
    ROW_NUMBER() OVER (
        PARTITION BY Data, Matricula
        ORDER BY Data, Matricula, Horario) As Pos
FROM tblRegHorarios),

LancamentosOrganizados As (

SELECT
    L1.Matricula, L1.Data,
    L1.Horario As Entrada, L2.Horario As Saida
FROM
    LancamentosHorarios As L1
    INNER JOIN LancamentosHorarios As L2 ON
        L1.Matricula = L2.Matricula AND L1.Data = L2.Data AND
        L1.Pos = L2.Pos – 1 AND L1.Pos % 2 = 1)
SELECT Matricula, SUM(DateDiff(Mi,Entrada,Saida)) As CargaHoraria
FROM LancamentosOrganizados GROUP BY matricula

A solução é interessante e o raciocínio também não é difícil. Como disse era apenas para exercitar, pois, embora uma boa solução ela é trabalhosa e um pouco dispendiosa. Essa solução foi elaborada dessa forma, pois, não havia como realizar SUM diretamente contras as colunas de data. Outra limitação que levou a essa solução é que o DateDiff não pode ser aplicado diretamente a linhas diferentes.

Há uma outra solução em TSQL mais simples e menos dispendiosa. Essa solução depende de um simples raciocínio matemático:

Se C – B = A então (C + Y) – (B + Y) = A já que C + Y – B – Y = C – B

Tudo o que fiz foi adicionar um valor Y às variáveis C e B sem desequilibrar a equação. É como se eu perguntasse qual a diferença de idade entre o SQL Server e o ORACLE. Hoje a diferença entre eles é de dez anos (o Oracle data de 1979 e o SQL Server de 1989). Se eu fizer essa pergunta daqui a 5, 10 ou 15 anos, a resposta será sempre a mesma, ou seja, independente de quando a pergunta é feita, a diferença será sempre de dez anos. Qual seria a relação desse raciocínio com o cálculo das horas ? Vejamos o resultado da consulta abaixo:

SELECT Matricula, Data, Horario, Tipo,
DateDiff(MI,Data,Horario) As Dif
FROM tblRegHorarios
WHERE Matricula = ‘M0001’ AND Data = ‘20090330’

O resultado tabular é expresso abaixo:

Matrícula

Data

Horário

Tipo

Dif

M0001

2009-03-30 00:00:00

2009-03-30 08:00:00

E

480

M0001

2009-03-30 00:00:00

2009-03-30 12:00:00

S

720

M0001

2009-03-30 00:00:00

2009-03-30 14:00:00

E

840

M0001

2009-03-30 00:00:00

2009-03-30 18:00:00

S

1080

A diferença entre "2009-03-30 00:00" e "2009-03-30 08:00" é de 480 minutos. A diferença entre "2009-03-30 00:00" e "2009-03-30 12:00" é de 720 minutos. A idéia é subtrair "2009-03-30 08:00" de "2009-03-30 12:00" que totaliza 4 horas. A diferença entre a primeira entrada e a primeira saída será sempre de 4 horas independente se há ou não uma subtração ou adição para desses dois momentos. É um pouco abstrato admitir uma data negativa, mas se os resultados estão expressos como inteiros (o retorno do DateDiff) então pode-se fazer uma subtração. Ex:

SELECT Matricula, Data, Horario, Tipo,
CASE Tipo WHEN ‘S’ THEN DateDiff(MI,Data,Horario)
ELSEDateDiff(MI,Data,Horario) END As Dif
FROM tblRegHorarios
WHERE Matricula = ‘M0001’ AND Data = ‘20090330’

O resultado tabular é expresso abaixo:

Matrícula

Data

Horário

Tipo

Dif

M0001

2009-03-30 00:00:00

2009-03-30 08:00:00

E

-480

M0001

2009-03-30 00:00:00

2009-03-30 12:00:00

S

720

M0001

2009-03-30 00:00:00

2009-03-30 14:00:00

E

-840

M0001

2009-03-30 00:00:00

2009-03-30 18:00:00

S

1080

A consulta negativou a diferença quando se trata de uma entrada, agora o cálculo fica bem mais simples. Ex:

;WITH LancamentosHorarios As (
    SELECT Matricula, Data, Horario, Tipo,
    CASE Tipo WHEN ‘S’ THEN DateDiff(MI,Data,Horario)
    ELSEDateDiff(MI,Data,Horario) END As Dif
    FROM tblRegHorarios)

SELECT Matricula, Data, SUM(Dif) As Dif
FROM LancamentosHorarios GROUP BY Matricula, Data

Para um resultado mais apresentável, pode-se evoluir o código:

;WITH LancamentosHorarios As (
    SELECT Matricula, Data, Horario, Tipo,
    CASE Tipo WHEN ‘S’ THEN DateDiff(MI,Data,Horario)
    ELSEDateDiff(MI,Data,Horario) END As Dif
    FROM tblRegHorarios)

SELECT Matricula,
    RIGHT(‘0’ + CAST(SUM(Dif) / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(SUM(Dif) % 60 As VARCHAR(2)),2) As CargaHoraria
FROM LancamentosHorarios GROUP BY Matricula

A consulta (bem mais simples) retorna que o funcionário M0001 possui 36:53 horas trabalhadas e o funcionário M0002 possui 36:48 horas trabalhadas. Com isso são apresentadas duas soluções baseadas em uma única instrução SELECT mas que outras alternativas existem ? Pode-se pensar em um cursor, mas gostaria de abordar algo mais moderno.

O uso do CLR

Já demonstrei o uso do CLR em alguns outros artigos como a utilização de matrizes e importação de arquivos XML. Nessas ocasiões observou-se que o CLR pode ser usado para criar tipos de dados ou stored procedures, mas além disso podemos utilizá-los para criar funções de agregação. Não entrarei nos detalhes do CLR, mas os interessados podem pesquisar em Como e quando programar em CLR no Microsoft SQL Server 2005.

O trecho de código abaixo representa um Assembly para criar uma função de agregação.

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

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.Native, IsInvariantToNulls = true,
    IsInvariantToDuplicates = false,
    IsInvariantToOrder = false)]

public struct SomaTempo
{
    private SqlDateTime DataAnterior;
    private SqlDateTime DataAtual;
    private int Minutos;
    private bool PrimeiroRegistro;
    private int Pos;

    public void Init()
    {
        // Inicializa as variáveis
        this.Minutos = 0;
        this.Pos = 1;
        this.PrimeiroRegistro = true;
    }

    public void Accumulate(SqlDateTime value)
    {
        DataAtual = value;

        // Acumula o valor se não for nulo
        if (value.IsNull)
        {
            return;
        }

        if (PrimeiroRegistro)
        {
            DataAnterior = DataAtual;
        }

        PrimeiroRegistro = false;

        // Calcula a diferença apenas se o registro for uma saída e o anterior uma entrada
        if (Pos % 2 == 0)
        {
            TimeSpan ts = DataAtual.Value.Subtract(DataAnterior.Value);
            this.Minutos += (ts.Hours * 60) + (ts.Minutes);
        }

        Pos += 1;
        DataAnterior = DataAtual;
    }

    public void Merge(SomaTempo Group)
    {
        Minutos += Group.Minutos;
    }

    public SqlInt32 Terminate()
    {
        // Retorna o valor acumulado
        return this.Minutos;
    }
}

Essa function receberá uma entrada do tipo SmallDatetime. O primeiro registro a ser informado será um registro de entrada, o segundo de saída, o terceiro de entrada, etc. Sempre que o registro for uma saída, é computada a diferença entre minutos em relação ao registro anterior. 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 CalculaSomaData.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\CalculaSomaData.cs (supondo que o arquivo esteja em G:\CLR\)
  • Localizar o arquivo CalculaSomaData.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 da agregação.

— Cria o Assembly
CREATE ASSEMBLY CalculaSomaData FROM ‘G:\CLR\CalculaSomaData.dll’

— Cria a agregação
CREATE AGGREGATE CalculaMinutos(@Data SmallDateTime)
RETURNS INT
EXTERNAL NAME
[CalculaSomaData].[SomaTempo];

Uma vez que a agregação tenha sido criada, basta utilizá-la:

— Utiliza a agregação (cálculo por dia)
SELECT Matricula, Data,
    RIGHT(‘0’ + CAST(dbo.CalculaMinutos(Horario) / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(dbo.CalculaMinutos(Horario) % 60 As VARCHAR(2)),2) As CargaHoraria
FROM tblRegHorarios GROUP BY Matricula, Data

— Utiliza a agregação (cálculo total)
SELECT Matricula,
    RIGHT(‘0’ + CAST(dbo.CalculaMinutos(Horario) / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(dbo.CalculaMinutos(Horario) % 60 As VARCHAR(2)),2) As CargaHoraria
FROM tblRegHorarios GROUP BY Matricula

A agregação em CLR poderia ser customizada para retornar já no formato hh:mm, mas isso talvez a tornasse um pouco inflexível, embora seja possível (basta alterar o método Terminate).

Mas e se fosse no SQL Server 2008 com o tipo de dados TIME ?

O SQL Server 2008 introduziu o tipo de dados TIME que armazena um horário diretamente, mas será que ele realmente pode realmente auxiliar ? O script abaixo adapta os dados ao SQL Server 2008.

— Cria uma tabela com o tipo TIME
CREATE TABLE tblRegHorarios2008 (
    Matricula CHAR(5), Data SMALLDATETIME,
    Horario TIME(0), Tipo CHAR(1))

— Insere os registros da tabela anterior
INSERT INTO tblRegHorarios2008 (Matricula, Data, Horario, Tipo)
SELECT Matricula, Data, CAST(Horario As TIME), Tipo FROM tblRegHorarios

A consulta abaixo possui uma semântica para calcular a carga horária de cada funcionário:

SELECT Matricula, Data,
    SUM(CASE Tipo WHEN ‘S’ THEN Horario
    ELSE -Horario END) As Total
FROM tblRegHorarios2008
GROUP BY Matricula, Data

A lógica seria que se o registro for de entrada, a hora seria negativada e se o registro fosse de saída a hora seria exibida normalmente. Assim pode-se posteriormente efetuar a somatória dos horários. Embora a lógica faça sentido, sua execução retorna uma mensagem de erro.

Msg 8117, Level 16, State 1, Line 3
Operand data type time is invalid for minus operator.

Pode-se contornar essa mensagem, refazendo-se a consulta.

SELECT Matricula, Data,
    SUM(CASE Tipo WHEN ‘S’ THEN Horario ELSE ’00:00′ END) –
    SUM(CASE Tipo WHEN ‘E’ THEN Horario ELSE ’00:00′ END)
As Total FROM tblRegHorarios2008
GROUP BY Matricula, Data

A lógica seria se o registro for de saída, a hora é exibida e sumarizada e posteriormente substraí-se o somatório das entradas. Embora também faça sentido, se executada também incorre em um erro.

Msg 8117, Level 16, State 1, Line 2
Operand data type time is invalid for sum operator.

Isso mostra que o tipo de dados TIME pode ajudar na melhor escolha dos tipos de dados, mas infelizmente a impossibilidade de sumarizá-los não representa grandes mudanças em relação às versões anteriores. O mecanismo da diferença é uma boa solução. Ex:

SELECT Matricula, Data, Horario, Tipo,
CASE Tipo WHEN ‘S’ THEN DateDiff(MI,’00:00′,Horario)
ELSEDateDiff(MI,’00:00′,Horario) END As Dif
FROM tblRegHorarios2008
WHERE Matricula = ‘M0001’ AND Data = ‘20090330’

O resultado tabular é expresso abaixo:

Matrícula

Data

Horário

Tipo

Dif

M0001

2009-03-30 00:00:00

2009-03-30 08:00:00

E

-480

M0001

2009-03-30 00:00:00

2009-03-30 12:00:00

S

720

M0001

2009-03-30 00:00:00

2009-03-30 14:00:00

E

-840

M0001

2009-03-30 00:00:00

2009-03-30 18:00:00

S

1080

A consulta é semelhante a presente no início do arquivo e utiliza a mesma lógica porém a referência agora é para o tipo de dados TIME. Uma vez que os minutos possam ser calculados, a montagem da carga horária também é possível.

;WITH LancamentosHorarios As (
    SELECT Matricula, Data, Horario, Tipo,
    CASE Tipo WHEN ‘S’ THEN DateDiff(MI,’00:00′,Horario)
    ELSEDateDiff(MI,’00:00′,Horario) END As Dif
    FROM tblRegHorarios2008)

SELECT Matricula,
    RIGHT(‘0’ + CAST(SUM(Dif) / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(SUM(Dif) % 60 As VARCHAR(2)),2) As CargaHoraria
FROM LancamentosHorarios GROUP BY Matricula

Os resultados mostram que a carga horária do funcionário M0001 é de 36:53 e do funcionário M0002 é de 36:48.

Não foi meu objetivo explorar a questão de cálculos horários no SQL Server. Há mais algumas situações que não foram abordadas até então e que também são comuns. Mostrarei uma dessas situações no próximo artigo. Até lá…

[ ]s,

Gustavo

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

Boa Tarde Pessoal,

Depois de uma semana atípica e bem conturbada é hora de atualizar o blog. Recentemente vi uma dúvida de SQL Server que foi a gota d’água. A dúvida referia-se a um cálculo de horas em uma tabela específica. Já perdi a conta de quantas dúvidas referente a cálculos horários eu já vi em fóruns, comunidades e sala de aula. Como "o copo encheu" chegou a hora de deixar a pregüiça de lado e fazer um post dedicado a isso. Antes de propriamente discorrer sobre o problema é necessário elaborar uma hipotética situação para ficar mais fácil de exemplificar.

A empresa XPD Ltda deseja desenvolver um sistema de ponto eletrônico de forma a controlar os horários de entrada e saída de seus funcionários. A captura de dados será biométrica e logo após os funcionários passarem seu polegar direito, o sistema deverá registrar a matrícula do funcionário e o horário exato da entrada (ou saída) do funcionário. Nenhum funcionário está autorizado a "virar a noite" no trabalho e portanto todos os registros irão ocorrer no mesmo dia. O primeiro registro naturalmente será uma "entrada". Os registros subseqüentes serão intercalados de forma que uma saída aparecerá após uma entrada e caso haja uma nova entrada (típico do retorno após o almoço), a nova entrada será registrada logo após a saída posterior. Se houver uma entrada sem uma saída respectiva, considerar-se-á que o horário de saída foi igual ao da última entrada.

Há duas possibilidades de modelagem aqui. Normalmente o número de entradas e saídas de um funcionário tende a ser baixo (ninguém irá entrar e sair na empresa muitas vezes ao dia). É possível modelar de forma um pouco mais inflexível a relação de entradas e saídas deixando-as fixas (ex: até quatro entradas e saídas no dia) ou utilizar uma abordagem um pouco mais flexível deixando esse número variável. Não há nenhum problema com nenhuma das abordagens mas é claro que elas possuem vantagens e desvantagens associadas (inclusive em relação ao horário). Utilizarei ambas.

Colunas de Entrada e Saída Fixas

Nessa abordagem irei adotar a possibilidade de até quatro entradas e quatro saídas por dia por funcionário. O script a seguir representa bem essa situação. Não coloquei as triggers para efetivar as devidas restrições necessárias (até por que além de poder fazer também na aplicação, o foco é explicar o cálculo):

CREATE TABLE tblRegistrosHorarios (
    Matricula CHAR(5),
    Data SMALLDATETIME,
    Entrada1 SMALLDATETIME, Saida1 SMALLDATETIME,
    Entrada2 SMALLDATETIME, Saida2 SMALLDATETIME,
    Entrada3 SMALLDATETIME, Saida3 SMALLDATETIME,
    Entrada4 SMALLDATETIME, Saida4 SMALLDATETIME)

INSERT INTO tblRegistrosHorarios VALUES (‘M0001’,‘20090330’,
‘20090330 08:00’,‘20090330 12:00’,‘20090330 14:00’,‘20090330 18:00’,
NULL,NULL,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0001’,‘20090331’,
‘20090331 08:00’,‘20090331 12:00’,‘20090331 14:00’,‘20090331 16:00’,
‘20090331 16:45’,‘20090331 19:00’,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0001’,‘20090401’,
‘20090401 08:00’,‘20090401 10:13’,‘20090401 11:45’,‘20090401 12:45’,
NULL,NULL,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0001’,‘20090402’,
‘20090402 09:07’,‘20090402 12:31’,‘20090402 14:11’,‘20090402 18:19’,
NULL,NULL,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0001’,‘20090403’,
‘20090403 07:43’,‘20090403 12:37’,‘20090403 13:28’,‘20090403 15:03’,
‘20090403 17:14’,‘20090403 20:38’,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0002’,‘20090330’,
‘20090330 08:30’,‘20090330 12:17’,‘20090330 13:32’,‘20090330 16:21’,
‘20090330 17:53’,‘20090330 18:30’,‘20090330 19:30’,‘20090330 20:30’)

INSERT INTO tblRegistrosHorarios VALUES (‘M0002’,‘20090331’,
‘20090331 07:51’,‘20090331 12:03’,‘20090331 13:58’,‘20090331 18:00’,
NULL,NULL,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0002’,‘20090401’,
‘20090401 08:00’,‘20090401 10:13’,‘20090401 11:45’,‘20090401 12:45’,
NULL,NULL,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0002’,‘20090402’,
‘20090402 08:37’,‘20090402 11:31’,‘20090402 13:24’,‘20090402 19:19’,
NULL,NULL,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0002’,‘20090403’,
‘20090403 09:32’,‘20090403 12:49’,‘20090403 13:51’,‘20090403 17:00’,
‘20090403 17:45’,‘20090403 19:38’,NULL,NULL)

O primeiro passo seria calcular as diferenças horárias para posteriormente fazer uma soma. Ex:

;WITH MinutosDia As (
SELECT Matricula, Data,
    Entrada1, Saida1, Entrada2, Saida2,
    Entrada3, Saida3, Entrada4, Saida4,
    ISNULL(DATEDIFF(Mi,Entrada1,Saida1),0) As Dif1,
    ISNULL(DATEDIFF(Mi,Entrada2,Saida2),0) As Dif2,
    ISNULL(DATEDIFF(Mi,Entrada3,Saida3),0) As Dif3,
    ISNULL(DATEDIFF(Mi,Entrada4,Saida4),0) As Dif4
FROM tblRegistrosHorarios)

SELECT * FROM MinutosDia

Essa instrução SQL retorna os dados normais da tabela além das diferenças entre as entradas e saídas horárias. Como existem até 4 entradas e até quatro saídas, podem existir até quatro diferenças. Posteriormente basta somar as diferenças.

;WITH MinutosDia As (
SELECT Matricula, Data,
    Entrada1, Saida1, Entrada2, Saida2,
    Entrada3, Saida3, Entrada4, Saida4,
    ISNULL(DATEDIFF(Mi,Entrada1,Saida1),0) As Dif1,
    ISNULL(DATEDIFF(Mi,Entrada2,Saida2),0) As Dif2,
    ISNULL(DATEDIFF(Mi,Entrada3,Saida3),0) As Dif3,
    ISNULL(DATEDIFF(Mi,Entrada4,Saida4),0) As Dif4
FROM tblRegistrosHorarios)

SELECT Matricula, Data, Dif1 + Dif2 + Dif3 + Dif4 As TotalMinutos
FROM MinutosDia

Com mais um pequeno ajuste o GROUP BY pode ser perfeitamente aplicado.

;WITH MinutosDia As (
SELECT Matricula, Data,
    Entrada1, Saida1, Entrada2, Saida2,
    Entrada3, Saida3, Entrada4, Saida4,
    ISNULL(DATEDIFF(Mi,Entrada1,Saida1),0) As Dif1,
    ISNULL(DATEDIFF(Mi,Entrada2,Saida2),0) As Dif2,
    ISNULL(DATEDIFF(Mi,Entrada3,Saida3),0) As Dif3,
    ISNULL(DATEDIFF(Mi,Entrada4,Saida4),0) As Dif4
FROM tblRegistrosHorarios)

SELECT Matricula, Data, SUM(Dif1 + Dif2 + Dif3 + Dif4) As TotalMinutos
FROM MinutosDia
GROUP BY Matricula, Data
 

Chegamos a conclusão de que no período previsto, o empregado M0001 possui 2213 minutos e o empregado M0002 possui 2208 minutos. Normalmente não é desejável exibir o total de minutos, mas sim a quantidade horária em um formato hh:mm por exemplo. Sabendo que uma hora equivale a 60 minutos, podemos dividir o total em minutos e calcular o total de horas e a diferença será o total de minutos que sobraram. Ex:

;WITH MinutosDia As (
SELECT Matricula,
    Entrada1, Saida1, Entrada2, Saida2,
    Entrada3, Saida3, Entrada4, Saida4,
    ISNULL(DATEDIFF(Mi,Entrada1,Saida1),0) As Dif1,
    ISNULL(DATEDIFF(Mi,Entrada2,Saida2),0) As Dif2,
    ISNULL(DATEDIFF(Mi,Entrada3,Saida3),0) As Dif3,
    ISNULL(DATEDIFF(Mi,Entrada4,Saida4),0) As Dif4
FROM tblRegistrosHorarios),

Resumo As (
SELECT Matricula, SUM(Dif1 + Dif2 + Dif3 + Dif4) As TotalMinutos
FROM MinutosDia
GROUP BY Matricula)

SELECT Matricula,
    CAST((TotalMinutos / 60) As VARCHAR(2)) + ‘:’ +
    CAST((TotalMinutos % 60) As VARCHAR(2)) As CargaHoraria
FROM Resumo

Dessa forma é possível calcular que M0001 realizou o total de 36:53 e M0002 realizou o total de 36:48. Podemos também visualizar os lançamentos de forma diária acrescentando a coluna Data no GROUP BY.

;WITH MinutosDia As (
SELECT Matricula, Data,
    Entrada1, Saida1, Entrada2, Saida2,
    Entrada3, Saida3, Entrada4, Saida4,
    ISNULL(DATEDIFF(Mi,Entrada1,Saida1),0) As Dif1,
    ISNULL(DATEDIFF(Mi,Entrada2,Saida2),0) As Dif2,
    ISNULL(DATEDIFF(Mi,Entrada3,Saida3),0) As Dif3,
    ISNULL(DATEDIFF(Mi,Entrada4,Saida4),0) As Dif4
FROM tblRegistrosHorarios),

Resumo As (
SELECT Matricula, Data, SUM(Dif1 + Dif2 + Dif3 + Dif4) As TotalMinutos
FROM MinutosDia
GROUP BY Matricula, Data)

SELECT Matricula, Data,
    CAST((TotalMinutos / 60) As VARCHAR(2)) + ‘:’ +
    CAST((TotalMinutos % 60) As VARCHAR(2)) As CargaHoraria
FROM Resumo

Para evitar possíveis problemas de formatação, basta adaptar a última consulta

SELECT Matricula, Data,
    RIGHT(‘0’ + CAST((TotalMinutos / 60) As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST((TotalMinutos % 60) As VARCHAR(2)),2) As CargaHoraria
FROM Resumo

Lançamentos de horas variáveis

Na abordagem anterior (típica de um formulário de folha de ponto) existem alguns inconvenientes como a limitação de entradas e saídas e principalmente a quantidade de registros nulos. Utilizarei os mesmos exemplos porém com uma modelagem conceitualmente mais adequada.

CREATE TABLE tblRegHorarios (
    Matricula CHAR(5),
    Data SMALLDATETIME,
    Horário SMALLDATETIME,
    Tipo CHAR(1))

Ao invés de fazer as várias instruções de INSERT INTO, pode-se converter as colunas da tabela anterior em linhas e realizar uma carga através do operador UNPIVOT e do script abaixo:

;WITH LancamentosHorarios As (
SELECT *
FROM
   (SELECT Matricula, Data, Entrada1, Saida1, Entrada2,
    Saida2, Entrada3, Saida3, Entrada4, Saida4
   FROM tblRegistrosHorarios) PV
UNPIVOT
   (Horario FOR Lancamento IN
      (Entrada1, Saida1, Entrada2, Saida2,
        Entrada3, Saida3, Entrada4, Saida4)
) As UP)

INSERT INTO tblRegHorarios
SELECT
    Matricula, Data, Horario,
    LEFT(Lancamento,1)
FROM LancamentosHorarios

SELECT Matricula, Data, Horario, Tipo FROM tblRegHorarios

Agora que a tabela tblRegHorarios está devidamente populada, os cálculos são um pouco mais trabalhosos. Na situação anterior, basta somar consultas. Na situação atual é preciso somar as linhas agrupadas por matricula e data ou somente por matrícula. A primeira vista, pode parecer que um simples SUM com o GROUP BY possa ser suficiente, mas dessa vez não é assim tão simples. Será necessário calcular a diferença entra a 2ª e a 1ª ou ainda entre a 4ª e a 3ª, a 6ª e a 5ª e assim sucessivamente. Existem algumas formas de se fazer isso, mas vamos a uma mais simples.

Sabendo que as entradas antecedem as saídas podemos deduzir que as entradas ocuparam sempre uma posição ímpar e as saídas uma posição par. Um outro detalhe é que a saída está sempre referente a sua entrada anterior. Se por exemplo uma saída representa a 4º registro então podemos afirmar que o 3º será sua entrada correspondente. Para numerar os registros, a função ROW_NUMBER() é bem adequada. Como a numeração deve ser por funcionário e por dia, é necessário usar o Partition By. Caso essas funções e operadores não sejam muito familiares, recomendo o artigo ?

SELECT
    Matricula, Data, Horario, Tipo,
    ROW_NUMBER() OVER (
        PARTITION BY Data, Matricula
        ORDER BY Data, Matricula, Horario) As Pos
FROM tblRegHorarios

Agora que existe uma coluna numerada, basta combinar as saídas pares de suas entradas ímpares correspondentes.

;WITH LancamentosHorarios As (
SELECT
    Matricula, Data, Horario, Tipo,
    ROW_NUMBER() OVER (
        PARTITION BY Data, Matricula
        ORDER BY Data, Matricula, Horario) As Pos
FROM tblRegHorarios)

SELECT
    L1.Matricula, L1.Data,
    L1.Horario As Entrada, L2.Horario As Saida
FROM
    LancamentosHorarios As L1
    INNER JOIN LancamentosHorarios As L2 ON
        L1.Matricula = L2.Matricula AND L1.Data = L2.Data AND
        L1.Pos = L2.Pos – 1 AND L1.Pos % 2 = 1

Essa consulta reproduzirá todos os lançamentos de entrada e saída por funcionário e por data. O JOIN entre matricula e data faz sentindo, mas alguns certamente devem estar se perguntando a razão de L1.Pos = L2.Pos – 1 e principalmente L1.Pos % 2 =1. O primeiro predicado é para combinar os registros com seu subseqüente (ex: 1º registro com o 2º registro, o 3º registro com o 4º registro, etc). O segundo predicado é para garantir que os registros em L1 serão ímpares (o resto de uma divisão de um número ímpar será sempre igual a um). Sem essa condição, seria admitido combinar o 2º registro com o 3º registro e a combinação não ajuda com o resultado.

Com o resultado de todas as entradas e saídas é possível utilizar a função DateDiff normalmente e calcular as diferenças.

;WITH LancamentosHorarios As (
SELECT
    Matricula, Data, Horario, Tipo,
    ROW_NUMBER() OVER (
        PARTITION BY Data, Matricula
        ORDER BY Data, Matricula, Horario) As Pos
FROM tblRegHorarios),

LancamentosOrganizados As (

SELECT
    L1.Matricula, L1.Data,
    L1.Horario As Entrada, L2.Horario As Saida
FROM
    LancamentosHorarios As L1
    INNER JOIN LancamentosHorarios As L2 ON
        L1.Matricula = L2.Matricula AND L1.Data = L2.Data AND
        L1.Pos = L2.Pos – 1 AND L1.Pos % 2 = 1)

SELECT Matricula, Data, DateDiff(Mi,Entrada,Saida) As Dif
FROM LancamentosOrganizados

Essa consulta retorna exatamente a diferença entre cada registro de entrada e saída correspondente. Para obter um relatório mais sumarizado, basta apenas substituir a última consulta (a executada contra a CTE LancamentosOrganizados) pela consulta abaixo

SELECT Matricula, SUM(DateDiff(Mi,Entrada,Saida)) As CargaHoraria
FROM LancamentosOrganizados GROUP BY matricula

Obtem-se exatamente o mesmo resultado, ou seja, o empregado M0001 possui 2213 minutos e o empregado M0002 possui 2208 minutos. Pelas mesmas razões da abordagem anterior, é interessante formatar o resultado. O exemplo abaixo mostra o resultado no formato hh:mm e o cálculo realizado por matrícula e por data.

;WITH LancamentosHorarios As (
SELECT
    Matricula, Data, Horario, Tipo,
    ROW_NUMBER() OVER (
        PARTITION BY Data, Matricula
        ORDER BY Data, Matricula, Horario) As Pos
FROM tblRegHorarios),

LancamentosOrganizados As (

SELECT
    L1.Matricula, L1.Data,
    L1.Horario As Entrada, L2.Horario As Saida
FROM
    LancamentosHorarios As L1
    INNER JOIN LancamentosHorarios As L2 ON
        L1.Matricula = L2.Matricula AND L1.Data = L2.Data AND
        L1.Pos = L2.Pos – 1 AND L1.Pos % 2 = 1)

SELECT Matricula, Data,
    RIGHT(‘0’ + CAST(SUM(DateDiff(Mi,Entrada,Saida)) / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(SUM(DateDiff(Mi,Entrada,Saida)) % 60 As VARCHAR(2)),2)
As CargaHoraria
FROM LancamentosOrganizados GROUP BY Matricula, Data

Essa consulta retorna os dados horários devidamente formatados (o GROUP BY pode ser modificado se o resultado for apenas por funcionário). O raciocínio até então foi apenas para "exercitar", no próximo artigo mostrarei outras formas de resolver esse problema além de outras considerações quando os registros estão dispostos em linhas e não em colunas. Para aqueles que querem mostrar o resultado em um formato de colunas, segue o comando de PIVOT.

;WITH LancamentosHorarios As (
SELECT
    Matricula, Data, Horario, Tipo,
    (ROW_NUMBER() OVER (
        PARTITION BY Data, Matricula
        ORDER BY Data, Matricula, Horario) + 1) / 2 As Pos
FROM tblRegHorarios),

Lancamentos As (

SELECT DISTINCT Matricula, Data FROM LancamentosHorarios)

SELECT Matricula, Data,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘E’ AND Pos = 1) As Entrada1,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘S’ AND Pos = 1) As Saida1,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘E’ AND Pos = 2) As Entrada2,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘S’ AND Pos = 2) As Saida2,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘E’ AND Pos = 3) As Entrada3,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘S’ AND Pos = 3) As Saida3,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘E’ AND Pos = 4) As Entrada4,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘S’ AND Pos = 4) As Saida4

FROM Lancamentos As L

Eu poderia ter pensado em utilizar o operador PIVOT para transformar linhas em colunas desfazendo a operação de UNPIVOT. Infelizmente o PIVOT só transforma linhas em colunas quando há operações de agregação. A transformação direta de linhas em colunas sem nenhuma função de agregação não é possível com o operador PIVOT. O Books OnLine é bem claro quanto a isso.

Até o próximo artigo…

[ ]s,

Gustavo

OUTER JOIN com mais de duas tabelas ? Será que está mesmo “certo” ?

Boa Noite Pessoal,

A última vez que falei sobre OUTER JOIN foi um relato de um serviço de consultoria prestado para um órgão público. De fato, problemas ocorreram quando se pensa que LEFT OUTER JOIN, RIGHT OUTER JOIN, *= e =* possam significar a mesma coisa. Durante o serviço de consultoria, essa não foi a única descoberta que o órgão fez em relação aos OUTER JOINs. Normalmente o raciocínio do OUTER JOIN é uma abstração do INNER JOIN na qual a lógica é “traga todos os registros independente de ter ou não correspondentes do outro lado”. Essa visão não deixa de estar correta, mas o problema é que ela é um pouco míope e algumas particularidades enganam até mesmo os mais experientes. Será que quando há mais de duas tabelas o OUTER JOIN é realmente tão trivial assim ? Vejamos com exemplos práticos.

CREATE TABLE tblClientes (
    ClienteID INT NOT NULL IDENTITY(1,1),
    Nome VARCHAR(80) NOT NULL,
    CONSTRAINT PK_Cliente PRIMARY KEY (ClienteID))

CREATE TABLE tblCarros (
    CarroID INT NOT NULL IDENTITY(1,1),
    ClienteID INT NOT NULL,
    Placa CHAR(7) NOT NULL,
    Ano SMALLINT NOT NULL,
    Descricao VARCHAR(20) NOT NULL,
    CONSTRAINT PK_Carro PRIMARY KEY (CarroID),
    CONSTRAINT FK_Cliente_Carro FOREIGN KEY (ClienteID)
        REFERENCES tblClientes (ClienteID))

CREATE TABLE tblObservacoes (
    ObservacaoID INT NOT NULL IDENTITY(1,1),
    CarroID INT NOT NULL,
    Observacao VARCHAR(8000) NOT NULL,
    CONSTRAINT PK_Observacao PRIMARY KEY (ObservacaoID),
    CONSTRAINT FK_Carro_Observacao FOREIGN KEY (CarroID)
        REFERENCES tblCarros (CarroID))

— Popula a tabela de Clientes
INSERT INTO tblClientes (Nome) VALUES (‘Ivone’)
INSERT INTO tblClientes (Nome) VALUES (‘Amanda’)
INSERT INTO tblClientes (Nome) VALUES (‘Mariana’)
INSERT INTO tblClientes (Nome) VALUES (‘Regiane’)
INSERT INTO tblClientes (Nome) VALUES (‘Eliane’)

— Popula a tabela de Carros
INSERT INTO tblCarros (ClienteID,Placa,Ano,Descricao)
VALUES (1,‘JGK7044’,2006,‘Peugeot 206’)

INSERT INTO tblCarros (ClienteID,Placa,Ano,Descricao)
VALUES (1,‘GDY7765’,2005,‘Fiat Palio’)

INSERT INTO tblCarros (ClienteID,Placa,Ano,Descricao)
VALUES (2,‘KHZ0345’,2007,‘Vectra GT’)

INSERT INTO tblCarros (ClienteID,Placa,Ano,Descricao)
VALUES (2,‘BDC3211’,2008,‘Fiat Stilo’)

INSERT INTO tblCarros (ClienteID,Placa,Ano,Descricao)
VALUES (3,‘JGA7438’,2008,‘Corsa Hatch’)

— Popula a tabela de Observacoes
INSERT INTO tblObservacoes (CarroID, Observacao) VALUES (1, ‘Colocar Alarme’)
INSERT INTO tblObservacoes (CarroID, Observacao) VALUES (2, ‘Revisar motor’)
INSERT INTO tblObservacoes (CarroID, Observacao) VALUES (3, ‘Conceder tapetes de brinde’)

Além dos scripts, vale a pena fazer um breve comentário sobre o modelo de dados:

  • Todo cliente pode ou não possuir um carro
  • Todo carro pode ou não possuir uma observação (algum detalhe a ser lembrado sobre o carro)

Uma pergunta básica de SQL poderia ser formulada: “Qual seria a instrução SQL que retorne todos os clientes quer tenham carro ou não e se houver carros mostrar somente os carros que possuem alguma observação ?”. Apenas visualizando os comandos de INSERT, é visível que existem 5 clientes e apenas três carros com observação.

Nome

Placa

Ano

Descricao

Observacao

Ivone

JGK7044

2006

Peugeot 206

Colocar Alarme

Ivone

GDY7765

2005

Fiat Palio

Revisar motor

Amanda

KHZ0345

2007

Vectra GT

Conceder tapetes de brinde

Mariana

NULL

NULL

NULL

NULL

Regiane

NULL

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

NULL

O comando abaixo é a tentativa mais comum.

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR ON CLI.ClienteID = CAR.ClienteID
    LEFT OUTER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID

Vejamos então o resultado dessa consulta:

Nome

Placa

Ano

Descricao

Observacao

Ivone

JGK7044

2006

Peugeot 206

Colocar Alarme

Ivone

GDY7765

2005

Fiat Palio

Revisar motor

Amanda

KHZ0345

2007

Vectra GT

Conceder tapetes de brinde

Amanda

BDC3211

2008

Fiat Stilo

NULL

Mariana

JGA7438

2008

Corsa Hatch

NULL

Regiane

NULL

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

NULL

De fato os 5 clientes estão presentes, mas há dois carros sem observações que não deveriam ser retornados. Afinal a idéia é levantar todos os clientes e os carros com observações se houver. Ao contrário do que possa parecer o LEFT OUTER JOIN não retornou o resultado esperado. Bem, se o LEFT OUTER JOIN retorna todos os registros à esquerda independente de possuir correspondentes à direita então a construção está correta do ponto de vista de funcionamento, pois, todos os carros foram retornados independente de terem ou não correspondentes na tabela de observações. O único problema é que o resultado não foi o satisfatório para a pergunta anterior.

Os clientes devem ser retornados independente de possuirem ou não carros e o LEFT OUTER JOIN é o operador correto para isso. Os carros devem ser retornados mas somente se possuírem observações. Essa é uma aplicação correta para o INNER JOIN. Nesse caso então, bastaria substituir o segundo LEFT por um INNER. Ex:

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR ON CLI.ClienteID = CAR.ClienteID
    INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID

Vejamos então o resultado dessa consulta:

Nome

Placa

Ano

Descricao

Observacao

Ivone

JGK7044

2006

Peugeot 206

Colocar Alarme

Ivone

GDY7765

2005

Fiat Palio

Revisar motor

Amanda

KHZ0345

2007

Vectra GT

Conceder tapetes de brinde

Dessa vez nenhum carro sem observação foi retornado mas em contrapartida nenhum cliente sem carro apareceu na pesquisa e portanto o resultado não foi esperado. A pergunta nesse caso é: “Por que os clientes sem carro não apareceram já que foi usado o LEFT OUTER JOIN ? Não era para retornar todos os clientes quer tenham ou não carro ?”. Esse é o problema de um raciocínio voltado para apenas uma tabela. Embora ele seja verdadeiro é aplicável para apenas uma tabela e quando há mais tabelas envolvidas as coisas podem ser um pouco diferentes. A consulta abaixo irá ajudar a entender o que aconteceu.

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    INNER JOIN tblCarros AS CAR ON CLI.ClienteID = CAR.ClienteID
    INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID

O resultado dessa consulta é idêntico ao anterior:

Nome

Placa

Ano

Descricao

Observacao

Ivone

JGK7044

2006

Peugeot 206

Colocar Alarme

Ivone

GDY7765

2005

Fiat Palio

Revisar motor

Amanda

KHZ0345

2007

Vectra GT

Conceder tapetes de brinde

Se o INNER JOIN foi utilizado, então somente os clientes que tenham carros com observações são retornados (afinal o INNER só retorna se houver a correspondência em ambas as tabelas). Uma vez que as assertivas (NULL = NULL) e (Qualquer Valor = NULL) são tidas como falsas, ao invés de interpretarmos o INNER JOIN como “obriga a ter correspondência em ambas as tabelas” podemos abstrair para “obriga a existência de registros em ambas as tabelas”. Se a obrigatoriedade de registros em ambas as tabelas é forçada pelo INNER JOIN vejamos o que acontece com a consulta LEFT e INNER.

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR ON CLI.ClienteID = CAR.ClienteID
    INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID

O LEFT OUTER JOIN entre Clientes e Carros irá recuperar todos os clientes que tenham carros ou não. Isso produzirá uma tabela intermediária com os seguintes resultados:

Nome

Placa

Ano

Descricao

Ivone

JGK7044

2006

Peugeot 206

Ivone

GDY7765

2005

Fiat Palio

Amanda

KHZ0345

2007

Vectra GT

Amanda

BDC3211

2008

Fiat Stilo

Mariana

JGA7438

2008

Corsa Hatch

Regiane

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

Essa tabela intermediária (visível para o SQL Server) contem a relação de todos os clientes independente de ter carros e seus carros quando existem. O próximo passo é fazer a junção dessa tabela intermediária com a tabela de observações. Se for utilizado um operador LEFT entre carros e observações, o resultado dessa tabela intermediária será recuperado (independente de haver ocorrências ou não em observações) e será idêntico à primeira consulta, ou seja, retorna carros sem observação. Mas e se for utilizado um operador INNER JOIN ?

Nome

Placa

Ano

Descricao

Observacao

Ivone

JGK7044

2006

Peugeot 206

Colocar Alarme

Ivone

GDY7765

2005

Fiat Palio

Revisar motor

Amanda

KHZ0345

2007

Vectra GT

Conceder tapetes de brinde

Amanda

BDC3211

2008

Fiat Stilo

NULL

Mariana

JGA7438

2008

Corsa Hatch

NULL

Regiane

NULL

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

NULL

No caso todos os registros da tabela intermediária serão retornados, mas como o INNER JOIN obriga a existência de correspondentes em ambos os lados, todos os registros da tabela intermediária que não possuam observação serão descartados (marcados em cinza). Esse descarte irá ocorrer independente se trata-se de um cliente sem carro ou de um carro sem observação. Percebe-se então que no final das contas não importa a quantidade de operadores OUTER, se houver um operador INNER durante as junções na tabela mais abaixo, implicitamente todos os operadores OUTER são convertidos para INNER. Gastou-se recursos de processamento (os operadores OUTER normalmente são menos performáticos) e ainda não se tem o resultado esperado.

Alguém pode estar se perguntando quais seriam as alternativas. Toda vez que me deparo com alguns problemas desse tipo vejo algumas soluções criativas. A maioria tente resolver os problemas na cláusula WHERE mas quase sempre isso não retorna o resultado esperado. Das que me deparei a que funcionou é exposta abaixo:

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    INNER JOIN tblCarros AS CAR ON CLI.ClienteID = CAR.ClienteID
    INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID
UNION ALL
SELECT
    Nome, NULL, NULL, NULL, NULL
FROM
    tblClientes AS CLI
WHERE
    NOT EXISTS (
        SELECT * FROM tblCarros AS CAR
        WHERE CLI.ClienteID = CAR.ClienteID AND
            EXISTS (
            SELECT * FROM tblObservacoes AS OBS
            WHERE CAR.CarroID = OBS.CarroID
        )
)

A consulta consegue retornar todos os clientes quer tenham ou não carros com observações e no caso de haver carros são retornados somente os com observações. O primeiro problema dessa alternativa é que ela é muito mais dispendiosa e confusa. O segundo é que a manutenção também é prejudicada, pois não é o tipo de consulta fácil de efetuar uma manutenção no caso de alterações decorrentes de regras de negócio.

A solução para esse problema não é complexa. Durante a explicação do uso da tabela intermediária percebe-se que o LEFT foi avaliado primeiro e que o INNER foi avaliado depois, ou seja, primeiro juntou-se Clientes e Carros para depois juntar o resultado com observações. Se essa ordem puder ser controlada será possível obter o resultado desejado.

A primeira forma de se alterar a ordem em que os JOINs são processados é mudando-se a hierarquia das tabelas. Normalmente as tabelas são especificadas obedecendo uma hierarquia de relacionamentos do lado um para o lado muitos. Se um cliente pode ter muitos carros e um carro pode ter muitas observações, é uma prática de código comum especificar as tabelas nessa hierarquia durante o uso do operador FROM, ou seja, primeiro clientes, depois carros e depois observações. Embora seja comum (e inclusive recomendado) não há um nenhum impecilho para mudar essa ordem.

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblCarros AS CAR
    INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID
    RIGHT OUTER JOIN tblClientes AS CLI ON CAR.ClienteID = CLI.ClienteID

Como a tabela de carros foi especificada primeiro é feito o INNER JOIN com a tabela de observações retornando todos os carros que tem observações. Esse resultado intermediário é combinado com um operador RIGHT OUTER JOIN e como a tabela de clientes está à direita são retornados todos os clientes (quer tenham ou não correspondentes com o resultado intermediário). Essa é uma abordagem performática, mas esteticamente é desaconselhável. Se aparecerem outras tabelas pode-se chegar em uma situação com tabelas fora da ordem hierárquica e múltiplos LEFT e RIGHT na mesma consulta favorecendo a presença de erros.

Outra forma de controlar a ordem em que esses JOINs são avaliados é a utilização de subqueries do tipo Derived Table. Essa normalmente é uma saída bem fácil e factível.

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN
        (SELECT ClienteID, Placa, Ano, Descricao, Observacao FROM tblCarros AS CAR
        INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID) AS Q
ON CLI.ClienteID = Q.ClienteID

A subquery Q contém a relação dos dados de carros que tenham observações já que houve uma junção do tipo INNER JOIN. Posteriormente a tabela de Clientes é combinada com a subquery Q em uma junção do tipo LEFT OUTER JOIN. Como a tabela de clientes está à esquerda, são retornados todos os clientes quer tenham ou não correspondentes na subquery Q. Essa abordagem é interessante e menos limitada que as anteriores, mas existe uma degradação mínima de desempenho por conta da projeção dos campos para a montagem da subquery Q (o que pode ser traduzido pelos operadores do tipo Computer Scalar nos planos de execução). Uma pequena variação da consulta pode eliminar esse passo.

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN
        (tblCarros AS CAR
        INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID)
            ON CLI.ClienteID = CAR.ClienteID

Essa construção (por sinal pouco conhecida) consegue retornar o resultado esperado lendo os dados diretamente sem fazer a projeção dos campos para a montagem de subqueries. Normalmente é a melhor alternativa por ser performática.

A introdução das Common Table Expressions (CTEs) no padrão ANSI99 também permite uma terceira solução para encontrar o resultado esperado conforme o script abaixo:

;WITH Q AS (SELECT ClienteID, Placa, Ano, Descricao, Observacao FROM tblCarros AS CAR
    INNER JOIN tblObservacoes AS OBS ON CAR.CarroID = OBS.CarroID)

SELECT
    Nome, Placa, Ano, Descricao, Observacao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN Q ON CLI.ClienteID = Q.ClienteID

Todas as três soluções são ANSI Compliance e funcionam em outros SGBDs, mas tanto a CTE quanto a subquery necessitam da projeção embora isso não resulta em perdas de desempenho perceptíveis.

Após demonstrar esses comportamentos, o órgão em questão questionou sobre o SQL Server e que esse comportamento era um BUG. Achar que isso é um BUG referente ao SQL Server é sim um grande equívoco. Primeiro porque a lógica está correta (ainda que alguns comportamentos sejam indesejáveis não estão errados) e segundo porque outros SGBDs tem o mesmo comportamento já que o ANSI especifica como os JOINs devem funcionar e não o SQL Server. Para tirar a prova real, fiz o teste com o PostgreSQL e o DB2 UDB e de fato as "surpresas" e soluções são as mesmas.

Na verdade o fato do duplo OUTER JOIN ser como é não representa propriamente um problema. O foco não é saber se uma construção está ou não “correta”, mas sim se ela retorna ou não o resultado esperado. Usar o LEFT OUTER JOIN com duas tabelas é algo que funciona em alguns casos mas em outros não. Usar qualquer uma das outras três construções também é algo que funciona em alguns casos e em outros não. O que realmente importa é conhecer como operadores OUTER JOIN funcionam e saber qual a construção correta para cada caso já que não há uma que funciona para todos os casos.

[ ]s,

Gustavo