Arquivo do autor:Gustavo Maia Aguiar

Outra alternativa para concatenar registros no SQL Server

Bom Dia Pessoal,

Esses dias tenho estudado bastante o SQL Server 2008 e as New Features já que comecei a maratona de certificações nesse produto. Hoje deparei-me com o tão repetido slogan "Beyond Relational" que aborda os novos tipos de dados. Para começar foi abordado o tipo de dados XML para trabalhar com esse padrão mais nativamente. Eu gosto muito do tema XML e das capacidades implementadas no SQL Server. Tive boas experiências com o 2005 e o 2008 só veio a somar com algumas novas funcionalidades. Aproveitei para verificar como utilizá-lo para resolver mais um problema comum que é a concatenação de registros.

Em alguns artigos passados eu já havia falado sobre diversas possibilidades de concatenar registros com CLR, cursores, subqueries, etc em "SQL Server 2005 T-SQL Tips: Concatenando e Agrupando" e "Concatenando registros". No assunto XML, demonstrei duas formas de fazer isso. Pensei eu que com tantas maneiras de fazer isso não haveria muito mais o que abordar, mas ao revisar o uso do XML no SQL Server 2008, acabei achando uma mais uma maneira de fazer essa concatenação. Vejamos um exemplo prático:

– Cria uma tabela de Empresas
CREATE TABLE Empresas (EmpresaID INT, EmpresaNome VARCHAR(70))

– Cria uma tabela de Ativos
CREATE TABLE Ativos (AtivoID INT, EmpresaID INT, AtivoNome CHAR(5))

– Insere alguns Setores
INSERT INTO Empresas VALUES (1,‘Petrobrás’)
INSERT INTO Empresas VALUES (2,‘Banco do Brasil’)
INSERT INTO Empresas VALUES (3,‘Itaú Holding’)
INSERT INTO Empresas VALUES (4,‘Banco Itaú’)

– Insere alguns Registros
INSERT INTO Ativos VALUES (1,1,‘PETR3′)
INSERT INTO Ativos VALUES (2,1,‘PETR4′)
INSERT INTO Ativos VALUES (3,2,‘BBAS3′)
INSERT INTO Ativos VALUES (4,3,‘ITSA2′)
INSERT INTO Ativos VALUES (5,3,‘ITSA3′)
INSERT INTO Ativos VALUES (6,3,‘ITSA4′)
INSERT INTO Ativos VALUES (7,4,‘ITUB3′)
INSERT INTO Ativos VALUES (8,4,‘ITUB4′)

– Faz um JOIN das duas tabelas
SELECT EmpresaNome As Empresa, AtivoNome As Ativo
FROM Empresas As E
INNER JOIN Ativos As A ON E.EmpresaID = A.EmpresaID

O uso do JOIN produz o seguinte resultado:

Empresa Ativo
Petrobrás PETR3
Petrobrás PETR4
Banco do Brasil BBAS3
Itaú Holding ITSA2
Itaú Holding ITSA3
Itaú Holding ITSA4
Banco Itaú ITUB3
Banco Itaú ITUB4

Mas e o resultado desejado fosse diferente ?

Empresa Ativos
Petrobrás PETR3 ; PETR4 ;
Banco do Brasil BBAS3 ;
Itaú Holding ITSA2 ; ITSA3 ; ITSA4 ;
Banco Itaú ITUB3 ; ITUB4 ;

Como eu havia falado de XML, uma das formas de fazer isso é utilizá-lo de forma aninhada e omitir a cláusula TYPE para que o XML não seja tipado. Ex:

SELECT EmpresaNome As Empresa,
    (SELECT AtivoNome As Ativo FROM Ativos As A
    WHERE E.EmpresaID = A.EmpresaID
    FOR XML RAW(‘A’)) As Ativos
FROM Empresas As E

O resultado é exibido abaixo:

Empresa Ativos
Petrobrás <A Ativo="PETR3"/><A Ativo="PETR4"/>
Banco do Brasil <A Ativo="BBAS3"/>
Itaú Holding <A Ativo="ITSA2"/><A Ativo="ITSA3"/><A Ativo="ITSA4"/>
Banco Itaú <A Ativo="ITUB3"/><A Ativo="ITUB4"/>

Como os dados em XML estão em um formato desde visto que a cláusula TYPE foi emitida, basta utilizar dois REPLACEs e efetuar a substituição. Ex:

;WITH Titulos As (
SELECT EmpresaNome As Empresa,
    (SELECT AtivoNome As Ativo FROM Ativos As A
    WHERE E.EmpresaID = A.EmpresaID
    FOR XML RAW(‘A’)) As Ativos
FROM Empresas As E)

