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

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

Será que LEFT OUTER JOIN, RIGHT OUTER JOIN, *= e =* são sinônimos ?

Olá Pessoal,

Estive no aeroporto de Atlanta e fiquei esperando 4 horas para o meu vôo de volta para o Brasil. Enquanto esperava nada melhor do que planejar um artigo. No ano de 2008, participei de uma consultoria em um órgão público para a migração de um base de dados em SQL Server 2000 para o SQL Server 2005. A idéia era fazer apenas o projeto de migração enquanto o órgão ficaria responsável por implementá-lo (sobre a minha supervisão). Alguns podem até pensar que uma migração do SQL Server 2000 para o 2005 é tão simples quanto migrar os documentos do Excel 2003 para o Excel 2007, ou seja, talvez um simples backup ou um attach sejam suficientes. De fato isso é verdade em muitas situações, mas nem sempre é assim.

Ao chegar no órgão, me deparei com diversos pontos a serem resolvidos, ou seja, faltavam ainda as máquinas que hospedariam o 2005, a definição de que bases deveriam ir primeiro, quem faria as correções necessárias, etc. Em um universo de pouco mais de 100 SPs, o Upgrade Advisor capturou 19 SPs para correção. Na maioria das SPs o problema era o mesmo, ou seja, a presença dos operadores *= e =* que não são mais permitidos no SQL Server 2005. A solução parecia ser bastante simples, ou seja, bastaria substituir o *= e o =* respectivamente por LEFT OUTER JOIN e RIGHT OUTER JOIN. Sim, isso é o que pensam a maioria dos desenvolvedores, DBAs, consultores e muitos outros profissionais (e talvez seja inclusive o que você julgue ser verdadeiro). A verdade é que isso é correto na maioria das situações, mas não em todas e julgar que substituir o *= e o =* por LEFT OUTER JOIN e RIGHT OUTER JOIN sempre é o caminho correto pode levá-lo a interpretações equivocadas e erros em algumas consultas.

O objetivo desse artigo não é propriamente falar sobre a migração, mas sobre esse ponto especificamente. Não subestime o artigo apenas pelo título, pois, você pode se surpreender. Talvez algumas construções tidas como inquestionáveis devam ser revistas.

Um pouco sobre o ANSI e as extensões proprietárias

Em muitas discussões veja uma certa confusão sobre o uso dos JOINs. Alguns dizem que juntar os campos via JOIN é o padrão ANSI e juntar os campos via cláusula WHERE não é o padrão ANSI. Sim, há razões para que se pense assim, mas a verdade é que as duas construções obedecem ao padrão ANSI (só que como veremos os JOINS obedecem a uma especificação mais recente). Voltemos alguns anos atrás para entender as proposições do ANSI.

A linguagem SQL nem sempre foi como ela é, ou seja, universal e adaptável a quase todos os bancos de dados relacionais e objeto-relacionais da atualidade. Ela não era uma linguagem padrão e cada banco de dados utilizava uma linguagem proprietária para consulta. Em meados de 1986, o comitê ANSI reuniu-se e definiu como a SQL deveria comportar-se e a partir daí foi feita uma gradativa adoção por parte dos fabricantes de bancos de dados. Como todo padrão, a medida que o tempo passa, identificam-se novas necessidades que não foram pensadas antes e por conseqüência não contempladas no padrão. Por isso, de tempos em tempos, o padrão tem de ser revisto (isso ocorre com a SQL, com os padrões XML e com muitos outros na área de TI). Em 1989 foi feita uma nova revisão para incorporar novas necessidades e o padrão continuou a ganhar força (essa revisão ficou conhecida como SQL89). Havia uma particularidade nesse padrão. Ele não possuia JOINs. Como então juntar as informações de tabelas diferentes ? Essa é a razão pela qual as junções eram feitas via cláusula WHERE. Ex:

SELECT Nome, Placa, Ano, Descricao
FROM tblClientes, tblCarros
WHERE tblClientes.ClienteID = tblCarros.ClienteID

Isso explica porque há tantas junções na cláusula WHERE. Os analistas mais antigos usavam esse tipo de construção e foram repassando-a para os analistas mais experientes. Embora o SQL89 permitesse a junção via cláusula WHERE (desconfio que isso já era possível desde a padronização de 1986), alguns problemas não demoraram a aparecer. A consulta acima retorna todos os clientes e os dados de seus carros, mas como é feita uma junção na cláusula WHERE, obrigatoriamente são retornados apenas os clientes que tem carros. Como retornar então a mesma consulta, mas incluir os clientes que não possuem carros ? Essa era uma necessidade que o padrão SQL89 não conseguia resolver, pois, senão havia JOINs não poderíamos utilizar o LEFT OUTER JOIN.

Visualizando essas limitações, alguns fabricantes (a exemplo da ORACLE e da Microsoft) introduziram novas construções para estender o padrão SQL89. Como cada fabricante faz suas próprias extensões, é por essa razão que são conhecidas como extensões proprietárias. Se todos os fabricantes concordassem em suas extensões, elas não seria proprietárias e provavelmente fariam parte de alguma revisão posterior. Seguem algumas simulações SQL89 para simular o LEFT OUTER JOIN.

Microsoft

Oracle

SELECT Nome, Placa, Ano, Descricao
FROM tblClientes, tblCarros
WHERE tblClientes.ClienteID *= tblCarros.ClienteID

SELECT Nome, Placa, Ano, Descricao
FROM tblClientes, tblCarros
WHERE tblClientes.ClienteID = tblCarros.ClienteID(+)

SELECT Nome, Placa, Ano, Descricao
FROM tblClientes, tblCarros
WHERE tblCarros.ClienteID (+)= tblClientes.ClienteID

Com construções desse tipo, foi possível que a Microsoft e a Oracle pudessem estender o padrão SQL89 e atender situações do tipo LEFT OUTER JOIN ou RIGHT OUTER JOIN. É justamente por esse tipo de implementação, que é bem comum ver junções na cláusula WHERE. Afinal com as extensões proprietárias, era possível aos analistas utilizá-las e atender às necessidades comuns e assim repassá-las aos analistas mais novos.

Em 1992, houve uma nova reunião no comitê para rever o padrão SQL. Essa revisão foi uma das mais importantes e talvez seja a implementação mais obedecida pelos bancos de dados atuais (mas ainda assim nenhum SGBD a adotou completamente). Essa revisão definiu vários pontos importantes (em especial os JOINs) e ficou conhecida como ANSI92, SQL92 ou ainda SQL2. Vale a pena lembrar que um revisão não "nega" a revisão anterior. Boa parte do padrão SQL89 está presente no SQL92 e assim sucessivamente. O SQL92 introduziu os JOINs de forma a separar as junções das tabelas dos filtros utilizados. A partir do SQL92, a consulta abaixo era possível:

SELECT Nome, Placa, Ano, Descricao
FROM tblClientes LEFT OUTER JOIN tblCarros
ON tblClientes.ClienteID = tblCarros.ClienteID

Quando o SQL92 foi finalizado, a recomendação geral era utilizar o LEFT OUTER JOIN em oposição a construções proprietárias como *= e (+). O fato de ser uma recomendação oficial não quer dizer que da noite pro dia, todos os analistas trocariam todas suas aplicações e os métodos de realizar consultas. Mesmo sendo um padrão, ainda se viu por muito tempo (e até os dias atuais) junções realizadas na cláusula WHERE e a utilização de extensões proprietárias.

Não existe absolutamente nenhuma diferença em termos de desempenho. Utilizar uma junção na cláusula WHERE ou via operadores JOIN tem exatamente o mesmo desempenho. Não sei se no passado isso fazia alguma diferença (acredito que não, pois, a semântica é a mesma). Ainda que houvesse alguma diferença no passado, os otimizadores de consulta atuais estão muito mais inteligentes e percebem que não há absolutamente nenhuma diferença. Isso é verdade para as junções do tipo INNER JOIN, mas e quando as junções do tipo OUTER JOIN ? Será que elas são exatamente iguais aos seus correspondentes proprietários ? Vejamos uma implementação em SQL Server.

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

— 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’)

Exemplos práticos

Vejamos agora alguns exemplos práticos para testar os comportamentos dos operadores OUTER JOIN e seus correspondentes proprietários. As consultas estão categorizadas de acordo com algumas situações bem particulares do dia a dia. Se você estiver usando o SQL Server 2005 ou o 2008, lembre-se de colocar o modo em compatibilidade com o SQL Server 2000 (80). Isso pode ser feito nas propriedades do banco de dados ou através da stored procedure sp_dbcmptlevel.

Situação 1: Junção à esquerda sem cláusula WHERE

O objetivo das consultas abaixo é retornar a relação de todos os clientes e seus carros. Todos os clientes devem ser retornados quer tenham carros ou não. A primeira está em conformidade com o padrão ANSI92 (é ANSI Compliance) e a segunda utiliza os operadores proprietários do Transact SQL.

— Recuperar todos os clientes e os carros se houver
SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR
        ON CLI.ClienteID = CAR.ClienteID

SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI, tblCarros AS CAR
WHERE
    CLI.ClienteID *= CAR.ClienteID

O resultado produzido é exatamente o mesmo:

Nome

Placa

Ano

Descrição

Ivone

JGK7044

2006

Peugeot 2006

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

O plano de execução também é exatamente o mesmo (era de se esperar):

Se os resultados são exatamente os mesmos, é recomendável utilizar o operador LEFT OUTER JOIN por estar na especificação ANSI ao invés de extensões proprietárias.

Situação 2: Junção à esquerda com cláusula WHERE para colunas da tabela à esquerda

O objetivo das consultas abaixo é retornar a relação de todos os clientes e seus carros. Todos os clientes devem ser retornados quer tenham carros ou não. Dessa vez, um filtro é colocado sobre a tabela de clientes, ou seja, é necessário retornar apenas os clientes que possuam a string "an" no nome. A primeira está em conformidade com o padrão ANSI92 (é ANSI Compliance) e a segunda utiliza os operadores proprietários do Transact SQL.

— Recuperar todos os clientes que tenham a string "an" nome e os carros se houver
SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR
        ON CLI.ClienteID = CAR.ClienteID
WHERE
    Nome LIKE ‘%an%’

SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI, tblCarros AS CAR
WHERE
    CLI.ClienteID *= CAR.ClienteID AND Nome LIKE ‘%an%’