SELECT Empresa,
    REPLACE(REPLACE(Ativos,‘<A Ativo="’,),‘"/>’,‘ ; ‘) As Ativos
FROM Titulos

Com o uso da CTE para melhorar a legibilidade do código e com os dois REPLACEs é possível chegar no resultado esperado:

Empresa Ativos
Petrobrás PETR3 ; PETR4 ;
Banco do Brasil BBAS3 ;
Itaú Holding ITSA2 ; ITSA3 ; ITSA4 ;
Banco Itaú ITUB3 ; ITUB4 ;

Eu já havia abordado essa construção em artigos anteriores. Há ainda uma terceira alternativa com o uso do XML para obter esse mesmo resultado. O SQL Server 2008 possui a função data() para trabalhar com XML. Essa função simplesmente converte nós XML para dados estruturados em um único nó. Se considerarmos cada ativo como um nó é possível simplesmente enumerá-los como texto através dessa função. Ex:

;WITH Titulos As (
SELECT EmpresaNome As Empresa,
    (SELECT AtivoNome As [data()] FROM Ativos As A
    WHERE E.EmpresaID = A.EmpresaID
    FOR XML PATH()) As Ativos
FROM Empresas As E)

SELECT Empresa, Ativos FROM Titulos

A utilização da função data(), enumerou todos os ativos de uma empresa em um único nó. Como a expressão XPath, exibiu o nó vazio (‘’), o resultado foi apenas a apresentação dos ativos separados por um espaço.

Empresa Ativos
Petrobrás PETR3 PETR4
Banco do Brasil BBAS3
Itaú Holding ITSA2 ITSA3 ITSA4
Banco Itaú ITUB3 ITUB4

Com a utilização de um REPLACE, basta substituir os espaços pelo delimitador desejado. Ex:

;WITH Titulos As (
SELECT EmpresaNome As Empresa,
    (SELECT AtivoNome As [data()] FROM Ativos As A
    WHERE E.EmpresaID = A.EmpresaID
    FOR XML PATH()) As Ativos
FROM Empresas As E)

SELECT Empresa, REPLACE(Ativos,‘ ‘,‘ ; ‘) + ‘ ; ‘ As Ativos FROM Titulos

O resultado obtido é exibido abaixo:

Empresa Ativos
Petrobrás PETR3 ; PETR4 ;
Banco do Brasil BBAS3 ;
Itaú Holding ITSA2 ; ITSA3 ; ITSA4 ;
Banco Itaú ITUB3 ; ITUB4 ;

O uso da função data() mostrou como utilizar o XML para concatenar registros, mas essa é uma alternativa um pouco restrita. Como ela pressupõe o uso de um espaço para fazer a separação, caso haja algum ativo com o espaço no nome, o uso do REPLACE não saberá distinguir o espaço da função data() do espaço no nome do ativo fazendo assim uma delimitação equivocada. Para essas situações, o uso das técnicas utilizadas nos artigos anteriores é mais eficaz.

[ ]s,

Gustavo

A armadilha do Backup Diferencial – O que fazer para evitá-la ?

Boa Tarde Pessoal,

Essa semana estou finalizando uma turma do curso 2780 que trata de administração e manutenção do SQL Server 2005. Como sempre digo aos alunos, um dos capítulos mais importantes é o de Disaster Recovery que trata especificamente sobre backup. As possibilidades de backup disponíveis no SQL Server 2005 são muito interessantes. Além dos tradicionais FULL, Diferencial e Log, temos ainda os backups de Filegroup, Tail Log, Partial, etc que realmente ampliam bastante as estratégias de recuperação de dados em cenários de desastre.

Durante a aula, como é de praxe, discorro sobre o funcionamento do backup full, do backup diferencial e dos backup de log além de alguns cenários típicos. O uso do backup diferencial em conjunto com o backup de log é uma excelente combinação por permitir uma estratégia autocontigente, já que na perda de algum log, é possível avançar com o diferencial e na hipótese da perda do diferencial é possível avançar com os logs. Entretanto, especificamente no caso do backup diferencial, existem algumas armadilhas para quem utiliza apenas a estratégia FULL + Diferencial que nem sempre está tão aparente. Hoje falarei de um detalhezinho no diferencial que se não observado pode levar a impossibilidade de utilizá-lo em um processo de restauração

Como funciona o backup diferencial

Não vou entrar nos detalhes internos do backup diferencial e de como o SQL Server faz para saber o que copiar para o diferencial. O que podemos dizer é que o backup diferencial é simplesmente uma cópia de tudo o que mudou desde o último backup full realizado. Assim sendo, após a realização de um backup full, todas as páginas que tiverem sido modificadas irão compor o backup diferencial. No momento de restauração, um backup full é restaurado e logo em seguida aplica-se o diferencial para sobrescrever as páginas por uma versão mais recente. Vejamos alguns exemplos:

– Cria um banco de dados
CREATE DATABASE TesteDiferencial

– Muda o contexto do banco de dados
USE TesteDiferencial

– Cria uma tabela de Numeros
CREATE TABLE Numeros (Num INT NOT NULL)

– Insere alguns registros
INSERT INTO Numeros VALUES (01)
INSERT INTO Numeros VALUES (02)
INSERT INTO Numeros VALUES (03)
INSERT INTO Numeros VALUES (04)

– Efetua um backup full
BACKUP DATABASE TesteDiferencial TO DISK = ‘C:\TesteDiferencialF01.BAK’

– Insere mais alguns números
INSERT INTO Numeros VALUES (05)
INSERT INTO Numeros VALUES (06)
INSERT INTO Numeros VALUES (07)
INSERT INTO Numeros VALUES (08)

– Efetua um primeiro backup diferencial
BACKUP DATABASE TesteDiferencial TO DISK = ‘C:\TesteDiferencialD01.DIF’
WITH DIFFERENTIAL

– Insere mais alguns números
INSERT INTO Numeros VALUES (09)
INSERT INTO Numeros VALUES (10)
INSERT INTO Numeros VALUES (11)
INSERT INTO Numeros VALUES (12)

– Efetua um segundo backup diferencial
BACKUP DATABASE TesteDiferencial TO DISK = ‘C:\TesteDiferencialD02.DIF’
WITH DIFFERENTIAL

Como o backup diferencial corresponde a todas as páginas alteradas desde o último backup full, temos as seguintes distribuições em relação aos registros:

Backup Conteúdo (Números)
Full01 01, 02, 03, 04
Dif01 05, 06, 07, 08
Dif02 05, 06, 07, 08, 09, 10, 11, 12

Alguns podem perguntar se o segundo diferencial não deveria ter somente os números de 9 a 12. Lembre-se que um backup diferencial tem todos os dados desde o último backup full (e não desde o último backup diferencial). Como o backup full continha os números de 1 a 4 e os números de 5 a 12 foram inseridos entre o último backup full e o segundo diferencial, esse tem portanto os números de 5 a 12 e não de 9 a 12.

É possível confirmar as sequências através dos registros de LSN retornados pela instrução RESTORE HEADER ONLY. Ex:

– Verifica as marcas de LSN do Backup FULL
RESTORE HEADERONLY FROM DISK = ‘C:\TesteDiferencialF01.BAK’

– Verifica as marcas de LSN do primeiro Backup Diferencial
RESTORE HEADERONLY FROM DISK = ‘C:\TesteDiferencialD01.DIF’

– Verifica as marcas de LSN do segundo Backup Diferencial
RESTORE HEADERONLY FROM DISK = ‘C:\TesteDiferencialD02.DIF’

Podemos ver o quadro esquemático conforme abaixo:

Backup FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
Full01 19000000007600173 19000000014900001 19000000007600173 0
Dif01 19000000016100037 19000000017800001 19000000016100037 19000000007600173
Dif02 19000000018200004 19000000018500001 19000000018200004 19000000007600173

Não vou entrar no mérito das colunas FirstLSN, LastLSN e CheckpointLSN. A coluna DatabaseBackupLSN do backup full mostra o valor de 0 que significa que nenhum backup havia sido feito o que é bem natural, pois, trata-se do primeiro backup realizado no banco de dados. Se for observado a coluna CheckpointLSN do backupfull foi 19000000007600173, ou seja, no LSN 19000000007600173 foi marcado no log de transações que o backup full foi realizado. A coluna DatabaseBackupLSN dos dois backups diferenciais aponta exatamente para esse LSN, o que significa que os dois backups diferenciais são referentes ao primeiro backup full e por isso é possível restaurar o backup full e aplicar qualquer um dos dois diferenciais. A seguir, será mostrado como essas marcas se comportam após a realização de outros backups e inserções de dados.

– Insere mais alguns números
INSERT INTO Numeros VALUES (13)
INSERT INTO Numeros VALUES (14)
INSERT INTO Numeros VALUES (15)
INSERT INTO Numeros VALUES (16)

– Efetua um segundo backup full
BACKUP DATABASE TesteDiferencial TO DISK = ‘C:\TesteDiferencialF02.BAK’

– Insere mais alguns números
INSERT INTO Numeros VALUES (17)
INSERT INTO Numeros VALUES (18)
INSERT INTO Numeros VALUES (19)
INSERT INTO Numeros VALUES (20)

– Efetua um terceiro backup diferencial
BACKUP DATABASE TesteDiferencial TO DISK = ‘C:\TesteDiferencialD03.DIF’
WITH DIFFERENTIAL

As marcas de LSN dos backups podem ser obtidas conforme os comandos abaixo:

– Verifica as marcas de LSN do segundo Backup FULL
RESTORE HEADERONLY FROM DISK = ‘C:\TesteDiferencialF02.BAK’

– Verifica as marcas de LSN do terceiro Backup Diferencial
RESTORE HEADERONLY FROM DISK = ‘C:\TesteDiferencialD03.DIF’

As marcas são demonstradas na tabela abaixo:

Backup FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
Full02 19000000019100042 19000000020800001 19000000019100042 19000000007600173
Dif03 19000000021900037 19000000023600001 19000000021900037 19000000019100042

Se juntarmos todas as marcas teremos uma nova sequência:

Backup FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
Full01 19000000007600173 19000000014900001 19000000007600173 0
Dif01 19000000016100037 19000000017800001 19000000016100037 19000000007600173
Dif02 19000000018200004 19000000018500001 19000000018200004 19000000007600173
Full02 19000000019100042 19000000020800001 19000000019100042 19000000007600173
Dif03 19000000021900037 19000000023600001 19000000021900037 19000000019100042

Os dois primeiros backups diferenciais têm a coluna DatabaseBackupLSN apontando para o valor 19000000007600173 e como esse é o valor de Checkpoint do primeiro backup full, essa igualdade é o que permite afirmar que os dois backups diferenciais podem ser restaurados em conjunto com o primeiro backup full. O mesmo é válido para o terceiro backup diferencial e o segundo backup full já que ambos referenciam o LSN 19000000019100042. Essas referências também permitem afirmar que o terceiro backup diferencial não pode ser restaurado em conjunto com o primeiro backup full. O script abaixo demonstra que isso não é possível.

– Muda o contexto para o MASTER
USE MASTER

– Elimina o banco Teste
DROP DATABASE TesteDiferencial

– Restaura o primeiro backup full
RESTORE DATABASE TesteDiferencial
FROM DISK = ‘C:\TesteDiferencialF01.BAK’
WITH NORECOVERY

– Verifica o LSN dos arquivos
SELECT name, differential_base_lsn FROM sys.master_files
WHERE database_id = DB_ID(‘TesteDiferencial’) AND data_space_id > 0

O comando de SELECT mostra que o arquivo de dados está parado no LSN 19000000007600173 e que necessita prosseguir a partir desse LSN. A seguir, é tentado a restauração do terceiro backup diferencial.

– Restaura o terceiro backup diferencial
RESTORE DATABASE TesteDiferencial
FROM DISK = ‘C:\TesteDiferencialD03.DIF’
WITH RECOVERY

Msg 3136, Level 16, State 1, Line 2
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Embora a mensagem não diga todos os detalhes, não é difícil descobrir qual é o erro com a tabela de marcas.

Backup FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
Full01 19000000007600173 19000000020800001 19000000007600173 0
Dif03 19000000021900037 19000000023600001 19000000021900037 19000000019100042

O primeiro backup full termina no LSN 19000000020800001 e o terceiro diferencial inicia no LSN 19000000021900037. Nesse caso há um GAP entre os LSNs “cobertos” e não faria sentido mesmo que a restauração fosse possível. A coluna DatabaseBackupLSN confirma essa impossibilidade já que o valor dessa coluna para o terceiro diferencial é 19000000019100042 que não bate com o Checkpoint do primeiro backup full que é 19000000007600173.

Toda essa demonstração foi para dizer apenas que o backup diferencial dependerá sempre do último backup full realizado antes do backup diferencial. Caso esse backup full seja perdido, não será possível aproveitar o backup diferencial em um processo de restauração. Isso ficou evidente, pois, caso o segundo backup full fosse indisponibilizado, não seria possível utilizar o terceiro backup diferencial.

Guardar adequadamente o último backup full realizado antes do backup diferencial é imprescindível para uma estratégia que se baseie em backups diferenciais. Isso foi demonstrado e mesmo com tantas constatações, para alguns isso não passa de algo óbvio e no mínimo uma obrigação. Pode parecer até sem sentido uma possibilidade de não guardar esse backup, mas vejamos algumas situações que podem mudar isso.

Imagine que esses backups tenham sido realizados da seguinte forma:

Backup Descrição Dia de Realização
Full01 Política de backup full semanal Domingo às 23:00
Dif01 Política de backup diferencial diária Segunda-Feira às 18:00
Dif02 Política de backup diferencial diária Terça-Feira às 18:00
Full02 Situação adversa Quarta-Feira às 14:30
Dif03 Política de backup diferencial diária Quarta-Feira às 18:00

A política de backup baseia-se em FULL semanal e diferenciais diários (pode-se incluir alguns backups de log, mas não quis abordá-los). Se tudo funcionar como deve-se ser, um backup full é realizado aos domingos e mais seis diferenciais variando de segunda a sábado. Para que a restauração possa ser realizada com êxito, é necessário restaurar o backup full semanal e aplicar o último diferencial. Esse plano funcionará perfeitamente desde que não haja a presença de situações adversas como a realização de um backup full no meio da semana através de uma situação adversa, ou seja, algo fora da política.

Por que alguém irá realizar um backup full fora da política ? Pode parecer até injustificável, mas essa situação "adversa" pode sim ocorrer. Imagine que na quarta-feira em questão, o diretor de TI solicite um backup full da base para que o mesmo seja disponibilizado para uma consultoria internacional realizar um projeto de Data Mining ? Ou ainda que a equipe de desenvolvimento faça seus projetos com base em um backup da produção e necessitaram de um na quarta-feira ? Ou ainda que para resolver um problema de produção foi necessário um backup full da base em outro ambiente ? Ou que algum outro DBA efetuou um backup da produção antes de aplicar um script potencialmente duvidoso ?

Em todas essas situações, a política não estará comprometida, se esse backup full oriundo de uma situação adversa for guardado junto com os demais. O grande risco é que na maioria dessas situações isso dificilmente acontece. Ninguém se preocupará em guardar um backup full, pois, confiar-se-á no backup full da política semanal. O grande problema é que aquele backup full não poderá ser utilizado com alguns diferenciais já que haverá um full mais recente. Muito provavelmente isso só será percebido quando a necessidade de restauração vier à tona, mas nesse caso ele não funcionará. Essa é a armadilha que o backup diferencial prepara se não for corretamente utilizado.

Como evitar a armadilha do backup diferencial ?

Se o backup diferencial for utilizado para versões anteriores ao SQL Server 2005 realmente não existe saída senão guardar bem os backups fora da política. No caso do SQL Server 2005 e superiores, existe a opção pouco conhecida chamada COPY_ONLY que é perfeita para situações adversas que exijam um backup full eventual. Vejamos como ela funciona.

– Criação do banco de dados
CREATE DATABASE TesteDifCopyOnly

– Muda o contexto do banco de dados
USE TesteDifCopyOnly

– Cria uma tabela
CREATE TABLE Letras (Letra CHAR(1))

– Insere alguns valores
INSERT INTO Letras VALUES (‘A’)
INSERT INTO Letras VALUES (‘B’)
INSERT INTO Letras VALUES (‘C’)

– Efetua um backup full
BACKUP DATABASE TesteDifCopyOnly TO DISK = ‘C:\TesteDifCopyOnlyF01.BAK’

– Insere mais valores
INSERT INTO Letras VALUES (‘D’)
INSERT INTO Letras VALUES (‘E’)
INSERT INTO Letras VALUES (‘F’)

– Faz um novo backup full com a opção COPY_ONLY
BACKUP DATABASE TesteDifCopyOnly TO DISK = ‘C:\TesteDifCopyOnlyF02.BAK’
WITH COPY_ONLY

– Insere mais valores
INSERT INTO Letras VALUES (‘G’)
INSERT INTO Letras VALUES (‘H’)
INSERT INTO Letras VALUES (‘I’)

– Faz um backup diferencial
BACKUP DATABASE TesteDifCopyOnly TO DISK = ‘C:\TesteDifCopyOnlyD01.DIF’
WITH DIFFERENTIAL

Os comandos abaixo demonstram as marcas de LSN desses backups.

Backup FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
Full01 20000000022800193 20000000030900001 20000000022800193 0
Full02 20000000032000037 20000000033700001 20000000032000037 20000000022800193
Dif01 20000000034000004 20000000034300001 20000000034000004 20000000022800193

O primeiro backup full foi gerado no LSN 20000000022800193 e embora tenha havido um segundo backup full entre o primeiro backup full e o primeiro diferencial, a opção COPY_ONLY não influenciou a referência do backup diferencial. Esse ainda referencia o primeiro backup full mesmo havendo um segundo backup full. A opção COPY_ONLY fez o backup "frio" e para o backup diferencial, o segundo backup full nunca existiu não alterando as referências. Assim sendo, o primeiro backup full possui as letras A,B e C e o backup diferencial possui as letras D, E, F, G, H e I e o segundo backup full possui as letras A, B, C, D, E e F. Se essa opção tivesse sido utilizada no exemplo anterior, a política de backup não seria afetada e o DIF03 poderia ser utilizado sem maiores problemas. Essa é uma forma de "escapar" da armadilha do backup diferencial. O script abaixo mostra que é plenamente possível utilizar essa estratégia:

– Muda o contexto de banco de dados
USE MASTER

– Exclui o banco de dados
DROP DATABASE TesteDifCopyOnly

– Restaura o primeiro backup full
RESTORE DATABASE TesteDifCopyOnly FROM DISK = ‘C:\TesteDifCopyOnlyF01.BAK’
WITH NORECOVERY

– Restaura o primeiro diferencial
RESTORE DATABASE TesteDifCopyOnly FROM DISK = ‘C:\TesteDifCopyOnlyD01.DIF’

– Verifica as letras cadastradas
SELECT Letra FROM TesteDifCopyOnly.dbo.Letras

A recuperação das letras de A a I mostra que a estratégia de restauração foi bem sucedida.

Algumas observações sobre a opção COPY_ONLY

O uso da opção COPY_ONLY e o backup "frio", ou seja, por debaixo dos panos pode parecer interessante, mas antes que alguém pense em utilizá-la como padrão em todos os backups, é necessário alertar para alguns efeitos colaterais. O segundo backup full pode ser restaurado sem problemas, mas não poderá ser utilizado para iniciar um processo mais completo de restauração. Ex:

– Muda o contexto de banco de dados
USE MASTER

– Exclui o banco de dados
DROP DATABASE TesteDifCopyOnly

– Restaura o segundo backup full
RESTORE DATABASE TesteDifCopyOnly FROM DISK = ‘C:\TesteDifCopyOnlyF02.BAK’
WITH NORECOVERY

– Restaura o primeiro diferencial
RESTORE DATABASE TesteDifCopyOnly FROM DISK = ‘C:\TesteDifCopyOnlyD01.DIF’

Msg 3136, Level 16, State 1, Line 2
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Como o segundo backup full foi "frio", tecnicamente ele nunca existiu. Se ele nunca existiu, ele não pode ser utilizado como referência para o backup diferencial e por isso o processo de restauração não é concluído. É possível utilizar o backup full com a opção COPY_ONLY para restaurar um novo banco, mas sem aplicações de log posteriores. Mesmo com essa limitação, ela atenderia perfeitamente as demais situações adversas, pois, o que interessava para essas situações era apenas o backup full.

Alguns poderiam dizer que essa não é preocupação tão digna, pois, uma política bem planejada não iria contar apenas com backups diferenciais, mas provavelmente com logs e a realização de backups full intermediários não atrapalha a sequência de logs. Sim, de fato, isso é verdade, mas penso que as boas estratégias são aquelas que tem mais de uma possibilidade de restauração. Ter os backups diferenciais em conjunto com os backups de log permite dois caminhos de restauração, pois, na falha de algum log, o diferencial pode ser utilizado e se algum diferencial falhar, os logs podem ser utilizados. Isso só é realmente verdadeiro se as situações adversas forem contornadas com o uso do COPY_ONLY. Do contrário, já estaremos eliminando de saída a efetividade dos backups diferenciais e reduzindo as possibilidades de sucesso nos momentos em que os backups forem realmente necessários.

[ ]s,

Gustavo

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

Bom Dia Pessoal,

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

Uma situação de negócio

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

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

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

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

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

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

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

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

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

(1 row(s) affected)

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

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

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

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

– Exclui os registros
DELETE FROM Clientes

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

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

(1 row(s) affected)

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

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

Razões para bloquear registros nulos repetidos

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

Razões para não bloquear registros nulos repetidos

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

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

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

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

A indexação e a Nulabilidade

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

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

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

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

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

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

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

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

– Não execute esse script. Ele é apenas educacional
– Cria tabelas adicionais (relacionamentos não inclusos)

CREATE TABLE ClientesRG (
    ClienteID INT NOT NULL,
    ClienteRG VARCHAR(15) NOT NULL)

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

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

– Não executar esse script. Ele é apenas educacional
– Cria uma tabela auxiliar para armazenar o RG

CREATE TABLE ClientesRG (
    ClienteID INT NOT NULL,
    ClienteRG VARCHAR(15) NOT NULL,
    OrgaoExpedidor VARCHAR(7) NOT NULL,
    DataExpedicao SMALLDATETIME NOT NULL)

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

Solução Alternativa 2 – O uso de triggers

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

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

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

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

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

END
GO

– Exclui os registros
DELETE FROM Clientes

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

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

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

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

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

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

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

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

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

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

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

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

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

– Exclui os registros
DELETE FROM Clientes

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

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

Podemos visualizar o resultado da consulta conforme a tabela abaixo:

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

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

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

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUCClienteRG’.

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

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

The statement has been terminated.

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

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

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

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

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

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

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

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

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

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

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

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

– Exclui os registros
DELETE FROM Clientes

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

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

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.vCPF’ with unique index ‘IXUCClienteCPF’.

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

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

The statement has been terminated.

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

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

DROP VIEW vRG
DROP VIEW vCPF

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

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

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

– Exclui os registros
DELETE FROM Clientes

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

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

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUClienteRG’.

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

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

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

[ ]s,

Gustavo

Recorde № 1 – 427 Visitas ao blog em um único dia

Boa Noite Pessoal,

Como de costume, sempre tento visualizar diariamente o número de acessos ao blog. É uma forma saber se o blog está caminhando em uma direção boa ou ruim. O número de visitas é um ótimo (e talvez um dos poucos) indicadores que possuo para saber se o blog está agradando ou não quem o visita. Normalmente o número de acessos fica entre 100 e 150 acessos durante a segunda e sexta e no fim de semana esse número cai para 10 a 30 acessos nos sábados e domingos.

Todo mês eu supero o número de visitas anteriores e lembro-me de que quando cheguei a 100 visitas em um único dia já foi um reconhecimento importante, pois, embora esse número seja insignificante perto do número de acessos de sites mais populares, ter 100 visitas em um blog de SQL Server do spaces é gratificante. Hoje para a minha surpresa, após fazer o acesso de rotina, tive a surpresa de visualizar 427 acessos em um único dia e totalizar 659 na semana (e ainda estamos na terça-feira). É realmente um crescimento bem surpreendente perto das médias que normalmente eu apuro. Não sei exatamente a que se deve esse aumento, pois, o Live não fornece tantas informações assim.

Fiz uma pequena apuração sobre os artigos mais lidos e constatei que os artigos referente a criação da tabela de números (partes I e II), os cáculos com data e hora (partes I, II, III e IV), os cálculos financeiros, consultas SQL e o mercado de ações (partes I, II e III) e os artigos de importação de XML (partes I, II, III e IV) estão entre os fortemente consultados no blog. Algumas categorias como “Mitos do SQL Server” e “Piores Práticas” também tem tido acessos um pouco acima do normal. Vi também que o Google, o fórum de SQL Server do MSDN e do TechNet e as comunidades SQL Server Brasil e SQL Brasil têm contribuído muito para as visitas. Algumas matérias como “Mitos do SQL Server – Será que COUNT(1) ou COUNT(‘X’) são mais performáticos que COUNT(*) ?” e “Como evitar contratar profissionais DBAs "Fake" e despreparados” renderam muitos comentários.

Eu iniciei o blog em 13 de julho de 2008 e de lá pra cá são 75 postagens (tá dando pra manter a média semanal (rs)). A cada artigo tento trazer a melhor explicação e qualidades possíveis para o assunto abordado. Muitas vezes não é possível esgotar o mesmo, mas a idéia é fazer de cada artigo uma excelente referência sobre um tema em particular. Ultimamente projetos, aulas, trabalho, estudos, família, etc tem tirado um pouco minha atenção dos fóruns, do blog e da ajuda às comunidades de SQL Server, mas realmente é bastante gratificante ter esse feedback. Espero em breve estar postando um recorde №2 (ajudem aê (rs)).

A todos os que visitam, comentam, indicam e contribuem com o blog eu gostaria de dar os meus sinceros agradecimentos.
Continuarei tentando fazer o melhor trabalho possível.

[ ]s,

Gustavo

Mundo .NET Ed. № 16 – Recursividade, Hierarquias, CTEs e Consultas SQL aplicadas ao Marketing de Rede

Boa Tarde Pessoal,

Esse mês fui convidado pela revista Mundo .NET para escrever um artigo referente ao SQL Server. Há sempre algo a dizer sobre SQL Server, mas dentre uma gama de possibilidades, resolvi escolher um tema de negócio bem atual e que representa um desafio de modelagem sobre tudo para iniciantes. Na edição № 16 da revista, escrevi um artigo sobre a implementação de um modelo de dados de uma empresa que trabalha com Marketing de rede. O artigo detalha não somente a elaboração do modelo em si, mas principalmente, como resolver as dificuldades natas na elaboração de consultas que aparecem em modelos similares.

Normalmente uma empresa que utiliza a estratégia de Marketing não faz uma propaganda maciça de seus produtos. Ao contrário, a empresa revende grandes quantidades de produtos a alguns poucos distribuidores que se encarregam de repassá-los ao mercado seja através de uma venda direta a um consumidor ou de forma indireta utilizando outros colaboradores para efetuar as vendas.

Uma das principais características do Marketing de rede é a presença da indicação dos membros participantes. A empresa repassa os produtos a seus distribuidores que podem ter uma rede de colabores que por sua vez podem contar com outras pessoas para efetuar as vendas. Essa pirâmide de colaboradores faz com que conceitualmente exista uma relação hierárquica. Um colaborador pode vender diretamente, mas também pode recrutar outros colaboradores para realizarem as vendas e esses últimos também podem ter sua rede de colaboradores. Não importa se um colaborador faz a venda direta ou se a faz indireta através de sua rede particular de colaboradores. No final das contas todos são colaboradores. Isso incorre no fato de que um colaborador pode referenciar-se a outro colaborador.

O detalhe da relação hierárquica não é exclusivo do marketing de rede. Os modelos de dados estão repletos de situações desse tipo como a relação empregado – supervisor, centro de custo filho – centro de custo pai, contribuinte – dependente, etc. A implementação de relacionamentos recursivos (self join) pode ser trabalhosa, mas existem boas alternativas para lidar com ele. Nesse artigo eu demonstro como fazê-lo através das CTEs já que elas estão presentes tanto nas versões 2005 como 2008 do SQL Server.

Aos que adquirirem a revista, espero que apreciem o material. Aos que não conhecem a revista Mundo .NET abaixo o link da revista.

Revista Mundo.NET
http://www.mundodotnet.com.br/

Para quem deseja conhecer outras alternativas para lidar com relacionamentos hierárquicos seguem algumas referências:

Modelagem de Dados: Hierarquias – Parte 1
http://www.plugmasters.com.br/sys/materias/586/1/Modelagem-de-Dados%3A-Hierarquias—Parte-1

Modelagem de Dados: Hierarquias – Parte 2
http://www.plugmasters.com.br/sys/materias/587/1/Modelagem-de-Dados%3A-Hierarquias—Parte-2

Modelagem de Dados: Hierarquias – Parte 3
http://www.plugmasters.com.br/sys/materias/680/1/Modelagem-de-Dados%3A-Hierarquias—Parte-3

Modelagem de Dados: Hierarquias – Parte 4
http://www.plugmasters.com.br/sys/materias/694/1/Modelagem-de-Dados%3A-Hierarquias—Parte-4

[ ]s,

Gustavo

Como retornar todas colunas participantes de uma chave primária ?

Boa Noite Pessoal,

Para saldar a média de uma postagem por semana, hoje colocarei uma dica rápida. Essa semana uma das administradoras de dados (AD) em um dos clientes em que trabalho me fez uma pergunta simples “Qual é o comando para retornar as colunas que compõe uma chave primária ?”. De fato é uma pergunta bem simples e quase sempre é respondida de modo visual através do Enterprise Manager ou do SQL Server Management Studio. Mas e via T-SQL ? Como fazer ?

A forma mais fácil de saber qual a coluna (ou combinação de colunas) que representa a chave primária é através da stored procedure sp_pkeys. Ex:

– Muda o contexto para o Adventure Works
USE AdventureWorks

– Lista as colunas da chave primária de Sales.SalesOrderHeader
EXEC sp_pkeys ‘SalesOrderDetail’, ‘Sales’

Normalmente essa é a maneira preferida (acredito até que a AD já conhecia), mas o fato do retorno ser através de uma stored procedure pode trazer alguns inconvenientes como a impossibilidade de retornar todas as colunas que são chaves primárias de todas as tabelas. Uma segunda opção seria utilizar a view Key_Column_Usage do schema Information_Schema.

– Muda o contexto para o Adventure Works
USE AdventureWorks

– Retorna os dados de INFORMATION_SCHEMA.KEY_COLUMN_USAGE
– Apenas as colunas de Sales.SalesOrderDetails

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = ‘Sales’ AND TABLE_NAME = ‘SalesOrderDetail’

Essa abordagem é interessante, mas o problema com a view Key_Column_Usage é que ela retorna também os dados de colunas envolvidas em unique constraints e foreign_keys. Alguns ajustes podem ser feitos para retornar apenas as colunas envolvidas em Primary Keys.

– Muda o contexto para o Adventure Works
USE AdventureWorks

– Retorna os dados de INFORMATION_SCHEMA.KEY_COLUMN_USAGE
– Apenas as colunas de Sales.SalesOrderDetails

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE As K
WHERE TABLE_SCHEMA = ‘Sales’ AND TABLE_NAME = ‘SalesOrderDetail’
AND CONSTRAINT_NAME = (
    SELECT NAME FROM SYSOBJECTS As U
    WHERE
        K.TABLE_NAME = OBJECT_NAME(U.Parent_Obj) AND
        U.XTYPE = ‘PK’)
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

Basta apenas retirar o predicado TABLE_SCHEMA e TABLE_NAME para ter uma lista com todas as colunas com chave primária do banco inteiro. Utilizei tabelas como SYSOBJECTS apenas para manter a compatibilidade com o SQL Server 2000 (era a plataforma que a AD estava utilizando). Para plataformas como o 2005 e o 2008 a solução pode utilizar as novas views de catálogo como a Sys.Indexes, Sys.Index_Columns e Sys.Columns ou combinar a Sys.Indexes com a Information_Schema.Key_Column_Usage. Ex:

– Método 1
SELECT
    OBJECT_SCHEMA_NAME(I.object_id) As Esquema,
    OBJECT_NAME(I.object_id) As Tabela,
    C.Name As Nome, IC.key_ordinal As Posicao
FROM
    sys.indexes As I
    INNER JOIN sys.index_columns As IC ON I.index_id = IC.index_id AND I.object_id = IC.object_id
    INNER JOIN sys.columns As C ON IC.column_id = C.column_id AND IC.object_id = C.object_id
WHERE
    I.is_primary_key = 1 AND OBJECTPROPERTYEX(I.object_id,‘IsUserTable’) = 1
ORDER BY Esquema, Tabela, Posicao

– Método 2
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE As K
WHERE CONSTRAINT_NAME = (
    SELECT FROM FROM sys.indexes As I
    WHERE
        K.TABLE_NAME = OBJECT_NAME(I.object_id) AND
        K.TABLE_SCHEMA = OBJECT_SCHEMA_NAME(I.object_id) AND
        I.is_primary_key = 1)
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

Aqueles que desejarem utilizar a construção do 2000 nas versões posteriores pode ser necessário fazer alguns ajustes por conta do schema caso haja mais uma mesma tabela em esquemas diferentes.

[ ]s,

Gustavo

Mitos do SQL Server – Será que a opção IGNORE_DUP_KEY permite entradas duplicadas na chave primária e índices únicos ?

Boa Noite Pessoal,

Seguindo tempestivas semanas de trabalho, algumas aulas sendo ministradas, provas de certificação marcadas e diversas outras pendências está realmente complicado manter a postagem semanal além de comparecer nos fóruns e até responder os e-mails e dúvidas de SQL Server (estou tentando colocar tudo em dia). Ontem apresentei uma aula sobre índices e notei que em uma das transparências aparecia a opção Ignore_Dup_Key. A idéia era meramente citar a opção, mas esse nome leva a interpretações equivocadas. Para não perder o hábito, resolvi discorrer um pouco mais sobre essa opção que ao contrário do que parece não permite a entrada de registros duplicados em uma chave primária, uma constraint unique ou um índice unique.

A relação entre constraints de unicidade e índices

Existem duas constraints relacionadas a unicidade (ou como alguns dizem, a integridade de entidade). A primeira é a chave primária e a segunda é a unique constraint. Normalmente a primeira opção é mais conhecida, pois, além de possibilitar unicidade é obrigatória para permitir relacionamentos. A segunda possui essas características embora não seja destinada a esse fim. Ela normalmente é utilizada para garantir a unicidade das chaves secundárias. Tanto a chave primária quanto a unique constraint são critérios lógicos e não físicos. Isso significa que uma chave primária ou uma unique constraint nada tem a ver com a unicidade dos registros diretamente. Para que essa unicidade possa ser garantida do ponto de vista físico, a criação dessas estruturas lógicas faz com que fisicamente índices sejam implementados. Ex:

– Cria uma tabela
CREATE TABLE Clientes (
    ID INT NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    CPF CHAR(11) NOT NULL,

CONSTRAINT PK_Clientes PRIMARY KEY (ID),
CONSTRAINT UQ_CPF UNIQUE (CPF))

– Verifica os índices existentes
EXEC sp_helpindex ‘Clientes’

– Verifica as constraints existentes
EXEC sp_helpconstraint ‘Clientes’

Como pode ser observado, a criação de uma chave primária e uma constraint unique provocou a criação de dois índices unique (mesmo que não de forma explícita). A criação desses índices é a implementação física que possibilita que restrições lógicas como a chave primária e a unique constraint possam ser garantidas. Não importa se a constraint é uma chave primária ou uma unique constraint, pois, fisicamente é criado um índice único. A ligação entre as constraints e os índices é indissiociável. Não é possível excluir os índices se as constraints existirem. A exclusão das constraints provoca a exclusão dos índices.

Os efeitos da opção IGNORE_DUP_KEY

O script abaixo remove as constraints previamente criadas e adiciona a propriedade IGNORE_DUP_KEY nas constraints. Essas propriedades não são pertinentes as constraints em si, mas são aplicáveis aos índices que serão criados por conta das constraints.

– Retira as Constraints da tabela de clientes
ALTER TABLE Clientes DROP CONSTRAINT PK_Clientes
ALTER TABLE Clientes DROP CONSTRAINT UQ_CPF

– Adiciona as constraints com a opção IGNORE_DUP_KEY
ALTER TABLE Clientes ADD CONSTRAINT PK_Clientes
    PRIMARY KEY (ID) WITH (IGNORE_DUP_KEY = ON)

ALTER TABLE Clientes ADD CONSTRAINT UQ_CPF
    UNIQUE (CPF) WITH (IGNORE_DUP_KEY = ON)

Agora que a chave primária foi criada com a opção de ignorar registros repetidos, resta checar se isso é semelhante a permitir registros duplicados.

– Insere um cliente com o ID 1 e com o ID 2
INSERT INTO Clientes VALUES (1, ‘Rodrigo Borges Freitas’, ’70036580923′)
INSERT INTO Clientes VALUES (2, ‘Iara Mendes Oliveira’, ’20136789102′)

– Insere um novo cliente com o ID 1 (já existente)
INSERT INTO Clientes VALUES (1, ‘Natasha Gomes Siqueira’, ’32205631997′)

O conjunto de comandos retorna a seguintes sequência de mensagens no SQL Server Management Studio:

(1 row(s) affected)

(1 row(s) affected)
Duplicate key was ignored.

(0 row(s) affected)

Os dois primeiros INSERTs foram contemplados na tabela, mas já o terceiro INSERT foi simplesmente “ignorado” e por isso a mensagem “(0 row(s) affected)”. Impedir a duplicação de chaves primárias é um requisito inviolável para permitir relacionamentos, pois, se chaves primárias pudessem ser duplicadas e houvesse dois registros com o ID igual a 1, qual seria o sentido das tabelas que tivessem uma FK apontando para esse ID ? Seria ilógico.

A unique constraint não é utilizada em relacionamentos e sobre certos aspectos menos restritiva. Será que a opção IGNORE_DUP_KEY possibilitaria a duplicação ?

– Insere um novo cliente com o ID 3, porém com CPF repetido
INSERT INTO Clientes VALUES (3, ‘Natasha Gomes Siqueira’, ’70036580923′)

Após a execução desse cadastro, a mensagem é a mesma que a anterior. O título “(0 row(s) affected)” e uma instrução SELECT podem confirmar que o terceiro registro não foi inserido. Nada mais lógico, pois, as restrições não se devem às particularidades de cada constraint já que ambas criam um índice único e rejeitam registros repetidos da mesma forma embora não retornem nenhuma mensagem de erro. Será que o mesmo se mantêm para inserções em conjunto e não em linha ?

– Cria uma tabela com a mesma estrutura porém sem restrições
CREATE TABLE ClientesTmp (
    ID INT NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    CPF CHAR(11) NOT NULL)

– Insere quatro registros
INSERT INTO ClientesTmp VALUES (1,‘Bruno Morais Jr.’,’45233310738′)
INSERT INTO ClientesTmp VALUES (3,‘Carla Martins Praxedes’,’20136789102′)
INSERT INTO ClientesTmp VALUES (4,‘Eugênio Silva Batista’,’58017439501′)
INSERT INTO ClientesTmp VALUES (5,‘Clístenes Jorge Moura’,’58017439501′)

– Tenta inserir todos os registros de ClientesTMP em Clientes
– A cláusula OUTPUT mostra o registro inserido

INSERT INTO Clientes (ID, Nome, CPF)
OUTPUT INSERTED.*
SELECT ID, Nome, CPF FROM ClientesTmp

Embora a tabela ClientesTmp tivesse 4 registros, apenas um registro foi inserido na tabela Clientes que conforme mostrado pela cláusula OUTPUT foi o ID 4. Os demais foram simplesmente ignorados. Não é difícil descobrir o porquê. O ID 1 já existia na tabela Clientes. O ID 3 possui um CPF que já está sendo utilizado pelo ID 2 na tabela Clientes e o ID 4 e 5 possuem o mesmo CPF. No momento em que o ID 4 foi inserido, o ID 5 passa a representar um CPF em repetição e por isso foi desconsiderado. Em outras palavras, o uso da opção IGNORE_DUP_KEY faz com que todos os registros sejam inseridos, descartando-se apenas os duplicados. Alguns podem achar que esse já era o comportamento padrão mesmo sem a opção IGNORE_DUP_KEY. Será mesmo ?

– Retira as Constraints da tabela de clientes
ALTER TABLE Clientes DROP CONSTRAINT PK_Clientes
ALTER TABLE Clientes DROP CONSTRAINT UQ_CPF

– Adiciona as constraints sem a opção IGNORE_DUP_KEY
ALTER TABLE Clientes ADD CONSTRAINT PK_Clientes PRIMARY KEY (ID)
ALTER TABLE Clientes ADD CONSTRAINT UQ_CPF UNIQUE (CPF)

– Exclui o ID 4 da tabela Clientes para tentar carregá-lo novamente
DELETE FROM Clientes WHERE ID = 4

– Tenta inserir todos os registros de ClientesTMP em Clientes
INSERT INTO Clientes (ID, Nome, CPF)
SELECT ID, Nome, CPF FROM ClientesTmp

Dessa vez o efeito não foi exatamente o mesmo. Embora o ID 4 fosse o único que pudesse ser inserido em Clientes a partir de ClientesTmp, o fato de haver outros rejeitos rejeitáveis fez com que toda a operação fosse abortada.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_Clientes’. Cannot insert duplicate key in object ‘dbo.Clientes’.
The statement has been terminated.

O teste da unique constraint sequer foi realizado, pois, o primeiro registro (ID 1) já provoca uma violação da chave primária PK_Clientes.

Após todos esses testes está mais que comprovodo que a opção IGNORE_DUP_KEY ignora os registros duplicados, mas isso em hipótese nenhuma significa que as duplicações são permitidas apenas porque essa opção foi especificada. A única coisa que essa opção faz é ignorar as duplicações descartando-as sem levantar uma exceção. Existem momentos em que isso é desejável, mas também existem outros que não. O fato é que o SQL Server, assim como qualquer banco de dados relacional sério não permitirá repetições em chaves primárias, constraints uniques, ou melhor dizendo em índices unique. Se alguém dissemina essa informação, está apenas disseminando mais um desses mitos não comprovados que vimos por aí.

[ ]s,

Gustavo

Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte II

Boa Noite Pessoal,

Dada as limitações do Spaces (eu nem sabia que existiam) no espaço do post, não pude colocar tudo em um único artigo sobre as consequências do uso da opção TRUNCATE_ONLY para truncar e diminuir o tamanho do Log. Após explicar as teorias necessárias para o entendimento dessas consequências na parte I do artigo, apresento agora um exemplo prático.

Um exemplo prático

Para tornar práticos alguns dos conceitos apresentados aqui, vejamos um exemplo:

– Cria um banco de dados
CREATE DATABASE SisMtr

– Garante que o Recovery Model é Full
ALTER DATABASE SisMtr SET RECOVERY FULL

– Cria alguns objetos no banco de dados SisMtr
CREATE TABLE SisMtr.dbo.Disciplinas (
    IDDisciplina INT, NomeDisciplina VARCHAR(100))

CREATE TABLE SisMtr.dbo.Cursos (
    IDCurso INT, NomeCurso VARCHAR(80))

– Faz um backup full da base
BACKUP DATABASE SisMtr TO DISK = ‘C:\Temp\SisMtrFull.BAK’

– Verifica informações do backup recém gravado
RESTORE HEADERONLY FROM DISK = ‘C:\Temp\SisMtrFull.BAK’

A exceção do último comando, esse script não faz nada demais. Ele apenas cria um banco, duas tabelas e faz um backup full. O último comando lê as informações do cabeçalho do backup full e mostra algumas informações de LSN sobre esse backup. Existem várias colunas, mas em especial duas merecem atenção:

FirstLSN LastLSN
22000000008400155 22000000015000001

Possivelmente os LSNs exibidos não serão os mesmos obtidos, pois, a sua numeração depende de várias características próprias de cada instância, bancos de dados criados, etc. A coluna FirstLSN mostra o LSN mais antigo encontrado no backup (22000000008400155) e o mais recente (22000000015000001). As transações que estão contempladas no backup full (juntamente com os dados), são todas as que ocorreram entre os LSNs 22000000008400155 e 22000000015000001.

Seguindo com o exemplo, serão realizadas algumas inserções nessas tabelas e será realizado um backup de log.

– Insere registros nas tabelas de Disciplinas e Cursos
INSERT INTO SisMtr.dbo.Disciplinas VALUES (1,‘História e Formação do Direito’)
INSERT INTO SisMtr.dbo.Disciplinas VALUES (2,‘Introdução à Economia’)
INSERT INTO SisMtr.dbo.Disciplinas VALUES (3,‘Administração Contemporânea’)
INSERT INTO SisMtr.dbo.Disciplinas VALUES (4,‘Ética’)
INSERT INTO SisMtr.dbo.Disciplinas VALUES (5,‘Metodologia Científica’)

INSERT INTO SisMtr.dbo.Cursos VALUES (1,‘Direito’)
INSERT INTO SisMtr.dbo.Cursos VALUES (2,‘Economia’)

– Faz um backup de Log
BACKUP LOG SisMtr TO DISK = ‘C:\Temp\SisMtrLog01.TRN’

– Verifica as informações do backup de Log
RESTORE HEADERONLY FROM DISK = ‘C:\Temp\SisMtrLog01.TRN’

O comando de RESTORE mostra as seguintes informações:

FirstLSN LastLSN
22000000008400155 22000000016900001

A coluna FirstLSN mostra que o primeiro LSN contemplado no backup de log é 22000000008400155. A coluna LastLSN mostra que o último LSN contemplado é 22000000016900001. Isso significa que esse backup de Log contempla todas as transações ocorridas entre os LSNs 22000000008400155 e 22000000016900001. Pode parecer estranho, pois, o backup full contemplava as transações entre o LSN 22000000008400155 e o LSN 22000000015000001 e o de Log de transações também contempla essas transações já que se inicia no LSN 22000000008400155 e termina no LSN 22000000016900001 que é superior ao LSN 22000000015000001 (LastLSN) do backup full. Isso é esperado porque esse foi o primeiro backup de log de transações e todo o conteúdo do arquivo do Log de transações foi "despejado" para o backup de Log.

– Cria uma tabela Associativa
CREATE TABLE SisMtr.dbo.DisciplinaCurso (
    IDDisciplina INT, IDCurso INT)

– Insere alguns registros na tabela recém criada
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (1,1)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (2,1)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (4,1)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (5,1)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (2,2)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (3,2)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (4,2)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (5,2)

– Faz um backup de Log
BACKUP LOG SisMtr TO DISK = ‘C:\Temp\SisMtrLog02.TRN’

– Verifica as informações do backup de Log
RESTORE HEADERONLY FROM DISK = ‘C:\Temp\SisMtrLog02.TRN’

O comando de RESTORE mostra as seguintes informações:

FirstLSN LastLSN
22000000016900001 22000000018800001

O primeiro LSN contemplado no arquivo é o LSN 22000000016900001 e o último LSN contemplado é o 22000000018800001. Isso significa que esse backup de Log contempla todos os LSNs entre 22000000016900001 e 22000000018800001. As sequências de LSNs após a realização dos backups são mostradas na tabela abaixo:

Backup Lsn Inicial Lsn Final
SisMtrFull 22000000008400155 22000000015000001
SisMtrLog01 22000000008400155 22000000016900001
SisMtrLog02 22000000016900001 22000000018800001

Com esses três backups é possível "refazer" a história do banco, pois, todas as transações estão contempladas e não há quebras nas sequências de LSN. O script abaixo conta quantos registros existem em cada uma das tabelas e faz a exclusão do banco de dados:

– Verifica quantos registros tem em cada tabela
SELECT
    (SELECT COUNT(*) FROM SisMtr.dbo.Disciplinas) As Disciplinas,
    (SELECT COUNT(*) FROM SisMtr.dbo.Cursos) As Cursos,
    (SELECT COUNT(*) FROM SisMtr.dbo.DisciplinaCurso) As DisciplinaCurso

– Efetua a exclusão do Banco
DROP DATABASE SisMtr

A tabela Disciplinas tem 5 registros, a tabela Cursos tem 2 registros e a tabela DisciplinasCurso tem 8 registros. Ao final do processo de restauração, esse mesmo resultado deve ser obtido.

– Restaura o Backup Full
RESTORE DATABASE SisMtr FROM DISK = ‘C:\Temp\SisMtrFull.BAK’ WITH NORECOVERY

– Verifica o status dos arquivos de dados do banco
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Differential_Base_LSN As LSN_Inicial, Redo_Start_Lsn As LSN_Final
FROM sys.master_files
WHERE Database_ID = DB_ID(‘SisMtr’) And Data_Space_Id > 0

Após restaurar o backup full, verifica-se que os LSNs contemplados no banco são justamente os mesmos obtidos pelo processo de restauração:

Banco Arquivo LSN_Inicial LSN_Final
SisMtr SisMtr 22000000008400155 22000000015000001

O banco encontra-se indisponível e em processo de restauração. O próximo passo é a aplicação do primeiro Log.

– Restaura o primeiro Log
RESTORE LOG SisMtr FROM DISK = ‘C:\Temp\SisMtrLog01.TRN’ WITH NORECOVERY

– Verifica o status dos arquivos de dados do banco
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Differential_Base_LSN As LSN_Inicial, Redo_Start_Lsn As LSN_Final
FROM sys.master_files
WHERE Database_ID = DB_ID(‘SisMtr’) And Data_Space_Id > 0

Após a restauração do primeiro backup de Log, verifica-se que o banco parou no LSN final (Last LSN) do backup de Log:

Banco Arquivo LSN_Inicial LSN_Final
SisMtr SisMtr 22000000008400155 22000000016900001

Embora o backup de Log tivesse as transações anteriores ao LSN final do backup Full (22000000015000001), apenas as transações entre o LSN 22000000015000001 e o LSN 22000000016900001 foram aplicadas, pois, as anteriores já estavam contempladas no backup full. O banco ainda está indisponível e necessita da aplicação do segundo log.

– Restaura o segundo Log
RESTORE LOG SisMtr FROM DISK = ‘C:\Temp\SisMtrLog02.TRN’ WITH NORECOVERY

– Verifica o status dos arquivos de dados do banco
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Differential_Base_LSN As LSN_Inicial, Redo_Start_Lsn As LSN_Final
FROM sys.master_files
WHERE Database_ID = DB_ID(‘SisMtr’) And Data_Space_Id > 0

Após a aplicação do segundo backup de Log, verifica-se que o banco parou no LSN final (Last LSN) do backup de Log:

Banco Arquivo LSN_Inicial LSN_Final
SisMtr SisMtr 22000000008400155 22000000018800001

Embora o segundo Log tenha sido aplicado, o banco de dados ainda não encontra-se plenamente operacional. O comando abaixo solicita ao SQL Server que o banco fique operacional.

– Deixa o banco operacional
RESTORE LOG SisMtr WITH RECOVERY

– Verifica o status dos arquivos de dados do banco
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Differential_Base_LSN As LSN_Inicial, Redo_Start_Lsn As LSN_Final
FROM sys.master_files
WHERE Database_ID = DB_ID(‘SisMtr’) And Data_Space_Id > 0

Após o banco de dados ficar OnLine e disponível não é possível mais determinar qual é o LSN final, pois, nunca se sabe quando em qual LSN as transações irão parar de ocorrer, pois, é esperado que o banco de dados não tenha nenhum tipo de problema que afete o seu funcionamento e que transações futuras venham a ser realizadas. Essa é a razão pela qual o valor da coluna LSN_Final é nula nesse caso. O objetivo foi demonstrar que os backups foram aplicados e que os LSNs estavam sendo contemplados. O processo de restauração foi possível porque as sequências de LSNs estavam íntegras e refletidas no backup.

O exemplo prático com o BACKUP LOG WITH TRUNCATE_ONLY

Como será que o uso do truncate pode afetar o processo de backup ? Vou utilizar um outro exemplo para simular o que costuma ocorrer em ambiente de produção quando esse comando é utilizado.

– Cria o banco de dados
CREATE DATABASE BDCargas ON (
    NAME=‘BDCargas_Data’, SIZE=10MB,
    FILENAME=‘C:\Temp\BDCargas_Data.MDF’)
LOG ON (
    NAME=‘BDCargas_Log’, SIZE=1MB,
    FILENAME=‘C:\Temp\BDCargas_Log.LDF’)

– Muda o Recovery Model para Full
ALTER DATABASE BDCargas SET RECOVERY FULL

– Verifica o tamanho dos arquivos
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Physical_Name As ArquivoFisico, (Size * 8) / 1024.00 As TamanhoMB
FROM sys.master_files WHERE Database_ID = DB_ID(‘BDCargas’)

– Realiza um Backup Full
BACKUP DATABASE BDCargas TO DISK = ‘C:\Temp\BDCargasFull.BAK’

– Verifica as informações do Backup Full
RESTORE HEADERONLY FROM DISK = ‘C:\Temp\BDCargasFull.BAK’

O script simplesmente cria um banco de dados chamado BDCargas, muda o Recovery Model para Full (caso já não esteja) e verifica o tamanho dos arquivos utilizados respectivamente 10MB para dados e 1MB para Log. As informações do Backup Full em relação ao LSN são:

FirstLSN LastLSN
23000000005900037 23000000007700001

O próximo passo é fazer com que o Log encha fazendo-se "necessário" o comando de TRUNCATE. O script abaixo cria uma tabela e efetua vários Inserts e Deletes. O tamanho do arquivo de dados não irá se alterar, pois, cada inserção gera uma exclusão, mas como as duas ações são logadas, o arquivo de log ficará muito grande.

– Cria uma tabela
CREATE TABLE BDCargas.dbo.Registros (
    ID INT IDENTITY(1,1), Mascara UNIQUEIDENTIFIER DEFAULT NEWID())

– Faz um backup de Log
BACKUP LOG BDCargas TO DISK = ‘C:\Temp\BDCargasLog01.TRN’

– Insere e exclui 200.000 registros
DECLARE @i INT
SET @i = 1

WHILE @i <= 200000
BEGIN
    INSERT INTO
BDCargas.dbo.Registros DEFAULT VALUES
    DELETE FROM BDCargas.dbo.Registros
    SET @i = @i + 1
END

– Verifica o tamanho dos arquivos
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Physical_Name As ArquivoFisico, (Size * 8) / 1024.00 As TamanhoMB
FROM sys.master_files WHERE Database_ID = DB_ID(‘BDCargas’)

Após aproximadamente cinco minutos, é possível notar que houve um aumento considerável do tamanho do Log que passou de 1MB para pouco mais de 214MB conforme a tabela abaixo:

Banco ArquivoLogico ArquivoFisico TamanhoMB
BDCargas BDCargas_Data C:\Temp\BDCargas_Data.MDF 10.0000000
BDCargas BDCargas_Log C:\Temp\BDCargas_Log.LDF 214.1250000

Não acho que 214MB seja um espaço absurdo, mas para realmente mostrar os malefícios do truncate do log, adicionei mais algumas transações antes de truncar o log.

– Insere dois registros
INSERT INTO BDCargas.dbo.Registros
DEFAULT VALUES
INSERT INTO BDCargas.dbo.Registros DEFAULT VALUES

– Trunca o Log
BACKUP LOG BDCargas
 WITH TRUNCATE_ONLY

– Muda o contexto de banco de dados
USE BDCargas;

– Diminui o tamanho do arquivo de LOG
DBCC SHRINKFILE(BDCargas_Log,1)

– Verifica o tamanho dos arquivos
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Physical_Name As ArquivoFisico, (Size * 8) / 1024.00 As TamanhoMB
FROM sys.master_files WHERE Database_ID = DB_ID(‘BDCargas’)

– Muda o contexto de banco de dados
USE MASTER;

Após executar o comando de truncagem e em seguida o Shrink é notável a diminuição do arquivo de Log de 214MB para o seu tamanho original:

Banco ArquivoLogico ArquivoFisico TamanhoMB
BDCargas BDCargas_Data C:\Temp\BDCargas_Data.MDF 10.0000000
BDCargas BDCargas_Log C:\Temp\BDCargas_Log.LDF 1.0000000

Após as 200.000 linhas terem sido inseridas e excluídas, o Log de transações cresceu rapidamente. Para diminuí-lo, utilizou-se o comando de truncate em conjunto com o Shrink devolvendo-o ao tamanho original. Antes do truncate haviam duas linhas que foram inseridas. Irei inserir mais três linhas e prosseguirei com um backup de Log e excluirei o banco de dados.

– Insere três registros
INSERT INTO BDCargas.dbo.Registros DEFAULT VALUES
INSERT INTO BDCargas.dbo.Registros DEFAULT VALUES
INSERT INTO BDCargas.dbo.Registros DEFAULT VALUES

– Verifica os registros que estão na tabela
SELECT ID, Mascara FROM BDCargas.dbo.Registros

– Faz um backup de Log
BACKUP LOG BDCargas TO DISK = ‘C:\Temp\BDCargasLog02.TRN’

– Exclui o banco de dados
DROP DATABASE BDCargas

Antes de excluir o banco de dados, o SELECT mostrou que haviam cinco registros cadastrados com os respectivos IDs 200.001, 200.002, 200.003, 200.004 e 200.005. Porém, ao tentar fazer um novo backup de Log, o SQL Server advertiu uma mensagem de erro.

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

Não foi possível fazer o backup de Log, pois, ele está sem referências. Como o log foi truncado entre o último backup de Log e a tentativa de um novo backup, transações foram descartadas do Log sem terem sido copiadas. Isso significa que embora esteja tudo certo com o banco, a estratégia de backups baseada em Logs ficou inviabilizada, pois, houve uma quebra de sequência. Uma forma de tornar a estratégia de Logs possível novamente é a realização de um novo backup full.

– Efetua um novo Backup Full
BACKUP DATABASE BDCargas TO DISK = ‘C:\Temp\BDCargasFull02.BAK’

– Efetua um novo Backup de Log
BACKUP LOG BDCargas TO DISK = ‘C:\Temp\BDCargasLog02.TRN’

Até pode-se tirar um novo backup full para viabilizar os logs posteriores, mas o fato é que algumas possibilidades foram perdidas. A tabela abaixo mostra o resumo dos backups e seus LSNs.

Backup Lsn Inicial Lsn Final
BDCargasFull 23000000005900037 23000000007700001
BDCargasLog01 23000000005900037 23000000010400001
BDCargasFull02 183000000008600037 183000000010200001
BDCargasLog02 183000000008600037 183000000010800001

É possível restaurar o primeiro backup full e aplicar o primeiro backup de log ou restaurar o segundo backup full e aplicar o segundo backup de log. As transações entre o primeiro backup de log e o segundo backup full não podem ser recuperadas por um processo de backup, pois, a truncagem do log fez com que elas fossem perdidas e não pudessem ser copiadas. Se o DBA necessitar de restaurar o banco em algum momento desse intervalo, ele não conseguirá. Possivelmente se isso acontecer, será uma demanda de extrema importância e o DBA descobrirá que esse intervalo foi perdido somente quando for "tarde demais".

Se por um lado o truncate do log pode realmente liberar espaço, por outro lado ele poderá causar um grave problema para o futuro. Utilizar o TRUNCATE_ONLY é semelhante a plantar uma bomba relógio para algum momento posterior. Pode ser que ela não exploda, pois, não necessariamente alguém irá solicitar um backup exatamente em um trecho não coberto, mas de qualquer forma, aqueles que o fazem com frequência, estão plantando não somente uma mas várias bombas relógio e nesse caso as chances de alguma delas explodir aumenta. Será terrível ter de dizer que não foi possível restaurar um backup não porque o arquivo corrompeu-se, porque a mídia foi roubada, etc mas simplesmente porque "utilizou-se um comando que não se sabia as consequências".

Alternativas para evitar o uso do BACKUP LOG com o TRUNCATE_ONLY

Acho que os benefícios e os malefícios desse comando já estão demonstrados. A regra geral é bem simples: "Se você realmente necessita de restaurações em momentos específicos e (ou) depende dos backups de log, a solução é ter uma rotina para fazer esses backups. Se eles forem feitos, o log não irá encher. Se você realmente não necessita de restaurações em momentos específicos e (ou) não depende dos backups de log, simplesmente altere o Recovery Model para Simple, pois, nesse modelo, as entradas de log serão eliminadas automaticamente dispensando a necessidade de truncar o log".

Ainda assim podem ocorrer situações em que o log fuja ao controle. Um processo de carga mal planejado, uma tentativa de invasão, uma falha no job de backups são algumas razões pelas quais um arquivo de log pode crescer rapidamente e talvez não haja tempo de esperar que o backup de log seja concluído. Nesse caso, pode ser tentador executar o TRUNCATE_ONLY, mas ainda assim essa deve ser a última alternativa e não a primeira. Algumas soluções paleativas que podem ser usadas.

  • Se houver mais arquivos no mesmo drive que o log cresceu, faça o backup de log dos arquivos menores e execute um shrink. Isso deve dar algum tempo para que o log que estourou possa ser copiado.
  • Se houver espaço disponível em outras unidades, considere efetuar um backup de log dividido em vários arquivos. Isso faz com que ele seja executado mais rapidamente.
  • Se houver espaço disponível em outra unidade, crie um segundo arquivo de log para que as transações sejam gravadas nesse arquivo enquanto o backup de log é realizado.
  • Se realmente for imprescindível fazer um truncate do log, lembre-se de fazer um backup full e (ou) diferencial logo em seguida para que as perdas sejam menores. Não esvazie o log e deixe por isso mesmo, pois, quanto mais tempo o backup demorar a ser feito depois do truncate, maior será o período de não recuperação.

Ao meu ver, se o profissional que trabalha com o banco tiver conhecimento e o ambiente for organizado, o truncate_only passa a ser um comando completamente dispensável. Se alguém precisa utilizá-lo é porque houve falta de planejamento e organização do ambiente ou há falta de conhecimento daquele que o utiliza. Tive a chance de ver um ambiente com mais de 600 bases em SQL Server de missão altamente crítica e simplesmente há mais de dois anos que a equipe de DBAs sequer sentiu a necessidade de executar esse comando em ambiente de produção. Problemas de espaço em disco e Logs estourando aconteceram, mas  foram contornados com as soluções corretas e não com o truncate do log. Acredito que se um ambiente desses consegue viver sem o TRUNCATE_ONLY, qualquer um pode organizar-se para não utilizá-lo.

Alguns podem até considerar exagero, mas vale a pena lembrar que o SQL Server 2008 não aceita o uso do BACKUP LOG com a opção WITH TRUNCATE_ONLY. No SQL Server 2008 é necessário mudar o Recovery Model para Simple se o log necessitar ser truncado. Se o banco realmente não necessita dos backups de Log, mudar o recovery model para Simple é a solução definitiva. A Microsoft também percebe que esse comando é completamente dispensável e que não deve ser utilizado.

"We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure. Use manual log truncation in only very special circumstances, and create backups of the data immediately"

Aqueles que tem problemas com o Log de transações e ao procurar ajuda se depararam com o BACKUP LOG WITH TRUNCATE_ONLY não devem simplesmente executá-lo, mas sim avaliar se ele é realmente a solução. Aqueles que "aconselham" usar o BACKUP LOG WITH TRUNCATE_ONLY devem pensar melhor antes de dar um conselho desses, pois, a vontade de ajudar pode acabar atrapalhando.

Quando alguém aparecer gritando "Socorro meu log cresceu demais. Como faço para zerá-lo ou diminuir o Log ?", espero que haja questionamentos e não simplesmente "use o BACKUP LOG WITH TRUNCATE_ONLY com o SHRINK que resolve".

[ ]s,

Gustavo

Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte I

Boa Noite Pessoal,

Nos últimos dias estive um pouco ausente dos fóruns e de diversas comunidades que participo, pois estou ministrando um curso oficial de SQL Server 2008. Ontem durante a aula apresentei a parte de backups e após as explicações, participei para os alunos uma situação muito comum que indiretamente está relacionada com backups. Hoje nos fóruns MSDN e Technet também presenciei a mesma situação acontecendo novamente intitulando-se "Log Gigantesco".

Acredito que depois da dificuldade de conectar-se remotamente ao SQL Server (se alguém estiver com essa dificuldade leia esse excelente artigo "Como configurar Conexão Remota no SQL Server 2005") , problemas com o log de transação que cresce assustadoramente representam provavelmente a dúvida mais comum de SQL Server que exista. De fato já vi inúmeras dúvidas relacionadas ao log do SQL Server que cresce, cresce, cresce e deixa alguns desenvolvedores, dbas iniciantes e profissionais preocupados e aparentemente sem ter o que fazer. Na maioria das vezes a solução é uma só conforme descrito abaixo (o texto foi adaptado para preservar a identidade de seus participantes):

"Oi Gente,

Estou com um banco de dados com um arquivo de LOG com mais de 40Gb e preciso de espaço.
O HD só tem mais 2GB e LOG continua crescendo. O que devo fazer ?"

"Oi Fulano,

Cara é muito simples. Basta só rodar esses dois comandos:

Backup Log NomeDoSeuBanco WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(NomeLogicoArquivoLog, 10)
GO

Se você não souber o nome do arquivo lógico roda a sp_helpdb ‘SeuBanco’. É importante rodar os dois comandos conectado no seu banco."

De fato essa é a solução padrão e realmente funciona. Na maioria das situações, aqueles que aplicaram essa solução certamente viram seus arquivos de Log diminuir tão assustadoramente quanto cresceram. Com uma solução tão "eficiente" a receita de bolo é escrita. Toda vez que o LOG atingir tamanhos absurdos, basta então rodar o BACKUP LOG com o TRUNCATE_ONLY e logo em seguida rodar o SHRINK FILE. A eficiência desse comando parece ser tanta que não me estranha que ele seja contemplado em jobs, planos de manutenção e nem que seja encapsulado em uma stored procedure.

Inquestionavalmente arquivos de Log gigantescos são um incômodo. Não só pelo fato de ameaçaram acabar com todo o espaço livre disponível na unidade, mas também porque se um backup possui um arquivo de LOG gigantesco, será necessário reservar um espaço gigantesco em outro ambiente no momento da restauração. E como normalmente os ambientes de homologação e desenvolvimento tem menos recursos, arquivos muito grandes podem ser verdadeiros inconvenientes. Após ver tantas dezenas de threads com problemas de arquivos de log que crescem além da conta fico com duas preocupações: Será que aqueles que simplesmente executam esses comandos tem a menor noção do problema que eles podem provocar ? E será que aqueles que repassam esses comandos sabem exatamente o tamanho do risco que podem estar introduzindo em outros ambientes ?

"Afinal o que há de mal em executar esses dois comandos ? Eles sempre irão reduzir o log de transações e "resolver o problema" e essa é a solução que está escrita na maioria dos tutoriais, fóruns, etc. Se o arquivo de log não for limpo aí sim é que os problemas aparecem." Não posso deixar de concordar com a afirmação, mas vejamos algo um pouco mais apurado antes de tirar as conclusões sobre algo praticamente inquestionável.

Um pouco sobre o Log de transações

Todo banco de dados possui uma estrutura de log que registra tudo o que aconteceu que represente escrita nesse banco de dados. Essa estrutura é imprescindível para que o banco de dados saiba o que aconteceu na eventualidade de uma queda de energia entre outros problemas e mantenha-se consistente. De tempos em tempos (mais precisamente a cada Checkpoint), as transações registradas no Log de transações são efetivamente aplicadas no banco de dados mantendo sua consistência. As entradas no Log possuem um sequencial chamado LSN que é o acrônimo de Log Sequence Number. À medida que o Log recebe novas instruções, novos LSNs são adicionados. A tabela abaixo mostra um hipotético exemplo de um Log de transações.

LSN Instrução
001 INSERT INTO Clientes VALUES (1,‘Alberto’,2560.92,‘M’)
002 DELETE FROM AjusteEstoque WHERE IDAjuste = 3097
003 CREATE TABLE Defeitos (IDDef INT, IDProd INT, Data SMALLDATETIME)
004 CHECKPOINT
005 ALTER TABLE Produtos ADD DataUltimaAtualizacao SMALLDATETIME
006 UPDATE Produtos SET DataUltimaAtualizacao = DataCadastro
007 GRANT SELECT ON Produtos TO UsrMarketing
008 EXEC sp_addrolemember ‘db_datareader’, ‘UsrRelatorios’
009 CHECKPOINT
010 DROP TABLE ProdutosHistorico

A propriedade RECOVERY MODEL

O Recovery Model de um banco de dados tem papel fundamental no funcionamento dessas estruturas de log e nas estratégias possíveis de Disaster Recover a partir de um backup. Há três opções para o Recovery Model: Simple, Bulk Logged e Full. Não explicarei os detalhes do funcionamento do Bulk Logged dada sua proximidade com o Recovery Model Full. Para o entendimento desse artigo, o Recovery Model Bulk Logged e Full podem ser interpretados como iguais embora não o sejam em alguns casos.

No Recovery Model Simple todas as transações são gravadas no arquivo de Log, mas a cada Checkpoint elas são excluídas do arquivo de Log liberando espaço para novas entradas. Se considerarmos que o arquivo de log exemplificado tem capacidade para gravar 10 LSNs e que o Recovery Model está marcado como Simple, após o Checkpoint do LSN004, os LSNs 001, 002, 003 e 004 seriam eliminados do arquivo de log, o mesmo valeria para os LSNs 005, 006, 007, 008 e 009 após o Checkpoint em LSN009. Se nesse momento aparecessem mais algumas entradas, elas seriam gravadas no início do arquivo que foi liberado. Ex

LSN Instrução
011 TRUNCATE TABLE TMP
012 EXEC UspRelatorioVendas @DataInicio = ’20090720′
013 DBCC CHECKTABLE(‘RelatoriosConsolidados’)
014 CHECKPOINT
015 DROP USER UsrApp
   
   
   
   
010 DROP TABLE ProdutosHistorico

Os LSNs 011, 012, 013 e 014 são posteriores ao LSN010, mas como o arquivo tem capacidade para 10 entradas, é necessário acomodar esses LSNs. Como há espaço no início do arquivo, as entradas podem ser cadastradas perfeitamente sem prejudicar a consistência desse Log. No Recovery Model Simple, o Log recicla-se automaticamente e apaga as entradas que ficaram para trás e que foram contempladas no banco pelo processo de Checkpoint. Após o processo de Checkpoint, as transações ficam refletidas no banco e portanto são apagadas do Log de transações. O checkpoint contemplado no LSN014 apagaria todos os LSNs deixando somente o LSN015 e liberaria espaço para mais nove entradas de Log (quatro na área antes do LSN015 e cinco após a presença do LSN015). O Recovery Model Simple irá sempre reciclar as entradas do log formando um circulo na qual hora determinadas posições estaram preenchidas, serão apagadas pelo processo de Checkpoint, serão utilizadas novamente e excluídas novamente. É por esse comportamento em forma de círculo que essa arquitetura é chamada em alguns produtos de Log Circular.

No Recovery Model Full, as coisas funcionam um pouco diferentes. Ao contrário do Recovery Model Simple, o Recovery Model Full não irá apagar de forma automática os LSNs do log de transações independente do que aconteça. Mesmo que o processo de Checkpoint ocorra e as transações já estejam contempladas no banco de dados, o Log continuará a mantê-las no arquivo de Log. Essa característica de arquivamento faz com que alguns chamem essa arquitetura de Archieve Log. Se admitirmos que esse arquivo possui capacidade apenas para 10 LSNs, após completar os 10 LSNs, o arquivo ficará completamente cheio. O cadastro do LSN 011 ao LSN015 irá demandar que o arquivo de Log cresça conforme a tabela abaixo:

LSN Instrução
001 INSERT INTO Clientes VALUES (1,‘Alberto’,2560.92,‘M’)
002 DELETE FROM AjusteEstoque WHERE IDAjuste = 3097
003 CREATE TABLE Defeitos (IDDef INT, IDProd INT, Data SMALLDATETIME)
004 CHECKPOINT
005 ALTER TABLE Produtos ADD DataUltimaAtualizacao SMALLDATETIME
006 UPDATE Produtos SET DataUltimaAtualizacao = DataCadastro
007 GRANT SELECT ON Produtos TO UsrMarketing
008 EXEC sp_addrolemember ‘db_datareader’, ‘UsrRelatorios’
009 CHECKPOINT
010 DROP TABLE ProdutosHistorico
011 TRUNCATE TABLE TMP
012 EXEC UspRelatorioVendas @DataInicio = ’20090720′
013 DBCC CHECKTABLE(‘RelatoriosConsolidados’)
014 CHECKPOINT
015 DROP USER UsrApp

Uma vez que o Recovery Model Full faz com que o banco de dados não exclua as entradas antigas do log de transações, ou seja, as transações que já estão contempladas no banco de dados, não é de se esperar que o log cresça de forma indefinida. Afinal novas transações irão gerar novos LSNs e como esses não são limpos de forma automática o resultado final é um arquivo de log de tamanhos acima do normal. O crescimento do Log se dará na velocidade em que novas transações ocorrem, mas é fato que mais cedo ou mais tarde ele irá incomodar.

Uma conclusão inicial é que se o arquivo de Log cresce de forma descomunal, o banco de dados não pode estar em Recovery Model Simple. Se o Recovery Model Simple faz a devida exclusão das entradas inativas (aquelas já contempladas pelo Checkpoint), não há como o Recovery Model Simple fazer com que um arquivo de Log cresça de forma desproporcional (muitas vezes ele não cresce). Se o banco de dados estiver com o Recovery Model Simple, a única preocupação é ter um arquivo de log que consiga acomodar a quantidade média de transações entre um checkpoint e outro.

A primeira recomendação é que se o arquivo de Log cresce demais, ao invés de simplesmente efetuar o Truncate com o Shrink funcionem, é mais factível mudar o Recovery Model para Simple. Assim, o arquivo de Log não irá crescer de forma descomunal e tenderá a ficar constante. Embora o Truncate com o Shrink funcione, o problema voltará a acontecer, pois, se o Recovery Model é Full o Log irá crescer novamente. Ao invés de executar sucessivos Truncates e Shrinks é mais sensato alterar o Recovery Model para Simple eliminando a causa raiz do problema.

A título de curiosidade, o truncate iria eliminar as entradas inativas do log, ou seja, todas aquelas anteriores ao último checkpoint e o shrink iria reduzir o arquivo, possivelmente devolvendo-o ao tamanho de 10 LSNs ao invés de 15 conforme a tabela abaixo:

LSN Instrução
   
   
   
   
   
   
   
   
   
015 DROP USER UsrApp

O início do arquivo ficaria pronto para receber novas entradas de LSN. Com o tempo, o Log cresceria e provocaria sucessivas expansões até que um novo truncate e um novo shrink fossem executados.

Se a diferença entre o Recovery Model Simple e o Recovery Model fosse apenas a administração do tamanho do arquivo de log seria interessante perguntar qual a utilidade do Recovery Model Full. Enquanto o Recovery Model Simple dispensa maiores preocupações com o tamanho do arquivo de log, o Recovery Model Full traz responsabilidades e preocupações adicionais e isso é um forte indício para pensar porque os bancos de dados não são criados com o Recovery Model Simple ? Dá muito menos trabalho.

É sem dúvida um raciocínio que faz sentido, mas vejamos a seguir porque o Recovery Model Full é importante mesmo com as implicações e overheads administrativos.

A escolha do Recovery Model e o processo de restauração

O Recovery Model tem papel fundamental no processo de backup e restauração do banco de dados. Alguns Recovery Models permitem a realização de backups de log e posterior recuperação enquanto outro simplesmente não permitem. O uso de backup de logs também é imprecindível para algumas estratégias de backup e restore mais complexas como o Restore Online e o Piecemal Restore ambos utilizados em conjunto com backups e filegroups. Por hora basta saber que o Recovery Model Simple não possibilita a realização de backups de logs enquanto que os demais Recovery Models possibilitam.

A razão para o Recovery Model Simple não permitir backups de logs é um pouco óbvia. Como ele exclui as entradas mais antigas do Log de transações a cada Checkpoint e como cada Checkpoint ocorre em média de minuto em minuto, não faria muito sentido fazer o backup do log de transações de uma base com o Recovery Model Simple. Não haveria o que gravar no backup, pois, provavelmente no momento de fazê-lo restariam apenas as entradas mais recentes (as que o Checkpoint) ainda não contemplou enquanto que muitos outros LSNs teriam sido deixados de lado. Como o Recovery Model Full não exclui as entradas do Log (mesmo as já contempladas pelo Checkpoint), faz sentido em falar de backups de log para bases com o Recovery Model Full. Vejamos como isso funciona através do hipotético arquivo de log.

LSN Hora Instrução
001 00:00 BACKUP DATABASE BD TO DISK = ‘C:\Temp\BDFull.BAK’
002 08:00 CREATE TABLE Tbl (Codigo INT)
003 09:00 INSERT INTO Tbl VALUES (1)
004 09:30 INSERT INTO Tbl VALUES (2)
005 10:00 BACKUP LOG BD TO DISK = ‘C:\Temp\BDLog01.TRN’
006 14:45 INSERT INTO Tbl VALUES (4)
007 15:20 INSERT INTO Tbl VALUES (5)
008 15:30 CHECKPOINT
009 16:00 INSERT INTO Tbl VALUES (6)
010 18:00 BACKUP LOG BD TO DISK = ‘C:\Temp\BDLog02.TRN’

Os backups contemplam a relação de LSNs conforme a tabela abaixo:

Backup Lsn Inicial Lsn Final
BDFull 001 001
BDLog01 002 005
BDLog02 006 010

Com essa relação de backups é visível que o banco de dados pode ser reconstruído para parar em qualquer posição possível entre o LSN 001 e o LSN 010. A tabela abaixo mostra as sequências necessárias para voltar o banco em qualquer LSN, ou melhor dizendo qualquer milissegundo entre às 00:00 e às 18:00.

LSN Sequência Necessária
001 Apenas o BDFull
002 Restaurar o BDFull e o BDLog01 com parada às 08:00
003 Restaurar o BDFull e o BDLog01 com parada às 09:00
004 Restaurar o BDFull e o BDLog01 com parada às 09:30
005 Restaurar o BDFull e o BDLog01 com aplicação total de BDLog01
006 Restaurar o BDFull, o BDLog1 com aplicação total e o BDLog02 com parada às 14:45
007 Restaurar o BDFull, o BDLog1 com aplicação total e o BDLog02 com parada às 15:20
008 Restaurar o BDFull, o BDLog1 com aplicação total e o BDLog02 com parada às 15:30
009 Restaurar o BDFull, o BDLog1 com aplicação total e o BDLog02 com parada às 16:00
010 Restaurar o BDFull, o BDLog1 e o BDLog02 com aplicação total para ambos

Segundo a tabela, é possível restaurar em qualquer LSN aplicando-se o backup full e restaurando-se os Logs necessários. Embora o exemplo pare em LSNs específicos, uma vez que os backups contemplem toda a duração de 00:00 até às 18:00 é possível parar em qualquer posição nesse intervalo. É totalmente factível voltar o banco às 12:00 mesmo que não haja nenhum LSN identificado.

Após a realização do backup de Log (BDLog01) os LSNs 001, 002, 003, 004 e 005 foram retirados do arquivo de Log e copiados para o backup de Log BDLog01. O backup de Log BDLog02 irá contemplar todos os LSNs gravados desde o último backup de Log (no caso o BDLog01) e portanto irá retirar do arquivo de Log os LSNs 006, 007, 008, 009 e 010. A realização desses backups retirou as entradas de Log inativas, liberou espaço e fez com que o arquivo de Log possuísse espaço disponível para acomodar futuras transações sem provocar um crescimento exagerado ou ainda o estouro do espaço disponível em disco.

Da mesma forma que um banco com o Recovery Model Simple não permite realizar backup de Log, ele também não permite efetuar o truncate do Log. Então situações que exijam o uso do truncate do Log só podem contemplar outros Recovery Models como no caso o Full. Vejamos como o uso do truncate (independente do Shrink ou não) afetaria o exemplo.

LSN Hora Instrução
001 00:00 BACKUP DATABASE BD TO DISK = ‘C:\Temp\BDFull.BAK’
002 08:00 CREATE TABLE Tbl (Codigo INT)
003 09:00 INSERT INTO Tbl VALUES (1)
004 09:30 INSERT INTO Tbl VALUES (2)
005 10:00 BACKUP LOG BD TO DISK = ‘C:\Temp\BDLog01.TRN’
006 14:45 CREATE TABLE EmpregadoMes (Nome VARCHAR(50))
007 15:20 INSERT INTO Tbl VALUES (5)
008 15:30 BACKUP LOG BD WITH TRUNCATE_ONLY
009 16:00 INSERT INTO EmpregadoMes VALUES (‘Fernando’)
010 18:00 BACKUP LOG BD TO DISK = ‘C:\Temp\BDLog02.TRN’

A relação de LSNs conforme os backups ficaria da seguinte forma:

Backup Lsn Inicial Lsn Final
BDFull 001 001
BDLog01 002 005
BDLog02 009 010

É possível observar que o LSN 008 fez um truncate no Log e por isso o backup de Log BDLog02 só contemplou o LSN subsequente ao LSN que efetuou o truncate. Vejamos agora as possibilidades de restauração:

LSN Sequência Necessária
001 Apenas o BDFull
002 Restaurar o BDFull e o BDLog01 com parada às 08:00
003 Restaurar o BDFull e o BDLog01 com parada às 09:00
004 Restaurar o BDFull e o BDLog01 com parada às 09:30
005 Restaurar o BDFull e o BDLog01 com aplicação total de BDLog01
006 Não é possível restaurar, pois, não há como chegar até o LSN 006
007 Não é possível restaurar, pois, não há como chegar até o LSN 007
008 Não é possível restaurar, pois, não há como chegar até o LSN 008
009 Não é possível restaurar, pois, não há como pular para o LSN 009
010 Não é possível restaurar, pois, não há como pular para o LSN 010

Como o log foi truncado no LSN 008, o backup de Log BDLog02 não irá contemplar os LSNs 006, 007 e 008 que são os LSNs subsequentes ao backup de Log BDLog01. Assim é impossível restaurar o banco em uma posição que contemple esses LSNs, ou seja, não será possível restaurar o banco em nenhum horário entre às 10:00 (LSN 005) e às 16:00 (LSN 009). Embora os LSNs 009 e 010 estejam contemplados no backup de Log BDLog02, também não será possível restaurá-los, pois, a restauração de backups de log não permite que sequências de log sejam quebradas. Se o Backup Full (BDFull) e o primeiro backup de Log (BDLog) forem restaurados, o banco ficará parado no LSN 005 às 10:00. Não será possível simplesmente "pular" das 10:00 (LSN 005) para às 16:00 (LSN 009), pois, nesse tempo houve atividades no banco de dados que não podem ser desconsideradas. Podemos ver que o LSN 006 cria a tabela EmpregadoMes e que o LSN 009 faz um INSERT nessa tabela. Se os LSNs 006, 007 e 008 pudessem ser ignorados, o LSN 009 iria gerar um erro, pois, se o LSN 006 foi ignorado, a tabela EmpregadoMes não exisitiria. A verdade é que mesmo que essa tabela fosse criada em um LSN contemplado no backup (digamos o LSN 004), não é possível fazer a restauração, pois, a única forma de garantir a consistência é passando por todos os LSNs contemplados sem "pular" (ou melhor desconsiderar) etapas da história do banco de dados. A verdade é que no momento em que se trunca o Log todo o processo de restauração é invalidado a partir do último backup de log antes do truncate (no caso após o LSN 005 o banco não pode ser recuperado).

Observa-se que um simples comando de backup com a opção truncate_only inviabilizou parte do processo de restauração. Inegavelmente o truncate do log de transações liberou espaço e talvez tenha "resolvido o problema do log de transações grande demais". Sim, essa é justamente a parte que todos conhecem e esperam como resultado do comando. Se o Shrink for executado em seguida, a área livre do arquivo de Log será devolvida ao Windows fazendo com que o arquivo seja efetivamente reduzido. O que maioria das pessoas efetivamente não avalia ou simplesmente desconhece é o efeito colateral desse comando. De fato ele liberou espaço, mas acabou de invalidar parte do processo de restauração. Possivelmente o executor do comando não irá descobrir isso após truncar o log, mas sim em uma situação futura quando seu banco de dados estiver com problemas e ele precisar restaurar um backup. Nesse hora ele encherá o peito e falará a si próprio (ou ao chefe se ele estiver próximo): "Tenho uma rotina de backups full e de Log e posso portanto restaurar o backup em qualquer posição desejada". Mal sabe ele que após rodar o inocente comando de truncate do log sua rotina de backups de Log foi jogada no lixo. O problema é que essa descoberta se dará no momento em que o backup será o mais imprescindível possível e infelizmente não será possível contar com ele.

Dada as limitações atuais do Spaces, não pude publicar nesse artigo um exemplo prático. Eu o apresento logo a seguir na parte II do artigo.

[ ]s,

Gustavo

Mitos do SQL Server – Será que COUNT(1) ou COUNT(‘X’) são mais performáticos que COUNT(*) ?

Bom Dia Pessoal,

Primeiro eu gostaria de agradecer os vários feedbacks que tive sobre a recém categoria "Mitos – SQL Server". A idéia de criar essa categoria surgiu meio que de brincadeira e achei que seria apenas mais um post dos vários que faço e não possuem comentários. Ao contrário do que imaginava, me surpreendi com os feedbacks, comentários e pedidos de novos mitos a serem desmistificados. É esse tipo de retorno que realmente motiva a elaborar artigos, webcasts, vídeos e continuar contribuindo com a comunidade.

Eu havia planejado um outro post para essa semana falando de ferramentas para o SQL Server, mas em virtude dos ótimos feedbacks resolvi adiá-lo um pouco. Hoje falarei sobre mais um desses mitos e lendas urbanas que muito se repete, mas pouco se investiga. Quando comecei a aprender SQL (faz um tempinho já), um analista me disse que quando fosse utilizar o COUNT, não usasse o COUNT(*), mas sim o COUNT(1) ou alguma outra expressão como COUNT(‘X’), COUNT(0), etc. O importante era manter o conteúdo do COUNT o menor possível já que é melhor contar um valor fixo para cada linha do que utilizar o * que supostamente leria a tabela inteira e posteriormente faria a contagem. Como todo aprendiz, nada mais sensato que observar e aprender com os mais experientes. Se alguém experiente me disse que era da forma X, como poderia um aprendiz discordar ?

O tempo passou e fui notando que não só o analista, mas muitos outros analistas, desenvolvedores, etc realmente acreditam que o COUNT(1) é bem mais performático que o COUNT(*) pelo mesmo motivo. O COUNT(1) irá gerar uma coluna com o valor para todas as linhas e fazer a contagem enquanto que o COUNT(*) irá recuperar todas as linhas e todos os campos da tabela (afinal é *) para posteriormente fazer a contagem. Para pequenas tabelas isso é irrelevante, pois, outros fatores como rede, disco, etc vão ocupar boa parte do tempo, já para grandes tabelas, o negócio é utilizar o COUNT(1). Vejo essa afirmação tão forte em alguns lugares que um dia desses me deparei com o seguinte comentário em uma comunidade voltada para tunning de SQL (algumas pequenas adaptações foram feitas para preservar a identidade do autor).

"Após diálogo com o consultor da Empresa Tunning SQL Associates, ficou esclarecido que usar de (1) no lugar de (*) é mais performático. Quando se usa (*) o SGBD XPTO monta as colunas, mesmo não trazendo valor. Já a utilização do (1) faz com que o banco XPTO não monte a estrutura das colunas e sim retornar apenas o valor (1)."

O resto do post tinha os devidos esclarescimentos, mas eu fico imaginando o que será que alguém leigo irá pensar quando ler uma afirmação dessas. É muito factível de acreditar, pois, faz todo o sentido. Vejamos se o que parece fazer sentido realmente está correto. O script abaixo cria uma tabela com 33 colunas e popula a mesma com 10.000 registros. Exagerei em alguns tipos de dados para realmente tornar o registro largo.

– Cria a tabela
CREATE TABLE T (
    ID BIGINT IDENTITY(1,1), VERSAO TIMESTAMP,
    UG UNIQUEIDENTIFIER DEFAULT NEWID(),
    C01 BIGINT, C02 BIGINT, C03 BIGINT, C04 BIGINT, C05 BIGINT,
    C06 BIGINT, C07 BIGINT, C08 BIGINT, C09 BIGINT, C10 BIGINT,
    C11 BIGINT, C12 BIGINT, C13 BIGINT, C14 BIGINT, C15 BIGINT,
    C16 BIGINT, C17 BIGINT, C18 BIGINT, C19 BIGINT, C20 BIGINT,
    C21 BIGINT, C22 BIGINT, C23 BIGINT, C24 BIGINT, C25 BIGINT,
    C26 BIGINT, C27 BIGINT, C28 BIGINT, C29 BIGINT, C30 BIGINT)

– Insere dez mil registros com valores aleatórios
DECLARE @i INT
SET @i = 1

WHILE @i <= 10000
BEGIN
    INSERT INTO
T (
        C01, C02, C03, C04, C05, C06, C07, C08, C09, C10,
        C11, C12, C13, C14, C15, C16, C17, C18, C19, C20,
        C21, C22, C23, C24, C25, C26, C27, C28, C29, C30)
    VALUES (
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()),
        CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID()))
    SET @i = @i + 1
END

Para tornar as coisas realmente interessantes, farei uma consulta com quatro variações (0, 1, ‘X’ e *) para fins comparativos.

– Ativa as medições de tempo e IO
SET STATISTICS TIME ON
SET STATISTICS IO ON

– Retorna todos os registros de 4 formas diferentes
SELECT COUNT(‘X’) FROM T
SELECT COUNT(1) FROM T
SELECT COUNT(0) FROM T
SELECT COUNT(*) FROM T

Após executar o batch duas vezes obtive o seguinte resultado: (é necessário executar duas vezes para que as haja igualdade no cachê de dados e no plano de execução).

Consulta Tempo IO Necessário
COUNT(‘X’) 06ms Table ‘T’. Scan count 1, logical reads 371, physical reads 0
COUNT(1) 10ms Table ‘T’. Scan count 1, logical reads 371, physical reads 0
COUNT(0) 83ms Table ‘T’. Scan count 1, logical reads 371, physical reads 0
COUNT(*) 14ms Table ‘T’. Scan count 1, logical reads 371, physical reads 0

Em termos de IO houve um empate, mas a questão do tempo favorece o uso do COUNT(‘X’) e do COUNT(1). É preciso ter cautela com essa medição, pois, embora pareça, o COUNT(‘X’) não é o mais rápido de todos como demonstra a estatística. Seria muito pouco provável que os tempos dessem exatamente os mesmos, pois, vale lembrar que a execução da consulta levará em conta muitos detalhes de distribuição de recursos como o quantum da CPU e o uso do discos por outras tarefas do sistema operacional. Se pelo menos estivéssemos falando de uma consulta que leva 1ms e outra que leva 1s ou que na média os tempos batessem, poderíamos fazer conclusões, mas apenas por essa curta estatística não é possível afirmar quem é o mais rápido (até porque os tempos podem diferir em outras execuções). Vejamos o plano de execução da consulta:

O que pode ser observado é que as quatro consultas são exatamente as mesmas. Dentro de um mesmo batch, cada uma levou 25% e os custos de IO foram exatamente os mesmos, o que permite (com excelente precisão) afirmar que as consultas são idênticas. Foi necessário ler toda a tabela (Table Scan) para posteriormente fazer a contagem (e isso inclui todas as colunas). Os resultados já permitem desmentir parcialmente o mito de que o COUNT(1) é mais eficaz que o COUNT(*) por exemplo. Alguns pontos ainda merecem ser esclarescidos nesse exemplo.

O primeiro ponto é que de forma nenhuma um banco de dados armazena os dados em um formato estilo Excel ou bloco de notas, ou seja, com as colunas organizadas adequadamente e os registros representando uma linha exata da planilha com a devida quebra. O armazenamento dos dados em um banco de dados normalmente obedece a formatos proprietários e nem de longe está próximo de ser uma planilha na qual as demais colunas pudessem ser simplesmente ignoradas para a contagem.

Outro ponto a se considerar é que os bancos de dados em geral fazem leituras por bloco e não por registros (embora possam impor bloqueios em nível de registro). Os blocos da tabela contém todas as colunas e se ele for requisitado, todas as colunas deverão estar presentes. Afinal não é possível ir ao disco, e recuperar um pedaço do bloco descartando as demais colunas (até porque como disse, o bloco não é uma planilha ou arquivo texto). Essas duas características fazem com que realmente seja impossível que o COUNT(1) seja superior ao COUNT(*) em termos de desempenho tornando inválida a suposição de que qualquer COUNT é mais performático que o COUNT(*).