O resultado produzido é exatamente o mesmo:

Nome

Placa

Ano

Descrição

Amanda

KHZ0345

2007

Vectra GT

Amanda

BDC3211

2008

Fiat Stilo

Mariana

JGA7438

2008

Corsa Hatch

Regiane

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

O plano de execução também é exatamente o mesmo (era de se esperar):

Se os resultados são exatamente os mesmos, é recomendável utilizar o operador LEFT OUTER JOIN por estar na especificação ANSI ao invés de extensões proprietárias.

Situação 3: Junção à esquerda com cláusula WHERE para colunas da tabela à direita

O objetivo da próxima consulta é recuperar a relação de todos os clientes e os carros de 2006 se houver. Dessa vez o comportamento do LEFT OUTER JOIN e das extensões proprietárias podem fazer com que o resultado seja diferente.

— Recuperar todos os clientes e os carros de 2006 se houver
SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR
        ON CLI.ClienteID = CAR.ClienteID
WHERE
    CAR.Ano = 2006

O resultado da consulta é:

Nome

Placa

Ano

Descrição

Ivone

JGK7044

2006

Peugeot 2006

O plano de execução dessa consulta foi:

Se executarmos a alternativa utilizando a versão proprietária, os resultados serão diferentes:

— Recuperar todos os clientes e os carros de 2006 se houver
SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI, tblCarros AS CAR
WHERE
    CLI.ClienteID *= CAR.ClienteID AND CAR.Ano = 2006

O resultado da consulta é:

Nome

Placa

Ano

Descrição

Ivone

JGK7044

2006

Peugeot 2006

Amanda

NULL

NULL

NULL

Mariana

NULL

NULL

NULL

Regiane

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

O plano de execução parece ser exatamente o mesmo:

Mas se o plano de execução parece ser exatamente o mesmo por que os resultados são diferentes ? E o que significa essa diferença ? Por que a primeira consulta retornou um registro e a segunda retornou 5 ? Embora o plano de execução pareça ser o mesmo ele não é. Há um detalhe visual que pode passar desapercebido. Na primeira consulta (a ANSI), o operador Clustered Index Scan superior está relacionado a PK_Carros e o Cluster Index Scan inferior está relacionado a PK_Cliente. Na segunda consulta (a das extensões) os operadores estão invertidos.

Na consulta ANSI, a junção entre clientes e carros é realizada primeiro e nesse caso são retornados todos os clientes e seus carros e caso haja algum cliente sem carro ele também é incluído no resultado (vimos isso posteriormente). Quando a cláusula WHERE especificou que o ano deve ser 2006, implicitamente admite-se que o carro tenha que existir, pois, somente os carros existentes podem ter o ano do carro comparado. Isso significa que todos os clientes que não possuem carros no ano de 2006 são descartados da consulta, pois, as colunas de carro estariam preenchidas com nulos. De um ponto de visto lógico, implicitamente o LEFT OUTER JOIN foi convertido para um INNER JOIN. De um ponto de vista físico, temos o overhead desnecessário do LEFT OUTER JOIN para uma consulta que poderia ser feita com o INNER JOIN, pois, apenas carro existentes e do ano de 2006 devem ser considerados.

Na consulta que utiliza as extensões proprietárias, o comportamento é um pouco diferente. Como o JOIN é feito via cláusula WHERE, o otimizador entende que a condição de junção é combinar a coluna ClienteID, mas adicionalmente filtrar os carros com ano de 2006. Isso significa que a tabela de carros é lida, os carros cujo o ano não seja igual a 2006 são descartados e um resultado intermediário é produzido (no caso somente o Peugeot 2006). Posteriormente esse resultado é combinado com a tabela de clientes em uma situação de LEFT OUTER JOIN, ou seja, retorna-se todos os clientes quer tenham correspondentes no resultado intermediário ou não. Como apenas Ivone tem um carro de 2006, os dados do carro aparecem. As demais clientes não possuem carros cujo ano é 2006 e portanto tem as colunas preenchidas com NULL.

Alguém pode perguntar: "Qual é o correto então ?". Em princípio, o "correto" não depende da sintaxe, mas sim do resultado esperado. O que é necessário mostrar ? O primeiro resultado ou o segundo resultado ? Já vi situações em que os dois são importantes. O que interessa não é tentar achar o "correto" (os dois podem ser corretos), mas sim identificar que as construções da forma como estão produzem resultados diferentes e que essa diferença precisa ser conhecida para que uma consulta não seja feita e apresente um resultado indesejado.

Outra pergunta que pode surgir é a respeito das extensões proprietárias. Se as extensões proprietárias devem ser evitadas, como então produzir o mesmo resultado sem elas ? Algumas tentativas podem ser elaboradas:

— Recuperar todos os clientes e os carros de 2006 se houver
SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI
    INNER JOIN tblCarros AS CAR
        ON CLI.ClienteID = CAR.ClienteID
WHERE
    CAR.Ano = 2006

UNION ALL

SELECT
    Nome, NULL, NULL, NULL
FROM
    tblClientes AS CLI
WHERE NOT EXISTS
    (SELECT * FROM tblCarros AS CAR
    WHERE CLI.ClienteID = CAR.ClienteID AND Ano = 2006)

A consulta acima retorna todos os clientes que tem carros do ano 2006 e faz uma união com todos os clientes que não tem carros em 2006 (inclusive os que não tem carros). Ela não deixa de estar correta, mas considero um desperdídicio de recursos fazer algo tão trabalhoso e não performático. Embora as extensões proprietárias não devam mais ser utilizadas, podemos usar o raciocínio para produzir o resultado desejado. Quando as extensões proprietárias foram utilizadas, o filtro foi realizado na tabela de carros primeiro produzindo um resultado intermediário que foi combinado com a tabela de clientes. Podemos fazer o mesmo, apenas mudando o filtro de lugar.

— Recuperar todos os clientes e os carros de 2006 se houver
SELECT
    Nome, Placa, Ano, Descricao
FROM
    tblClientes AS CLI
    LEFT OUTER JOIN tblCarros AS CAR
        ON CLI.ClienteID = CAR.ClienteID AND Car.Ano = 2006

O resultado é idêntico ao das extensões proprietárias:

Nome

Placa

Ano

Descrição

Ivone

JGK7044

2006

Peugeot 2006

Amanda

NULL

NULL

NULL

Mariana

NULL

NULL

NULL

Regiane

NULL

NULL

NULL

Eliane

NULL

NULL

NULL

Pode-se concluir que é possível evitar as extensões proprietárias para produzir o resultado acima. Só é preciso verificar o que é predicado de junção, ou seja, as condições que são usadas para juntar as tabelas e o que é filtro para eliminar registros indesejados após a junção. Quando os predicados são referentes à tabela da direita, eles devem ser colocados no JOIN, pois, se forem utilizados na cláusula WHERE, haverá a conversão implícita e nesse caso é mais performático usar o INNER JOIN. Todos as explicações são válidas para o operador RIGHT OUTER JOIN e sua extensão proprietária =*.

Toda migração para o SQL Server 2005 ou 2008 tem um ponto fundamental a ser decidido. Afinal mudar ou não o nível de compatibilidade ? Se mudarmos o nível de compatibilidade para 90 ou 100, poderemos utilizar novos recursos do TSQL como Row_Number, Common Table Expressions, Grouping Sets, etc. A contrapartida é que esses níveis de compatibilidade não toleram o uso das extensões proprietárias *= e =*. Ainda que o nível de compatibilidade 80 não impeça de se utilizar novos recursos do SQL Server 2005 / 2008 (Mirror, P2P Replication, Include Indexes, Transparent Data Encryption, etc), normalmente almeja-se utilizar os recursos novos do Transact SQL. Para que os novos recursos do TSQL possam ser utilizados, é preciso mudar o nível de compatibilidade e isso inclui substituir as construções *= e =*.

Se a migração for de um banco de dados complexo e com muitas regras de negócio, tome muito cuidado e não suponha que basta simplemente trocar *= e =* por LEFT OUTER JOIN e RIGHT OUTER JOIN. Como vimos, em algumas situações o resultado é o mesmo, mas em outras situações o resultado é diferente. E por incrível que pareça pouca gente sabe que tem diferença. Ainda vejo muitos consultores, DBAs e analista acharem que são sinônimos em todas as ocasiões. É preciso fazer as adaptações corretas.

[ ]s,

Gustavo

A divisão relacional (quando o AND e o OR não funcionam)

Boa Noite Pessoal,

Estou devendo várias postagens aqui no meu blog e hoje vou tentar sanar mais uma dívida do meu último Webcast. Dando continuidade à sessão de dicas hoje falarei sobre o problema da divisão relacional. Eu imagino que poucos já tenham ouvido falar sobre esse assunto e provavelmente a maioria que está lendo esse tópico não tem a menor idéia do que venha a ser esse termo. O termo é de fato pouco intuitivo, mas estou certo de que os problemas de divisão relacional são mais comuns do que o nome. Um dia desses respondi um problema de divisão relacional em uma Thread no fórum de SQL Server no Technet.

Uma operação de divisão normal submete-se um valor (dividendo) contra um outro valor (divisor) e obtém-se o resultado da divisão. Se fizermos o cálculo de 10 ÷ 2 obteremos o valor de 5 sendo 10 o valor do dividendo, 2 o valor do divisor e 5 o resultado da divisão. É possível que algumas divisões não sejam exatas, ou seja, gerem algum resto, mas a presença do resto é irrelevante para a analogia da matemática e de banco de dados.

A divisão de 10 por 2 resultou em 5. Quando fazemos a divisão de 10 por 2 estamos segmentando o dividendo 10 contra o divisor 2 e obtendo um determinado resultado igual a 5. Analogamente a um banco de dados é como se estivéssemos fazendo um SELECT em uma tabela chamada 10, e dividíssemos por um conjunto de registros chamado 2 e obtivéssemos o retorno de 5. Outra forma de expressar a divisão relacional refere-se à situação na qual divide-se um conjunto de registros de forma a filtrar registros em subconjuntos cujo o filtro atenda a mais de uma característica de forma exclusiva. Talvez essas frases não façam muito sentido e para facilitar um pouco o entendimento, usarei uma exemplificação. Nada melhor do que algo prático. As tabelas criadas não representam dados reais.