Ainda que o COUNT(1) contasse uma coluna virtual com o valor 1, seria necessário ler todos os blocos da tabela e posteriormente montar a coluna virtual para efetuar a contagem. Se todas colunas já foram recuperadas do disco e a contagem dos registros já pode ser feita, qual seria a vantagem de ter o passo adicional da montagem da coluna virtual com o valor 1 ? Simplesmente nenhuma. O uso do COUNT(1) ao pé da letra seria inclusive mais lento e por isso que o otimizador de consulta simplesmente o ignora e produz exatamente o mesmo plano que o COUNT(*). Ainda que fosse utilizado algo como COUNT(Coluna), o efeito prático seria o mesmo.

A influência dos índices em expressões do tipo COUNT

Como será que os índices podem influenciar em uma situação desse tipo ? Alguns dirão que se o COUNT for feito sobre a coluna do índice haverá um melhor desempenho. Será que os índices podem de fato desempatar e tornar o COUNT(*) mais lento ? Nada melhor do que um teste prático. O script a seguir cria um índice sobre a coluna ID.

– Cria um índice sobre a coluna ID
CREATE INDEX IX_ID ON T (ID)

Agora que existe um índice sobre a coluna ID, vejamos algumas variações do COUNT e seus resultados.

– Ativa as medições de tempo e IO
SET STATISTICS TIME ON
SET STATISTICS IO ON

– Retorna todos os registros de 4 formas diferentes
SELECT COUNT(‘X’) FROM T
SELECT COUNT(1) FROM T
SELECT COUNT(ID) FROM T
SELECT COUNT(*) FROM T

Após executar duas vezes o seguinte resultado é obtido:

Consulta Tempo IO Necessário
COUNT(‘X’) 41ms Table ‘T’. Scan count 1, logical reads 26, physical reads 0
COUNT(1) 11ms Table ‘T’. Scan count 1, logical reads 26, physical reads 0
COUNT(ID) 11ms Table ‘T’. Scan count 1, logical reads 26, physical reads 0
COUNT(*) 113ms Table ‘T’. Scan count 1, logical reads 26, physical reads 0

Como disse anteriormente, a estatística de tempo é importante, mas ela varia de acordo com alguns fatores e nesse caso ela sozinha não é conclusiva. Vejamos o plano de execução.

O COUNT(0) foi descartado porque o resultado é o mesmo do COUNT(1). Dessa vez, ao invés do COUNT(0) foi utilizada a coluna exatamente utilizada pelo índice e de forma semelhante ao exemplo anterior o resultado foi exatamente o mesmo. Ainda que a coluna ID seja utilizada no COUNT e a mesma possui um índice ela não não apresentou um desempenho superior às demais opções. O plano de execução demonstra que independente de COUNT(1), COUNT(*) ou COUNT(ID), todas as construções utilizaram o índice sobre ID.