— Cria uma tabela de Clientes
CREATE TABLE tblClientes (ClienteID INT, NomeCliente VARCHAR(50))

— Popula a tabela de Clientes
INSERT INTO tblClientes VALUES (1,‘Wagner’)
INSERT INTO tblClientes VALUES (2,‘Sérgio’)
INSERT INTO tblClientes VALUES (3,‘Felipe’)
INSERT INTO tblClientes VALUES (4,‘Ricardo’)

— Cria uma tabela de carros
CREATE TABLE tblCarros (CarroID INT, ClienteID INT,
    CarroModelo VARCHAR(50), CarroMarca VARCHAR(20))

— Popula a tabela de carros
INSERT INTO tblCarros VALUES (01,01,‘Astra’,‘GM’)
INSERT INTO tblCarros VALUES (02,01,‘Fit’,‘Honda’)
INSERT INTO tblCarros VALUES (03,02,‘Astra’,‘GM’)
INSERT INTO tblCarros VALUES (04,02,‘207’,‘Peugeot’)
INSERT INTO tblCarros VALUES (05,02,‘Fusca’,‘Volks’)
INSERT INTO tblCarros VALUES (06,02,‘Mégane’,‘Renault’)
INSERT INTO tblCarros VALUES (07,03,‘C3’,‘Citroen’)
INSERT INTO tblCarros VALUES (08,03,‘Palio’,‘Fiat’)
INSERT INTO tblCarros VALUES (09,03,‘Polo’,‘Volks’)
INSERT INTO tblCarros VALUES (10,03,‘Fusca’,‘Volks’)
INSERT INTO tblCarros VALUES (11,04,‘Celta’,‘GM’)
INSERT INTO tblCarros VALUES (12,04,‘Palio Weekend’,‘Fiat’)
INSERT INTO tblCarros VALUES (13,04,‘Golf’,‘Volks’)

Para representar a divisão relacional no mundo dos bancos de dados, basta fazer uma pergunta bem simples: Quais são os clientes que tem carros da GM e da Volks e que carros são esses ? Visualmente sabemos que dos quatro clientes, apenas Sérgio e Ricardo de fato possui carros da GM e da Volks. Como representar isso em forma de consulta ? A irresistível vontade é de usar o IN.

SELECT NomeCliente, CarroModelo, CarroMarca
FROM tblClientes AS CL
    INNER JOIN tblCarros AS CR ON CL.ClienteID = CR.ClienteID
WHERE CR.CarroMarca IN (‘GM’,‘Volks’)

De fato essa é intuitivamente a solução quase que imediata, vejamos os resultados apresentados por essa consulta:

NomeCliente

CarroModelo

CarroMarca

Wagner

Astra

GM

Sérgio

Astra

GM

Sérgio

Fusca

Volks

Felipe

Polo

Volks

Ricardo

Celta

GM

Ricardo

Golf

Volks

Visualmente podemos perceber que os clientes Wagner e Felipe (marcados em cinza) não possuem carros da GM e da Volks. Wagner possui um carro da GM é verdade e da mesma forma Felipe possui um carro da Volks. Em todo caso nenhum dos dois possui carros das duas marcas como Sérgio e Ricardo e portanto não atendem à consulta inicial. Se a necessidade é de haver carros da GM e da Volks, o próximo impulso é utilizar a consulta abaixo:

SELECT NomeCliente, CarroModelo, CarroMarca
FROM tblClientes AS CL
    INNER JOIN tblCarros AS CR ON CL.ClienteID = CR.ClienteID
WHERE CR.CarroMarca = ‘GM’ AND CR.CarroMarca = ‘Volks’

Se a consulta acima for executada, nenhum resultado será retornado. Isso acontece porque o carro pode ser de uma marca somente, ou seja, ou o carro pertence à GM ou pertence a Volks. Como não é possível que um carro pertença às duas marcas não haverá nenhum carro que satisfaça aos predicados na cláusula WHERE e portanto nenhum registro é retornado.

Se você sentiu um nó na cabeça e não sabe como prosseguir para retornar o resultado desejado, eu acabo de apresentar-lhe o problema da divisão relacional. É justamente a ausência de um "OU" exclusivo na SQL que possibilita que esse "nó" apareça. É certo que existem alternativas, pois, seria vergonhoso se a SQL não tivesse alternativas para lidar com um problema tratado na álgebra relacional cuja as bases permitiram o surgimento da SQL. É claro que a resposta não é tão simples uma vez que não há um operador próprio para lidar com a divisão relacional. Aos que forem mais persistentes, sugiro continuar com mais algumas tentativas antes de prosseguir com a leitura das soluções.

Quando expus esse problema para alguns amigos DBAs em DB2, um dele expos uma solução que achei interessante. Aparentemente ela pode não ser performática, mas se propõe a resolver o problema e retornar os valores desejados

SELECT NomeCliente, CarroModelo, CarroMarca
FROM tblClientes AS CL
    INNER JOIN tblCarros AS CR ON CL.ClienteID = CR.ClienteID
WHERE
    CR.CarroMarca IN (‘GM’,‘Volks’)
    AND EXISTS (SELECT * FROM tblCarros AS CG
        WHERE CR.ClienteID = CG.ClienteID AND CarroMarca = ‘GM’)
    AND EXISTS (SELECT * FROM tblCarros AS CV
        WHERE CR.ClienteID = CV.ClienteID AND CarroMarca = ‘Volks’)

Confesso que quando fiz o Webcast eu não tinha levantado essa possibilidade. Ela é ANSI Compliance e funciona em outros SGDBs. O problema é o desempenho. Nas palavras desse DBA, "o problema é que são três consultas para retornar uma coisa só". Sim, eu concordo. Podemos diminuir os problemas de desempenho com índices sobre ClienteID e CarroMarca. Provalvelmente esses índices já existiriam, mas continuam sendo três consultas para retornar uma coisa só.

Uma outra alternativa compatível com a especificação ANSI é a utilização do operador INTERSECT para fazer a intersecção entre dois conjuntos. A operação de intersecção é aquela que retorna os membros de que estejam em todos os conjuntos. Sendo o conjunto A os clientes que possuem carros da GM e o conjunto B os clientes que possuem carros da Volks, a intersecção do conjunto A e B é o conjunto de clientes que possui carros da GM e da Volks.

— Selecionar todos que tem carros da GM
SELECT ClienteID FROM tblCarros WHERE CarroMarca = ‘GM’

INTERSECT — Faz a interseção

— Selecionar todos que tem carros da Volks
SELECT ClienteID FROM tblCarros WHERE CarroMarca = ‘Volks’

Para quem não conhece o operador INTERSECT, vale a pena lembrar que ele está na especifição ANSI 92 e que por uma razão que desconheço apenas no SQL Server 2005 é que finalmente ele foi contemplado no TSQL. A consulta acima fez a interseção dos clientes que possuem carros da GM e dos clientes que possuem carros da Volks. Para ficar uma solução mais completa é necessário efetuar o JOIN entre o resultado da intersecção e as tabelas de carros e de clientes. A consulta abaixo o faz através de uma Common Table Expression (CTE).

— Juntando tudo
;WITH ClientesGMVolks AS (
SELECT ClienteID FROM tblCarros WHERE CarroMarca = ‘GM’
INTERSECT — Faz a interseção
SELECT ClienteID FROM tblCarros WHERE CarroMarca = ‘Volks’)

SELECT NomeCliente, CarroModelo, CarroMarca
FROM tblClientes AS CL
    INNER JOIN ClientesGMVolks AS GV ON CL.ClienteID = GV.ClienteID
    INNER JOIN tblCarros AS CR ON CL.ClienteID = CR.ClienteID
WHERE CarroMarca IN (‘GM’,‘Volks’)

Uma outra solução que utiliza recursos do SQL Server 2005 é o uso do operador pivot para transformar as marcas em colunas. Como o interesse é apenas nas marcas GM e Volks, basta criar uma coluna para cada marca.

SELECT ClienteID, [GM], [Volks]
FROM (SELECT ClienteID, CarroMarca FROM tblCarros) AS ST
PIVOT (COUNT(CarroMarca) FOR CarroMarca IN ([GM], [Volks])) AS PT

Uma vez que haja uma coluna para contar quantos carros cada cliente tem da GM e uma coluna para contar quantos carros cada cliente tem da Volks, para saber que clientes tem carros da GM e da Volks, para filtrar essas colunas para que a quantidade seja superior a zero. Como o objetivo é recuperar não só os clientes, como também os nomes e os carros, a query precisa ser um pouco mais trabalhada.

— Selecione todos os clientes e seus carros
SELECT NomeCliente, CarroModelo, CarroMarca
FROM tblClientes AS CL
    INNER JOIN tblCarros AS CR ON CL.ClienteID = CR.ClienteID
WHERE

— Onde o cliente esteja na lista de clientes que tem carros da GM e Volks
CL.ClienteID IN (

SELECT ClienteID
FROM (SELECT ClienteID, CarroMarca FROM tblCarros) AS ST
PIVOT (COUNT(CarroMarca) FOR CarroMarca IN ([GM], [Volks])) AS PT
WHERE GM >= 1 AND Volks >= 1)

— E o carro seja da GM ou da Volks
AND CarroMarca IN (‘GM’,‘Volks’)

A solução que costumava utilizar antes de parar para é bem simples e funciona relativamente bem além de não depender de recursos como o INTERSECT ou o PIVOT. Isso garante uma portabilidade maior para essa solução. Antes de postá-la é interessante detalhar o raciocínio para chegar à solução.

SELECT ClienteID, COUNT(CarroMarca) As TotalMarcas
FROM tblCarros
WHERE CarroMarca IN (‘GM’,‘Volks’)
GROUP BY ClienteID

Essa consulta produz o seguinte resultado:

ClienteID

TotalMarcas

1

1

2

2

3

2

4

2

O cliente 1 (Wagner) de fato só possui um carro da GM e por isso o TotalMarcas retornou 1. Os clientes 2 e 4 (Sérgio e Ricardo) possuem um carro da GM e um carro da Volks cada. A consulta estaria correta senão fosse o fato de que o cliente 3 (Felipe) possui dois carros da Volks e por isso sua contagem foi igual a dois mesmo que ele não possua carros da GM. A contagem de marcas é feita com base no total de carros e não é feita distintamente. Se a contagem fosse distinta, Felipe poderia ter dois carros da Volks, mas a marca seria apenas uma. O uso do DISTINCT faz a contagem distintamente. Posteriormente basta juntar os resultados seja com um JOIN ou com uma Common Table Expression (CTE). Fiz o exemplo baseado em CTE, mas é possível utilizar o SELECT da CTE em uma subquery.