Inicialmente poderia pensar-se que o COUNT(ID) deveria ser mais rápido. Um bloco de índice teria somente a coluna ID e alguns ponteiros enquanto um bloco de dados teria não somente a coluna ID como todas as demais colunas da tabela. Assim a contagem com base no ID devem varrer os blocos de índices (26 blocos no total) e as demais construções deveriam varrer os blocos de dados (371 blocos) sendo portanto mais lentas.

Essa tese até faz algum sentido, mas antítese é essencialmente bem simples quando a teste. Se a contagem de entradas nos blocos de índice e nos blocos de dados é exatamente a mesma, qual é o sentido de varrer os blocos de dados ? É bem mais simples ir nos blocos de índice. A contagem de ambas será sempre a mesma, pois, a coluna ID é preenchida em todos os registros e um COUNT no índice ou nos dados é o mesmo. O otimizador então irá preferir ler 26 blocos (Índice) do que 371 blocos (tabela).

O uso do índice funcionou, mas pressupõe que a coluna ID sempre estará preenchida. Será que o raciocínio se mantém para colunas que aceitem valores nulos ? O script abaixo faz uma demonstração

– Elimina o índice sobre ID
DROP INDEX T.IX_ID

– Atualiza 1000 registros tornando nulo C1
UPDATE T SET C01 = NULL WHERE ID <= 1000