;WITH CGV AS (
— Conte os clientes e suas marcas distintamente
SELECT ClienteID, COUNT(DISTINCT CarroMarca) As TotalMarcas
FROM tblCarros

— Onde a marca do carro seja GM ou Volks
WHERE CarroMarca IN (‘GM’,‘Volks’)
GROUP BY ClienteID

— Onde apenas os clientes com duas marcas sejam escolhidos
— Como só a GM e Volks não há como ter mais de duas marcas
HAVING COUNT(DISTINCT CarroMarca) = 2)

SELECT NomeCliente, CarroModelo, CarroMarca
FROM tblClientes AS CL
    INNER JOIN tblCarros AS CR ON CL.ClienteID = CR.ClienteID
WHERE CarroMarca IN (‘GM’,‘Volks’) AND EXISTS
    (SELECT * FROM CGV WHERE CL.ClienteID = CGV.ClienteID)

Há uma última solução baseada em lógica reversa que embora eu não tenha apresentado no Webcast é bem interessante. Como o entendimento dessa solução não é tão simples, eu a postarei de forma desmembrada.

— Grava as marcas em uma tabela temporária
CREATE TABLE #Marcas (CarroMarca VARCHAR(20))
INSERT INTO #Marcas VALUES (‘GM’)
INSERT INTO #Marcas VALUES (‘Volks’)

Se a tabela estivesse desnormalizada, possivelmente haveria uma tabela com as marcas desejadas, como faz parte da solução obter um conjunto com as marcas desejadas, faz-se necessário gravá-las em um formato tabular visto que não há uma tabela de marcas de carro.

— Produz um plano cartesiano de todos os clientes e marcas desejadas
SELECT ClienteID, CarroMarca
FROM tblClientes CROSS JOIN #Marcas

O resultado do plano cartesiano gera um registro para cada combinação cliente e marca. Como há duas marcas de interesse (GM e Volks), é gerado dois registros para cada cliente (um com GM e outro com Volks).

SELECT ClienteID, CarroMarca
FROM tblClientes AS CL CROSS JOIN #Marcas AS M
WHERE NOT EXISTS
    (SELECT * FROM tblCarros AS CR
    WHERE CL.ClienteID = CR.ClienteID AND M.CarroMarca = CR.CarroMarca)
    ORDER BY ClienteID

O CROSS JOIN entre Clientes e Marcas produz todas as combinações hipotéticas entre Clientes e Marcas enquanto que a tabela de carros possui todas as combinações Clientes e Marcas que realmente existem. Se retirarmos as combinações Clientes e Marcas existentes do conjunto das combinações de Clientes e Marcas hipotéticas, teremos o conjunto de Clientes e Marcas que não foram utilizados. A consulta acima retorna o seguinte resultado:

ClienteID

CarroMarca

1

Volks

3

GM

Como o cliente 1 (Wagner) só possui carros da GM, a combinação Wagner – Volks é apenas hipotética já que ela não existe. O mesmo ocorre para a combinação Felipe (Cliente 3) e a marca GM. Os clientes 2 e 4 (Sérgio e Ricardo) não são retornados, pois, ambos possuem carros da Volks e da GM. As combinações referentes a esses clientes são de fato existentes e não apenas hipotéticas. 

SELECT DISTINCT ClienteID
FROM tblClientes AS CL CROSS JOIN #Marcas AS M
WHERE NOT EXISTS
    (SELECT * FROM tblCarros AS CR
    WHERE CL.ClienteID = CR.ClienteID AND M.CarroMarca = CR.CarroMarca)
    ORDER BY ClienteID

A consulta acima apenas retira a coluna CarroMarca da consulta anterior, pois, o que importa é saber os clientes que não atendam às duas marcas (GM e Volks). O uso do DISTINCT é necessário, pois, se um cliente não possuir nem carros da GM e nem carros da Volks, ele apresentará duas combinações e será portanto duplicado. O interesse não é descobrir qual das duas marcas o cliente não possui, mas sim que ele não possui as duas marcas. Uma vez que os clientes que não possuam carros da GM e da Volks sejam conhecidos, basta retirar esse resultado do conjunto de clientes. O resultado será o conjunto de clientes que possui carros da GM e da Volks conforme a consulta abaixo:

SELECT * FROM tblClientes WHERE ClienteID
NOT IN (
    SELECT DISTINCT ClienteID
    FROM tblClientes AS CL CROSS JOIN #Marcas AS M
    WHERE NOT EXISTS
    (SELECT * FROM tblCarros AS CR
    WHERE CL.ClienteID = CR.ClienteID AND M.CarroMarca = CR.CarroMarca))

O resultado exibirá apenas os clientes 2 e 4, pois, são os únicos clientes que possuem carros da GM e da Volks. O código baseado em lógica reversa (foram dois NOT EXISTS) é didático, mas da forma que foi exposto está cheio de práticas desaconselháveis como tabelas temporárias e o degradador NOT IN. O mesmo código pode ser escrito de uma forma um pouco mais performática.

;WITH

— Informa as marcas desejadas
MarcasDesejadas (CarroMarca) AS
(SELECT ‘GM’ UNION ALL SELECT ‘Volks’),

— Localiza todos os clientes que não tenham essas marcas
CSGV (ClienteID) AS

(SELECT DISTINCT ClienteID
FROM tblClientes AS CL CROSS JOIN MarcasDesejadas AS M
WHERE NOT EXISTS
    (SELECT * FROM tblCarros AS CR
    WHERE CL.ClienteID = CR.ClienteID AND M.CarroMarca = CR.CarroMarca))

— Retorna todos os clientes e seus carros que não estejam na lista CSGV
SELECT NomeCliente, CarroModelo, CarroMarca
FROM tblClientes AS CL
    INNER JOIN tblCarros AS CR ON CL.ClienteID = CR.ClienteID
WHERE CarroMarca IN (SELECT CarroMarca FROM MarcasDesejadas)
AND NOT EXISTS
    (SELECT * FROM CSGV WHERE CL.ClienteID = CSGV.ClienteID)

A alternativa da lógica reversa é um pouco mais díficil, mas não deixa de ser uma alternativa válida. Acredito que as alternativas apresentadas aqui sejam suficientes para resolver problemas comuns de divisão relacional (mesmo que não se saiba exatamente que esse é o nome do problema quando se depara com ele). O interessante de haver muitas alternativas é possibilitar ao implementador a escolha. É importante avaliar sempre a melhor das alternativas em termos de desempenho. Algumas alternativas podem apresentar um desempenho melhor ou pior que outras dependendo do volume de dados, da proposta de indexação, etc.

Espero que agora "divisão relacional" seja apenas um nome difícil de lembrar e não mais uma consulta difícil de fazer.

[ ]s,

Gustavo

Matrizes no SQL Server 2005 – Parte III

Boa Noite Pessoal,

Dando continuidade ao assunto de matrizes no SQL Server 2005, hoje falarei sobre a utilização do CLR para trabalhar com um tipo de dados Array. Dessa vez não utilizei a expressão "simular um tipo de dados Array". Isso propositalmente, visto que o CLR permite a construção de tipos cujo escopo possibilita ir além de derivação de tipos mais simples (User Data Types).

Um pouco sobre CLR

CLR é o acrônimo de Common Language Runtime. O CLR é a peça mais fundamental do Framework .NET e a partir do SQL Server 2005 é possível utilizar esse framework diretamente no SQL Server. Isso possibilita a escrita de stored procedures, triggers, functions em alguma linguagem suportada pelo Framework .NET (tipicamente o VB.NET e o C#), a criação de agregações e tipos próprios.

O T-SQL é uma linguagem poderosa, mas fundamentalmente criada para facilitar a recuperação e gravação de dados. Embora o T-SQL possua uma vasta coleção de comandos que complementam as operações DML tradicionais (SELECT, INSERT, UPDATE, DELETE) ele jamais chegará próximo de linguagens de programação mais tradicionais. Estruturas de laço e repetição (WHILE) bem como de controle de fluxo (IF, ELSE) estão presentes, mas dificilmente veremos tópicos mais avançados como a presença de funções de manipulação de texto (split, insert, etc), um bom suporte a expressões regulares e funções de rede como hostname, ping, telnet, etc.

Isso é perfeitamente compreensível já que o objetivo primordial de um dialeto da SQL como o T-SQL é primar pela recuperação e gravação de dados da melhor forma possível e não tornar-se uma linguagem de programação para competir com o C#, Java, etc. Ainda assim, existem situações que podem exigir outras funcionalidades que não a recuperação e gravação de dados. Se for necessário que uma procedure conecte-se a um WebService para efetuar uma validação ? Se for preciso realizar o cálculo do coeficiente de correlação entre duas séries temporais ? Como fazer que o SQL Server aproveite os métodos de uma classe presente em um componente de terceiros para realizar a geração de arquivos consolidados ?

As situações descritas acima deixam o T-SQL em cheque visto que ele não possui recursos nativos para lidar com as mesmas. Ainda que o implementador tivesse um conhecimento fora do comum e conseguisse codificar algo em T-SQL para atender essas situações, provavelmente o código seria difícil de manter além de pouco performático. Para situações como as descritas, codificar algo suportado pelo Framework .NET é uma alternativa a se considerar.

Há vários prós e contras utilizar o CLR e sem dúvida existem áreas de sombreamento entre o CLR e o T-SQL. O link abaixo pode esclarescer muitas dúvidas de quando utilizar o T-SQL e quando utilizar o CLR.

Como e quando programar em CLR no Microsoft SQL Server 2005
http://www.microsoft.com/brasil/msdn/Tecnologias/sql/CLRSQLServer2005.mspx

Não é o foco descrever o uso do CLR e suas possibilidades no banco de dados. Há várias questões a se considerar como desempenho, segurança, versionamento, etc. Por hora apenas utilizarei o CLR para criar um tipo de dados Array de forma mais nativa do que as simulações apresentadas nos tópicos anteriores. Também ressalto que meus conhecimentos em CLR não são avançados e possivelmente haverá códigos mais eficientes do que o que eu proponho (se alguém tiver algum que me envie). O foco é demonstrar que o CLR também pode ser utilizado para trabalhar com arrays.

Uma pequena degustação

O primeiro passo é habilitar o uso do CLR, que por padrão, vem desabilitado. Podemos fazer isso através do SQL Server Surface Area Configuration ou via T-SQL.

— Habilita o uso do CLR na instância do SQL Server
EXEC sp_configure ‘clr enabled’, 1

— Força que as alterações tenham efeito sem a necessidade de reiniciar o serviço
reconfigure with override

A utilização do CLR normalmente é codificada através do Visual Studio. Entretando, ao contrário do que possa parecer, não é necessário ter o Visual Studio para compilar código CLR e carregá-lo no SQL Server já que o SQL Server 2005 utiliza o Framework .NET também e possui o compilador. Ainda que o SQL Server 2005 não estivesse presente, o próprio Windows Update faz a instalação do Framework 2.0 (atualmente estamos no 3.5). O script abaixo cria a function para retornar em um formato tabular uma seqüência de parâmetros delimitada por algum caractér.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Text;
using System.Collections;

public partial class UserDefinedFunctions
{
    [SqlFunction(
            FillRowMethodName = "PopularLinha",
           TableDefinition = "Elemento nvarchar(max)")]
    public static IEnumerable fnSplit(SqlString str, string separador)
    {
        string[] strArray;
        if (!str.IsNull)
        {
            strArray = str.Value.Split(separador.ToCharArray());
            return strArray;
        }
        else
            return "";
    }

    public static void PopularLinha(Object obj, out string Elemento)
    {
        Elemento = (string)obj;
    }

};

Para gerar o Assembly a partir desse código siga os seguintes passos:

  • Copiar e colar o código no bloco de notas
  • Salvar o arquivo como fnSplit.cs em algum diretório (ex: C:\Fontes\)
  • 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 C:\Fontes\fnSplit.cs (supondo que o arquivo esteja em C:\Fontes\)
  • Localizar o arquivo fnSplit.dll na pasta utilizada no prompt
  • Mover a DLL para um local mais apropriado (ex: C:\Executaveis\)

Uma vez que o Assembly esteja compilado, resta carregá-lo para o SQL Server, criar a function com base no Assembly e utilizá-la.

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY Funcoes FROM ‘C:\Executaveis\fnSplit.dll’

— Cria uma Function baseada no Assembly
CREATE FUNCTION dbo.fnSplit(@Array NVARCHAR(MAX), @Separador NCHAR(1))
RETURNS TABLE (Elemento NVARCHAR(MAX))
AS
EXTERNAL NAME
Funcoes.UserDefinedFunctions.fnSplit
GO

— Utiliza a função definida no Assembly
SELECT Elemento FROM dbo.fnSplit(‘SP;RJ;DF’,‘;’)

Dada algumas limitações do CLR, irei trabalhar um código para criação de um tipo Array fixo em três elementos.

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 TArray : INullable
{
    private bool Nulo;

    public SqlInt16 Pos1;
    public SqlInt16 Pos2;
    public SqlInt16 Pos3;

    public override string ToString()
    {
        return
            Pos1.ToString() + ";" + Pos2.ToString() + ";" + Pos3.ToString() + ";";
    }

    public bool IsNull
    {
        get
        {
            return Nulo;
        }
    }

    public static TArray Null
    {
        get
        {
            TArray TA = new TArray();
            TA.Nulo = true;
            return TA;
        }
    }

    public static TArray Parse(SqlString s)
    {
        try
        {
            if (s.IsNull)
                return Null;
            TArray TA = new TArray();
            string[] SA = s.ToString().Split(";".ToCharArray());

            TA.Pos1 = SqlInt16.Parse(SA[0]);
            TA.Pos2 = SqlInt16.Parse(SA[1]);
            TA.Pos3 = SqlInt16.Parse(SA[2]);

            return TA;
        }
        catch
        {
            throw new ArgumentException("A inicialização da matriz não está correta");
        }

    }

    public SqlDouble Media()
    {
        return (Pos1 + Pos2 + Pos3) / 3;
    }

    public SqlInt16 MenorValor()
    {
        SqlInt16 res = Pos1;
        if (Pos2 <= Pos1)
        {
            res = Pos2;
            if (Pos3 <= Pos2)
                res = Pos3;
        }
        else
        {
            if (Pos3 <= Pos1)
                res = Pos3;
        }

        return res;
    }

    public SqlInt16 MaiorValor()
    {
        SqlInt16 res = Pos1;
        if (Pos2 >= Pos1)
        {
            res = Pos2;
            if (Pos3 >= Pos2)
                res = Pos3;
        }
        else
        {
            if (Pos3 >= Pos1)
                res = Pos3;
        }

        return res;
    }

}

A compilação desse Assembly deve ser feita seguindo-se exatamente os mesmos procedimentos para a compilação do Assembly anterior. Após a compilação estar concluída e o Assembly tiver sido copiado para um diretório mais apropriado, poderemos trabalhar com ele. O script abaixo demonstra a utilização de um tipo Array baseado em .NET.

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY Tipos FROM ‘C:\Executaveis\TipoArray.dll’

— Cria um novo tipo Array a partir do Assembly
CREATE TYPE dbo.Array
EXTERNAL NAME Tipos.TArray

— Cria uma variável do tipo Array
DECLARE @Array dbo.Array

— Inicializa o Array
SET @Array = CAST(’15;20;50′ AS dbo.Array)

— Mostra os valores do Array
SELECT
    @Array.Pos1 AS Elemento1, @Array.Pos2 AS Elemento2, @Array.Pos3 AS Elemento3,
    @Array.Media() AS Media, @Array.MenorValor() AS MenorValor, @Array.MaiorValor() AS MaiorValor

O interessante é que uma vez que o tipo Array exista no banco de dados, o mesmo pode ser utilizado inclusive em tabelas e cláusulas SQL. O script abaixo demonstra como criar uma coluna do tipo Array em uma tabela fictícia de alunos e notas escolares (supondo que cada aluno tenha exatamente três notas).

— Cria uma tabela de Alunos
CREATE TABLE Alunos (NomeAluno VARCHAR(50), Notas dbo.Array)

— Insere registros
INSERT INTO Alunos VALUES (‘Pedro’,CAST(‘5;7;9’ AS dbo.Array))
INSERT INTO Alunos VALUES (‘Leandro’,CAST(‘4;10;9’ AS dbo.Array))
INSERT INTO Alunos VALUES (‘Michelle’,CAST(‘8;9;9’ AS dbo.Array))

— Retorna o nome dos alunos e suas médias
SELECT NomeAluno, Notas.Media() AS Media FROM Alunos

— Retorna os alunos cuja maior nota seja igual a 9
SELECT NomeAluno, Notas.MaiorValor() AS MaiorNota, Notas.Media() AS Media
FROM Alunos WHERE Notas.MaiorValor() = 9

— Retorna todos os dados
SELECT NomeAluno, Notas FROM Alunos

A implementação CLR realmente criou um tipo de dados Array nativo uma vez que ele pode ser utilizado tanto como variável quanto colunas em uma tabela assim como qualquer outro tipo (VARCHAR, INT, SMALLDATETIME, etc). O exemplo postado se assemelha muito a uma tabela aninhada, ou seja, uma tabela dentro de outra tabela. Uma vez que o SQL Server tenha o tipo de dados TABLE, mas esse não possa ser utilizado como coluna, essa seria uma abordagem alternativa para tornar isso possível. É um recurso muito parecido com o Nested Table do Oracle.

Embora seja uma abordagem interessante (e bem incomum), isso não quer dizer que seja a melhor solução. Utilizar uma tabela de notas com uma Foreign Key apontando para Alunos ainda seria o mais correto do ponto vista relacional. Se a matriz for fixa, é mais vantajoso criar uma coluna para cada elemento (no caso três colunas de notas). O tipo de dados criado não é muito performático, visto que não pode ser indexado da forma como foi concebido. A visualização sempre dependerá de escolher a propriedade ou o método correto. Isso só reforça que o uso do CLR deve ser feito com muita cautela e que ele não é a substituição do T-SQL, mas sim uma outra alternativa.

Acho que finalizo por aqui minhas dicas sobre T-SQL e Arrays. Aos que desejarem aprofundar-se não deixem de consultar o link abaixo:

Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html

[ ]s,

Gustavo

Matrizes no SQL Server 2005 – Parte II

Na dica anterior, demonstrei como utilizar matrizes no SQL Server 2005 através da delimitação de uma string com um separador retornando uma tabela com os elementos da matriz. É uma técnica bem simples e que pode ajudar em diversas situações, mas é também uma técnica bem limitada e serve para trabalhar com matrizes cujo o elemento tenha apenas uma única característica (possivelmente o nome).

Reafirmo que o SQL Server não possui tipos Array nativos, mas sim formas de contornar as necessidades deles e de simulá-los. Hoje demonstrarei como fazer isso através da utilização do XML no SQL Server 2005.

Um pouco sobre XML

O XML é um formato semi-estruturado. Diz-se semi-estruturado porque ele obedece a algumas regras de formação no que diz respeito a declaração de tags, presença de um elemento root, etc, mas ao mesmo tempo é um formato bastante flexível uma vez que existe total liberdade na criação de elementos, atributos, etc. Por padrão não há regras para a quantidade de elementos, atributos, aninhamentos, etc. Essa natureza semi-estruturada permite a livre confecção do XML da forma que melhor convier a quem o projeta. Como o XML não é tão rígido quanto estruturas relacionais é possível declarar uma estrutura de documento XML de forma muito flexível além de superar as limitações de uma representação tabular constituída de linhas e colunas.

Convém lembrar que toda essa liberdade possui um preço. A ausência de limites claramente definidos na montagem de um documento XML pode tornar difícil a tarefa de administrá-lo. Se forem criadas elementos e atributos de forma anárquica, ou seja sem regras preestabelecidas, pode ficar difícil de gerenciar um documento XML. Sem uma estrutura padronizada de um documento XML, haverá dificuldades em consultá-lo e atualizá-lo. Para tornar a tarefa de padronização de um documento XML possível, temos à disposição o padrão XML Schema cuja implementação no SQL Server 2005 se dá através de uma XML Schema Collection. Os interessados em consultar mais sobre esse assunto podem consultar um artigo de minha autoria disponível no site do Plugmasters.

O XML realmente permite uma ampla flexibilidade que deve ser trabalhada para torná-lo gerenciável. Impor regras de validade a um documento XML seria de certa forma semelhante às regras impostas nas tabelas e colunas. Em todo caso, em documentos XML podemos praticamente escolher até onde limitá-lo enquanto que as restrições tabulares são mais rígidas e difíceis de transpor. É esse aspecto que o torna tão poderoso já que permite representações tabulares, hierárquicas, complexas, etc. Utilizando os padrões XPath e XQuery pode-se inclusive realizar consultas contra documentos XML o que amplia ainda mais as possibilidades. Utilizaremos o poder do XML e suas implementações no SQL Server 2005 para trabalhar com Arrays.

XML e Arrays

Para iniciarmos o uso do XML, utilizarei-me dos scripts presentes na dica anterior. Criaremos as mesmas tabelas e registros utilizados.

— Cria uma tabela de Clientes
CREATE TABLE tblClientes (Nome VARCHAR(80), UF CHAR(2))

— Popula a tabela de Clientes
INSERT INTO tblClientes VALUES (‘Aline’,‘SP’)
INSERT INTO tblClientes VALUES (‘Lívia’,‘DF’)
INSERT INTO tblClientes VALUES (‘Jaqueline’,‘DF’)
INSERT INTO tblClientes VALUES (‘Cecília’,‘MG’)
INSERT INTO tblClientes VALUES (‘Marana’,‘RS’)
INSERT INTO tblClientes VALUES (‘Ronaldo’,‘DF’)
INSERT INTO tblClientes VALUES (‘Gilvan’,‘PR’)
INSERT INTO tblClientes VALUES (‘Dayane’,‘DF’)
INSERT INTO tblClientes VALUES (‘Victor’,‘RJ’)
INSERT INTO tblClientes VALUES (‘Vinicius’,‘PR’)
INSERT INTO tblClientes VALUES (‘Marana’,‘RS’)

O desafio anterior era procurar repassar um Array para realizar a pesquisa dos clientes nos estados São Paulo, Rio de Janeiro e Rio Grande do Sul. Possivelmente esses valores serão repassados através de um string. Utilizarei os mesmos estados, mas antes é importante um conversão da string para um formato XML conforme o script abaixo:

— Declara variáveis
DECLARE @Estados VARCHAR(20), @EstadosXML XML
SET @Estados = ‘SP,RJ,RS’
SET @EstadosXML = ‘<es><e>’ + REPLACE(@Estados,‘,’,‘</e><e>’) + ‘</e></es>’

— Mostra o conteúdo em XML bem formado
SELECT @EstadosXML

O que o script fez foi transformar a string que separava os estados em um formato XML bem formado. Aparentemente esse mudança é mínima, mas para trabalhar com XML é esperado que utilizemos um formato XML e não uma string pura. A mínima mudança entretanto é bem mais significativa do que parece. O resultado em XML não é uma mera representação. Uma vez que os dados estejam em XML é possível utilizar alguns métodos para trabalhá-lo. O script abaixo demonstra como converter o XML em um formato relacional.

— Declara variáveis
DECLARE @Estados VARCHAR(20), @EstadosXML XML
SET @Estados = ‘SP,RJ,RS’
SET @EstadosXML = ‘<es><e>’ + REPLACE(@Estados,‘,’,‘</e><e>’) + ‘</e></es>’

— Mostra os elementos do XML em formato tabular
SELECT es.e.value(‘.’,‘char(2)’) As Estado
FROM @EstadosXML.nodes(‘/es/e’) es (e)

Uma vez que os dados estejam em formato tabular fica bem mais fácil aplicar utilizar esse formato para realizar pesquisas.

DECLARE @Estados VARCHAR(20), @EstadosXML XML
SET @Estados = ‘SP,RJ,RS’
SET @EstadosXML = ‘<es><e>’ + REPLACE(@Estados,‘,’,‘</e><e>’) + ‘</e></es>’

SELECT Nome, UF FROM tblClientes WHERE UF IN (
SELECT es.e.value(‘.’,‘char(2)’) As Estado
FROM @EstadosXML.nodes(‘/es/e’) es (e))

Esse foi apenas um exemplo do que o XML pode fazer para simular aplicações com Array. É importante perceber que os métodos de pesquisa baseados em XPath e XQuery também permitem obter outras propriedades de um Array. O script abaixo conta quantos elementos existem em um Array baseado em XML e também mostra o valor do penúltimo elemento.

— Declara variáveis
DECLARE @Estados VARCHAR(20), @EstadosXML XML
SET @Estados = ‘SP,RJ,RS’
SET @EstadosXML = ‘<es><e>’ + REPLACE(@Estados,‘,’,‘</e><e>’) + ‘</e></es>’

— Mostra quantos elementos existem no Array
SELECT @EstadosXML.value(‘count(/es/e)’,‘int’) As TotalEstados

— Mostra o penúltimo elemento
SELECT @EstadosXML.value(‘(/es/e[last()-1])[1]’,‘CHAR(2)’) As TotalEstados

Um exemplo mais elaborado

Os exemplos acima são interessantes, mas ainda estão incorrendo na natureza unidimensional da solução apontada na dica anterior. O XML apenas tornou as coisas mais fáceis e menos procedurais, mas ainda não superou as limitações da delimitação da string. O próximo exemplo supera um pouco esses limites. Ele consiste no cadastro de um cliente e seus telefones através de uma stored procedure. Embora os dados de clientes e telefones estejam normalmente em tabelas separadas, todos os dados são repassados para uma única stored procedure capaz de cadastrar tantos os clientes quanto os telefones. Para esse exemplo não me preocupei com as constraints, pois, a idéia básica é demonstrar como o XML pode ajudar a montar um Array de telefones.

— Cria as tabelas
CREATE TABLE tblClientes (IDCliente INT Identity(1,1), Nome VARCHAR(80), RG VARCHAR(20))
CREATE TABLE tblTelefones (IDCliente INT, Tipo VARCHAR(20), Numero CHAR(8))
GO

— Cria Stored Procedure
CREATE PROCEDURE uspInsereClientes
    @DadosCliente XML
AS

— Captura os dados do cliente
DECLARE @Nome VARCHAR(80), @RG VARCHAR(20)
SET @Nome = @DadosCliente.value(‘(/Cliente/@Nome)[1]’,‘VARCHAR(80)’)
SET @RG = @DadosCliente.value(‘(/Cliente/@RG)[1]’,‘VARCHAR(20)’)

— Insere um novo cliente
INSERT INTO tblClientes (Nome, RG) VALUES (@Nome,@RG)

— Captura o ID do novo cliente
DECLARE @IDCliente INT
SET @IDCliente = SCOPE_IDENTITY()

— Insere os telefones do cliente
INSERT INTO tblTelefones (IDCliente, Numero, Tipo)
SELECT @IDCliente,
    tels.tel.value(‘./@Numero’,‘CHAR(8)’),
    tels.tel.value(‘./@Tipo’,‘VARCHAR(20)’)
FROM
    @DadosCliente.nodes(‘/Cliente/Telefones/Telefone’) tels(tel)
GO

A stored procedure espera receber um documento XML que contenha os dados de nome e RG do cliente. Como apenas um cliente por vez é informado, é seguro armazenar os dados de nome e RG desse cliente em variáveis cujo valores são extraídos do documento XML via XPath. Ainda no documento XML podem aparecer um ou mais telefones que devem ser cadastrados na tabela de telefones. O número de telefones é variável e é representado por um Array de telefones cujo o tamanho é desconhecido. Uma vez que a estrutura esteja montada, basta executar a chamada a stored procedure com dados de exemplo:

— Declara uma variável XML
DECLARE @DadosClientePar XML
SET @DadosClientePar = ‘<Cliente Nome="Mário Sérgio" RG="1536799 – SSP/DF">
<Telefones>
<Telefone Tipo="Celular" Numero="92314520"/>
<Telefone Tipo="Residencial" Numero="32262378"/>
<Telefone Tipo="Trabalho" Numero="34145677"/>
</Telefones></Cliente>’

— Executa a procedure passando como parâmetro o XML
EXEC uspInsereClientes @DadosCliente = @DadosClientePar

— Consulta a tabela de clientes para conferência
SELECT IDCliente, Nome, RG FROM tblClientes

— Consulta a tabela de telefones para conferência
SELECT IDCliente, Tipo, Numero FROM tblTelefones

Como pode ser observado, o cliente foi cadastrado corretamente e os seus telefones também aproveitando o ID recém inserido. Dessa vez o Array de telefones não incluiu apenas uma característica de telefone. Foram trabalhadas duas características (o número e o tipo). A stored procedure foi chamada uma única vez passando todos os dados necessários. Não foi necessário sequer um único loop para percorrer os registro do Array (seja via SQL ou aplicação). Em todo caso, o tamanho do documento XML é maior do que os dados em si já que tags e quebras de linhas foram adicionadas.

Os exemplos postados mostram o poder do XML para simular um tipo Array. É necessário apenas utilizar as declarações de elementos do XML para adicionar membros ao Array. A utilização de consultas XPath / XQuery também permitem obter outras informações úteis a cerca do Array como a quantidade de elementos, elementos com base em posição, etc. Como é possível aninhar elementos dentro de elementos, pode-se inclusive criar matrizes dentro de matrizes. Com a utilização da XQuery fica fácil transpor matrizes bidimensionais, tridimensionais, etc para uma estrutura relacional compatível com a SQL.

Nessa dica acabei utilizando diversos recursos e funções do SQL Server 2005. Caso os termos XPath, XQuery, Scope_Identity, etc não lhe sejam familiares eu recomendo a leitura de outros artigos que publiquei no site do Plugmasters. A leitura prévia dos mesmos pode tornar o uso de Arrays com XML ainda mais fácil.

Para os que gostariam de simular algo do tipo no SQL Server 2000, não deixei de pesquisar o Kb da Microsoft no link abaixo:

How to pass array of values into SQL Server stored procedure using XML and Visual Basic .NET
http://support.microsoft.com/kb/555266/en-us

[ ]s,

Gustavo

Matrizes no SQL Server 2005 – Parte I

Bom Dia Pessoal,

Uma das interações naturais de um DBA é com a equipe de desenvolvimento. Essa interação normalmente acontece para esclarescimento de dúvidas, boas práticas, etc. Durante várias dessas interações uma dúvida comum aparecia com uma razoável freqüência. A dúvida era sempre as mesmas, mas às vezes disfarçada em perguntas diferentes:

  • Posso utilizar vetores no T-SQL ?
  • Como fazer para declarar matrizes em Transact-SQL?
  • Como passar vários parâmetros de uma só vez para uma Stored Procedure ?

Toda linguagem dá suporte a tipos básicos de dados como strings, inteiros, booleanos, datas, etc e com a SQL não é diferente. Como as linguagens de programação são bem mais poderosas que a SQL e seus dialetos (T-SQL, PL/SQL, PgPL/SQL, etc) é normal trabalhar com tipos mais complexos como objetos, coleções, etc. As linguagens de programação possuem o Integer e o T-SQL tem o INT como seu correspondente. As linguagens de programação possuem o String e o T-SQL tem o VARCHAR como seu correspondente mais imediato. As linguagens de programação tem os Arrays, Lists e Collection e a procura por algo similar no T-SQL é mais do que comum.

Imagina-se então que deva haver algum tipo de dados para fazer isso, algum comando oculto ou simplesmente algum SELECT imbutindo um simples truque para simular um array. Bom, embora truques e soluções alternativas existam, o fato é que por padrão o SQL Server não possui um tipo de dados Array. A natureza da SQL é ser atômica e um Array representa algo multivalorado e sem dúvida proibitivo para o mundo relacional. Imagine uma tabela com uma coluna Array ? Isso seria admitir múltiplos valores para uma mesma coluna em um determinado registro e seria algo não atômico. Se fosse possível, estaríamos presenciando os chamados grupos de repetição violando inclusive a 1ª forma normal.

Em todo caso, a explicação não contradiz o fato de que trabalhar com Arrays facilitaria muito o trabalho dos desenvolvedores. Os valores de um Grid poderiam constituir um Array e serem disparados para o SQL Server de uma só vez. O SQL Server com um tipo "Array" poderia receber todos esses valores e automaticamente gravá-los em uma tabela sem desrespeitar a atomicidade, pois, seria um registro para cada item do Array. Isso iria evitar iterações pelas linhas do Grid. Certamente que existem alguns "Grids" que já fazem isso, mas não deixam de fazer repetidas iterações para gravar o resultado no SQL Server. Cada iteração representa uma transação à parte e um tráfego de rede à parte. Tudo isso incorre em uma certa queda de desempenho.

O T-SQL nos fornece desde alguns truques até algumas soluções mais elaboradas para trabalhar com Arrays ainda que não haja um tipo específico para isso. Demonstrarei um pequeno truque para simular um Array através do T-SQL.

— Cria uma tabela de Clientes
CREATE TABLE tblClientes (Nome VARCHAR(80), UF CHAR(2))

— Popula a tabela de Clientes
INSERT INTO tblClientes VALUES (‘Aline’,‘SP’)
INSERT INTO tblClientes VALUES (‘Lívia’,‘DF’)
INSERT INTO tblClientes VALUES (‘Jaqueline’,‘DF’)
INSERT INTO tblClientes VALUES (‘Cecília’,‘MG’)
INSERT INTO tblClientes VALUES (‘Marana’,‘RS’)
INSERT INTO tblClientes VALUES (‘Ronaldo’,‘DF’)
INSERT INTO tblClientes VALUES (‘Gilvan’,‘PR’)
INSERT INTO tblClientes VALUES (‘Dayane’,‘DF’)
INSERT INTO tblClientes VALUES (‘Victor’,‘RJ’)
INSERT INTO tblClientes VALUES (‘Vinicius’,‘PR’)
INSERT INTO tblClientes VALUES (‘Marana’,‘RS’)

Agora vamos supor que seja necessário informar um RANGE de estados para realizar uma pesquisa. Ex: SP, RJ e RS. Uma tentadora consulta seria a seguinte:

— Faz uma ineficaz tentativa de pesquisa
DECLARE @Estados VARCHAR(20)
SET @Estados = ‘SP,RJ,RS’

SELECT Nome FROM tblClientes WHERE UF IN (@Estados)

Embora alguns fiquem inconformados ou até sem entender porque o código acima não funciona, ou seja, não retorna os cliente de SP, RJ e RS esse é o comportamento correto. A explicação é óbvia. O campo UF é um campo VARCHAR e suporta strings. E se por um acaso existisse um estado chamado SP,RJ,RS ? Se a suposta pesquisa trouxesse os dados de SP, RJ e RS ficaríamos em um ambigüidade, pois a consulta não saberia se deveria retornar registro cujo estado seja SP, RJ ou RS ou os registros cujo o estado fosse o fictício SP,RJ,RS. É por isso que a consulta acima não funciona, pois, não existe nenhum registro que pertença ao estado SP,RJ,RS até porque o UF é um CHAR(2).

Em todo caso, essa é uma demanda por um tipo Array (ainda que disfarçada). Não se tem a intenção de pesquisar os registros cujo estado seja igual a SP,RJ,RS mas sim os registros cujo o estado seja São Paulo, Rio de Janeiro ou Rio Grande do Sul. O anseio é que a string desempenhe um papel de um Array cujo valores são delimitados por , e não de um simples string. Podemos então transformar essa string em um "Array" através de uma function que exponha abaixo:

CREATE FUNCTION [dbo].[fnStringToArray] (@String VARCHAR(1000), @Separador CHAR(1))
RETURNS @Array TABLE (Valor VARCHAR(500))
AS
BEGIN

    IF PATINDEX(‘%’ + @Separador + ‘%’, @String) = 0
        INSERT INTO @Array VALUES (LTRIM(RTRIM(@String)))

    ELSE
    BEGIN
        WHILE
PATINDEX(‘%’ + @Separador + ‘%’, @String) > 0
        BEGIN
            INSERT INTO
@Array VALUES
            (SUBSTRING(LTRIM(RTRIM(@String)), 1, PATINDEX(‘%’ + @Separador + ‘%’, LTRIM(RTRIM(@String))) – 1))

            SET @String = SUBSTRING(@String, PATINDEX(‘%’ + @Separador + ‘%’, @String) + 1, LEN(@String))
        END
        INSERT INTO
@Array VALUES (LTRIM(RTRIM(@String)))
    END

    RETURN
END

Essa função foi emprestada por Alexandre VM em uma das threads do MSDN (embora existam outras soluções parecidas postada por Rafael Krisller e Marcelo Colla em um outra Thread). Esse tipo de função consegue "quebrar" uma string especificando um separador. O resultado é retornado em um formato tabular. Ex:

SELECT Valor FROM [dbo].[fnStringToArray] (‘SP,RJ,RS’, ‘,’)

O resultado desse SELECT corresponde os estados SP, RJ e RS em um formato tabular que dessa forma representa um Array e pode ser utilizado para operações de consulta, inserção, atualização e exclusão. Através dessa função podemos resolver o problema de pesquisa anterior.

— Declara um Array para passagem dos parâmetros e pesquisa posterior
DECLARE @Estados VARCHAR(20)
SET @Estados = ‘SP,RJ,RS’

SELECT Nome, UF FROM tblClientes WHERE UF IN (
SELECT Valor FROM [dbo].[fnStringToArray] (@Estados, ‘,’))

Dessa forma, a string armazenada em @Estados é convertida para um conjunto em formato tabular (o que não deixa de certa forma de ser um Array) e pode ser comparada elemento a elemento e trazer o resultado esperado. Outra possibilidade seria aplicar uma SQL dinâmica.

— Declara um Array para passagem dos parâmetros e pesquisa posterior
DECLARE @Estados VARCHAR(20), @cmdSQL NVARCHAR(200)
SET @Estados = ‘SP,RJ,RS’
SET @Estados = CHAR(39) + REPLACE(@Estados,‘,’,CHAR(39) + ‘,’ + CHAR(39)) + CHAR(39)
SET @cmdSQL = ‘SELECT Nome, UF FROM tblClientes WHERE UF IN (‘ + @Estados + ‘)’
EXEC sp_executesql @cmdSQL

Embora tenha suas desvantagens natas, para esse problema a SQL dinâmica é mais eficiente que utilizar um Array e essa diferença é proporcional a quantidades de elementos, ou seja, quanto mais elementos houver melhor tende a ser o desempenho da SQL dinâmica. Em todo caso, a idéia era abordar a utilização de Arrays por isso o código T-SQL anterior.

O uso da função fnStringToArray demonstra como simular um Array, mas esse método possui algumas limitações. Observe que o Array além de ser unidimensional limita-se a uma única característica para cada elemento, ou seja, apenas o estado. Seria extremamente trabalhoso imbutir o estado e algum outro elemento. De fato, essa implementação simula um Array, mas com muitas limitações (embora seja um começo). Irei demonstrar uma forma mais poderosa de trabalhar com Arrays posteriormente.

[ ]s,

Gustavo

Mais um velho problema de concorrência…

Boa Noite Pessoal,

Participando do fórum MSDN, vi uma Thread que me chamou atenção. O título da Thread é "Como retornar uma mensagem qdo um registro está lockado!" e reflete um velho problema de concorrência (http://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=3634760&SiteID=21)

Como podemos ter o controle sobre os bloqueios em nível de registro ? Sabemos que o SQL Server gerencia bloqueios em vários níveis (banco, arquivos, tabelas, índices, páginas, etc) e que alguns objetos como sysprocesses, sp_lock, sys.dm_exec_requests, sys.dm_tran_locks, etc podem ajudar mas servem apenas para monitoramente e não para controle. Imaginemos a seguinte situação:

Uma aplicação Web recupera e atualiza dados de vários clientes simultâneos. Pode acontecer de dois atendentes (A1 e A2) recuperarem os dados do um Cliente (C1), fazerem atualizações divergentes e apenas um deles sairá vencedor. Suponha que A1 e A2 recuperem os dados e que A1 atualize o estado civil do Cliente C1 de solteiro para casado e que A2 atualize a renda do cliente de R$ 2.000,00 para R$ 3.000,00.

Se A1 vencer, C1 será casado mas possuirá renda de R$ 2.000,00. Se A2 vencer, C1 será solteiro mas possuirá renda de R$ 3.000,00. Nenhuma das situações é a ideal e revelam um problema clássico de concorrência.

Se a aplicação fosse Desktop, enquanto A1 estiver atualizando A2 fica bloqueado. Se A2 chegasse primeiro, A1 ficaria bloqueado. Seria possível detectar que existe um bloqueio, mas ainda que isso fosse possível não teríamos como saber que o bloqueio se deve a um registro específico (C1) e mesmo que conseguíssemos. Em uma situação WEB a conexão a banco de dados não pode ser mantida por muito tempo como uma aplicação Desktop.

Esse impasse é natural já que bloqueios no banco de dados são gerenciados pelo próprio SGBD e não podemos (e nem devemos) a priori influenciar em como o SGBD faz esse controle. O que é necessário não utilizar o mecanismo de bloqueios do SGBD mas sim gerenciar bloqueios em nível de aplicação.

O SQL Server possui duas procedures para tratar isso e que comumente são ignoradas (na verdade até certo tempo atrás eu as desconhecia também). Essas procedures conseguem impor um bloqueio em nível lógico podendo ser trabalhado no TSQL mas sem influenciar nos bloqueios impostos nas estruturas físicas do SQL Server. Elas funcionam como um bit lógico popularmente conhecido como flag. Essas procedures são a sp_getapplock e sp_releaseapplock. São boas para resolver problemas desse tipo, mas prefiro não trabalhar com elas já que serão removidas futuramente (Deprecated Features).

Uma das formas de resolver esse problema é a utilização de um tipo de dados que muita gente conhece mas pouca gente sabe pra que serve. O tipo de dados TIMESTAMP é ótimo para uma situação dessas. Esse tipo de dados é criado automaticamente quando o registro é inserido e alterado automaticamente quando o registro é alterado. Explicarei como ele pode nos ajudar para resolver esse problema. Vamos simular a situação descrita anteriormente. Primeiro rodemos um script para criar a referida tabela. Adicionei uma coluna chamada versão com tipo TimeStamp para utilizarmos para controle.

CREATE TABLE tblClientes (
    Codigo INT, Nome VARCHAR(20),
    EstadoCivil VARCHAR(20), Renda SMALLMONEY,
    Versao TIMESTAMP)

Logo em seguida, vamos popular a tabela com apenas um registro. Ele é o suficiente para que possamos trabalhar a situação que descrevi.

INSERT INTO tblClientes (Codigo, Nome, EstadoCivil, Renda)
VALUES (1, ‘Cliente 1’, ‘Solteiro’, 2000.00)

Para deixar um pouco mais profissional, serão criadas duas SPs. Uma para recuperar o registro e outra para alterá-lo. O código pode parecer um pouco estranho, mas as explicações virão até o término desse post.

— Recuperar o Cliente
CREATE PROCEDURE usp_RecuperaCliente
    @Codigo INT,
    @Versao TIMESTAMP OUTPUT
AS
SET
@Versao = (SELECT Versao FROM tblClientes WHERE Codigo = @Codigo)
SELECT
    Codigo, Nome, EstadoCivil, Renda
FROM
    tblClientes
WHERE
    Codigo = @Codigo
GO

— Alterar o Cliente
CREATE PROCEDURE usp_AlteraCliente
    @Codigo INT,
    @Nome VARCHAR(20) = NULL,
    @EstadoCivil VARCHAR(20) = NULL,
    @Renda SMALLMONEY = NULL,
    @Versao TIMESTAMP
AS
UPDATE
tblClientes SET
    Nome = ISNULL(@Nome,Nome),
    EstadoCivil = ISNULL(@EstadoCivil, EstadoCivil),
    Renda = ISNULL(@Renda,Renda)
WHERE
    Codigo = @Codigo AND Versao = @Versao
GO

Agora abra duas janelas no SQL Server Management Studio. Chamarei-as de J1 e J2 respectivamente. Utilizaremos para simular uma concorrência. Inicialmente utilizarei comandos SQL simples sem utilizar as SPs. Na janela J1 coloque o seguinte código:

UPDATE tblClientes SET EstadoCivil = ‘Casado’
WHERE Codigo = 1

Na janela J2 coloque o seguinte código:

UPDATE tblClientes SET Renda = 3.000
WHERE Codigo = 1

Se você executou o código de J1 primeiro, o cliente mudará o status para casado mas terá a renda de R$ 2.000,00. Se você executou o código de J2 primeiro, o cliente mudará a renda para R$ 3.000,00 mas permanecerá com o status civil de solteiro. O ideal seria que uma das janela (J1 ou J2) fosse avisada de alguma forma que não pode ou não deve prosseguir com a atualização. Vejamos como poderemos resolver esse impasse usando as procedures criadas. Tanto em J1 quanto em J2 execute o seguinte código:

DECLARE @VersaoRecuperada TIMESTAMP
EXEC usp_RecuperaCliente @Codigo = 1, @Versao = @VersaoRecuperada OUTPUT
SELECT @VersaoRecuperada

O resultado em ambas as janelas é exatamente o mesmo. A procedure recupera os dados do Cliente 1 e retorna como parâmetro de saída a "versão" atual do registro. Para verificarmos qual é a versão exibimos o conteúdo da variável @VersaoRecuperada

No próximo passo simularemos o teste de atualização. Cole o código abaixo na janela J1 para realizar a alteração do estado civil do cliente 1 mas ainda não execute.

— Recuperar os dados de Cliente
DECLARE @VersaoRecuperada TIMESTAMP
EXEC usp_RecuperaCliente @Codigo = 1, @Versao = @VersaoRecuperada
OUTPUT

— Verificar o valor do campo versão do Cliente 1
SELECT @VersaoRecuperada

— Aguarda 5 segundos para simular o delay de preenchimento do campo
WAITFOR DELAY ’00:00:05′

— Atualizar o Status do Cliente 1
EXEC usp_AlteraCliente @EstadoCivil = ‘Casado’,
@Codigo = 1, @Versao = @VersaoRecuperada

— Verificar os dados do Cliente 1
SELECT * FROM tblClientes WHERE Codigo = 1

Na janela 2, cole o código abaixo para realizar a alteração da renda do cliente 1 mas ainda não execute.

— Recuperar os dados de Cliente
DECLARE @VersaoRecuperada TIMESTAMP
EXEC usp_RecuperaCliente @Codigo = 1, @Versao = @VersaoRecuperada OUTPUT

— Verificar o valor do campo versão do Cliente 1
SELECT @VersaoRecuperada

— Aguarda 5 segundos para o delay de preenchimento do campo
WAITFOR DELAY ’00:00:05′

— Atualizar o Status do Cliente 1
EXEC usp_AlteraCliente @Renda = 3.000,
@Codigo = 1, @Versao = @VersaoRecuperada

— Verificar os dados do Cliente 1
SELECT * FROM tblClientes WHERE Codigo = 1

Execute o código da janela J1 e antes que o mesmo finalize execute o código da janela J2. Aguarde a finalização e observe os resultados. A alteração feita em J1 foi realizada enquanto a alteração feita em J2 não foi realizada. O que ocorreu foi o seguinte:

  1. J1 e J2 recuperaram o mesmo registro com o mesmo TIMESTAMP
  2. J1 atualizou o registro com base no código 1 e no TIMESTAMP recuperado. Como o registro não havia sido atualizado ainda, o TIMESTAMP era o mesmo que o recuperado. Após a atualização de J1, o TIMESTAMP foi alterado para um novo valor.
  3. J2 tentou atualizar o registro com base no código 1 no TIMESTAMP recuperado. Como J1 fez a atualização primeiro e mudou o TIMESTAMP, J2 não localizou o registro. Havia o registro com o código 1, mas com o outro TIMESTAMP e portanto não fez a atualização.

Isso mostra como lidar com problemas de concorrência em um nível lógico sem utilizar os recursos de bloqueio do banco de dados. Se essa implementação não fosse feita. J1 faria a atualização e teria a percepção de que o cliente 1 é casado com renda de R$ 2.000,00. Quando J2 fizesse o segundo UPDATE, iria aumentar a renda para R$ 3.000,00 com a percepção de que o cliente continuaria solteiro mas ele viria casado. Com o mecanismo de controle de concorrência baseado em TIMESTAMP podemos evitar esse problema.

Isso no entanto, leva a um inconveniente. J2 não saberá se não fez a atualização porque alguém atualizou o registro primeiro, ou se o mesmo foi excluído por exemplo. Para evitar esse inconveniente, é preciso alterar a SP para essa situação em particular.

ALTER PROCEDURE usp_AlteraCliente
    @Codigo INT,
    @Nome VARCHAR(20) = NULL,
    @EstadoCivil VARCHAR(20) = NULL,
    @Renda SMALLMONEY = NULL,
    @Versao TIMESTAMP
AS
UPDATE
tblClientes SET
    Nome = ISNULL(@Nome,Nome),
    EstadoCivil = ISNULL(@EstadoCivil, EstadoCivil),
    Renda = ISNULL(@Renda,Renda)
WHERE
    Codigo = @Codigo AND Versao = @Versao
IF (@@rowcount = 0) AND EXISTS (SELECT * FROM tblClientes WHERE Codigo = @Codigo)
    RAISERROR(‘O registro foi atualizado previamente. Tente novamente’,16,1)

Dessa forma, se nenhum registro for atualizado mas houver um cliente com o código especificado, um erro será retornado. Se os códigos de atualização forem executados após a alteração da SP, J2 receberá uma mensagem dizendo que em outras palavras, o registro que foi recuperado não exatamente o presente no banco de dados e que uma nova tentativa deve ser feita. Evidente que o tratamento de erro deve ser adequado para que vários usuários não tentem atualizar e receber a mesma mensagem indefinidamente.

Outra possibilidade é que caso a mensagem apareça, J2 seria perguntado se deseja efetuar as alterações mesmo com a mensagem. Dessa forma as alterações de J2 não seriam perdidas, mas ele seria notificado das possíveis conseqüências. Nessa situação específica isso pode ser desejável já que as atualizações de J1 e J2 não são conflitantes, mas nem sempre esse é o cenário mais comum.

O mecanismo apresentado é clássico na resolução de problemas de concorrência e pega emprestado alguns conceitos da concorrência otimista. Fiz todo o código baseado em TSQL, mas no mundo real, certamente o TIMESTAMP será recuperado para a camada do cliente e resubmetido após a alteração (por isso o DELAY de 5s).

Até a próxima pessoal,

[ ]s,

Gustavo