– Cria um índice sobre C1
CREATE INDEX IX_ID ON T (C01)

– Retorna todos os registros de 4 formas diferentes
SELECT COUNT(‘X’) FROM T
SELECT COUNT(1) FROM T
SELECT COUNT(C01) FROM T
SELECT COUNT(*) FROM T

Após executar duas vezes (apenas o COUNT) o seguinte resultado é obtido:

Consulta Tempo IO Necessário
COUNT(‘X’) 04ms Table ‘T’. Scan count 1, logical reads 30, physical reads 0
COUNT(1) 16ms Table ‘T’. Scan count 1, logical reads 30, physical reads 0
COUNT(C01) 12ms Table ‘T’. Scan count 1, logical reads 30, physical reads 0
COUNT(*) 163ms Table ‘T’. Scan count 1, logical reads 30, physical reads 0

O tempo dispensa maiores comentários (embora seja tendencioso para condenar o *). O plano de execução é exposto abaixo:

E novamente o IO e o plano foi exatamente o mesmo. Ainda que o índice não seja sobre colunas obrigatórios, a leitura do índice foi realizada por todas as construções e elas "empataram". Todas leram as 30 páginas de índice ao invés de passar por 371 páginas da tabela (o que era esperado, pois, é bem mais eficiente). Ainda que o COUNT(1) não tenha sido superior ao COUNT(*) é necessário atentar-se para um detalhe. O plano de execução tem uma mensagem após a execução do COUNT(C01).

Warning: Null value is eliminated by an aggregate or other SET operation

Esse aviso só apareceu na consulta com a coluna C01 e embora o desempenho tenha sido o "mesmo", a mensagem indica que há valores nulos que foram eliminados durante a consulta. O resultado da consulta é o seguinte:

Embora o plano de execução tenha sido praticamente o mesmo, no caso da contagem com base na coluna do índice, valores nulos foram eliminados. Antes da criação do índice, a coluna C01 tinha 10.000 valores preenchidos assim como a tabela tinha 10.000 linhas. Entretanto, o UPDATE tornou nulo 1.000 desses registros e por isso a contagem retornou 9.000. Afinal existem 10.000 linhas, mas apenas 9.000 IDs. Ainda assim, o uso do índice é utilizado para todas as variações do COUNT. No caso de colunas nulas, há apenas uma etapa posterior que é a eliminação de valores nulos para fazer a contagem. Isso não torna o uso do COUNT(1), COUNT(0) ou COUNT(‘X’) mais ou menos performático que o COUNT(*) visto que ambas as construções não estão sujeitas aos mesmos comportamentos de uma coluna não nula.

Felizmente nessa mesma comunidade, ao final do post, houve um comentário muito sensato:

"Após consultar a obra de Peter Gultuzan (SQL Performance Tuning), constatei que dos 8 maiores SGBDs, entre eles Oracle, DB2 e SqlServer o uso dessas sentenças não apresentou diferencas de performance entre si… Após fazer um teste no banco Z2X não vi nenhum ganho digno de nota (nem mesmo 5%)."

Como podemos perceber, COUNT(1), COUNT(‘X’) ou COUNT(QualquerExpressaoQueNãoSejaUmaColuna) têm exatamente o mesmo comportamento, resultado e desempenho de uma instrução COUNT(*). Quando se utilizam colunas, algumas surpresas podem acontecer conforme demonstrado. O uso do COUNT(*), COUNT(1), etc também pode ter efeitos adversos quando em conjunto com OUTER JOINs. Isso não refere-se ao desempenho, mas aos resultados esperados. Maiores detalhes em:

Piores Práticas – Uso do COUNT(*)
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!538.entry

[ ]s,

Gustavo