Arquivo da categoria: DICAS

Exportando consultas para documentos XML

Publicado em 26/12/2011 | 8 Comentários

Boa Noite Pessoal,

Essa será possivelmente uma das minhas últimas postagens de 2011, então vamos para uma dica rápida. Hoje falarei sobre como exportar uma tabela ou consulta para um documento XML através do BCP e do CLR. Já vi algumas postagens que tratam esse assunto, mas proponho um pouco mais que isso. Demonstrarei a exportação mantendo a identação dos elementos no documento XML. Para começar nada melhor que algumas tabelas de exemplo

— Cria um banco para testes
CREATE DATABASE ExportXML
GO

— Muda o contexto
USE ExportXML
GO

— Cria as tabelas
CREATE TABLE Clientes (
ClienteID INT NOT NULL,
ClienteNome VARCHAR(100) NOT NULL)

CREATE TABLE Contas (
    ClienteID INT NOT NULL,
    ContaID INT NOT NULL,
    ContaAgencia VARCHAR(6) NOT NULL,
    ContaNumero VARCHAR(9) NOT NULL)

CREATE TABLE Lancamentos (
    ClienteID INT NOT NULL,
    ContaID INT NOT NULL,
    Valor SMALLMONEY NOT NULL,
    Tipo CHAR(1) NOT NULL,
    Data DATE NOT NULL)

— Insere os registros
INSERT INTO Clientes VALUES (1,‘Ronaldo’)
INSERT INTO Clientes VALUES (2,‘Adalton’)
INSERT INTO Clientes VALUES (3,‘Daniela’)
INSERT INTO Clientes VALUES (4,‘Roberta’)

INSERT INTO Contas VALUES (1,1,‘3590-4’,‘97532-8’)
INSERT INTO Contas VALUES (1,2,‘3590-4’,‘856413-0’)
INSERT INTO Contas VALUES (1,3,‘3590-4’,‘948766-0’)
INSERT INTO Contas VALUES (2,1,‘1004-9’,‘974322-8’)
INSERT INTO Contas VALUES (2,2,‘1004-9’,‘15649-X’)
INSERT INTO Contas VALUES (3,1,‘2944-0’,‘7561-2’)

INSERT INTO Lancamentos VALUES (1,1,6500,‘C’,‘20111220’)
INSERT INTO Lancamentos VALUES (1,1,1200,‘D’,‘20111221’)
INSERT INTO Lancamentos VALUES (1,1,2500,‘D’,‘20111221’)
INSERT INTO Lancamentos VALUES (1,2,3500,‘C’,‘20111221’)
INSERT INTO Lancamentos VALUES (1,2,2600,‘C’,‘20111221’)
INSERT INTO Lancamentos VALUES (2,1,3200,‘D’,‘20111222’)
INSERT INTO Lancamentos VALUES (2,1,1100,‘D’,‘20111222’)
INSERT INTO Lancamentos VALUES (2,1,1900,‘C’,‘20111222’)
INSERT INTO Lancamentos VALUES (2,2,5800,‘D’,‘20111223’)
INSERT INTO Lancamentos VALUES (2,2,3200,‘D’,‘20111223’)
INSERT INTO Lancamentos VALUES (2,2,9800,‘C’,‘20111223’)

Agora que temos uma massa de testes, segue um consulta para estruturar esses dados em XML.

SELECT
    Cliente.ClienteNome As Nome,Conta.ContaAgencia As Agencia,
    Conta.ContaNumero As Numero,Lancamento.Data As Data,
    Lancamento.Valor, Lancamento.Tipo
FROM Clientes As Cliente
    LEFT OUTER JOIN Contas As Conta ON Cliente.ClienteID = Conta.ClienteID
    LEFT OUTER JOIN Lancamentos As Lancamento
        ON Conta.ClienteID = Lancamento.ClienteID AND Conta.ContaID = Lancamento.ContaID
FOR XML AUTO, ROOT(‘Lancamentos’)

Posto apenas um parte do documento XML produzido com esse comando:

Para facilitar os trabalhos, vou encapsultar a instrução SELECT em uma View.

CREATE VIEW vLancamentoXML (ResultadoXML) As
SELECT
    Cliente.ClienteNome As Nome,Conta.ContaAgencia As Agencia,
    Conta.ContaNumero As Numero,Lancamento.Data As Data,
    Lancamento.Valor, Lancamento.Tipo
FROM Clientes As Cliente
    LEFT OUTER JOIN Contas As Conta ON Cliente.ClienteID = Conta.ClienteID
    LEFT OUTER JOIN Lancamentos As Lancamento
        ON Conta.ClienteID = Lancamento.ClienteID AND Conta.ContaID = Lancamento.ContaID
FOR XML AUTO, ROOT(‘Lancamentos’)

Agora façamos uma tentativa de exporar esse conteúdo para um arquivo através do BCP. A instrução abaixo em um PROMPT de comando é capaz de fazê-lo.

BCP “ExportXML.dbo.vLancamentoXML” out “C:\Lancamentos.xml” -T -c

Eis uma parte do resultado do arquivo em XML

O documento XML foi criado e pode ser plenamente utilizado, pois, é um XML bem formado, mas a identação foi completamente perdida na geração do documento. Se esse XML for processado por uma aplicação, não faz diferença ele estar identado ou não, mas se uma pessoa for abrí-lo, a falta de identação pode ser um grande inconviente.

Uma vez que o BCP foi projetado para exportar texto e não XML, não há parâmetros que possam fazer a identação ficar correta. Então precisaremos dar um jeito do texto já ser exportado de forma identada. Criei aqui uma função capaz de fazê-lo.

CREATE FUNCTION dbo.RetornaXMLGRID (@XML XML)
RETURNS @Registros TABLE (Registro VARCHAR(MAX))
As
BEGIN
    DECLARE @strXML VARCHAR(MAX)
    SET @strXML = CAST(@XML As VARCHAR(MAX))
    SET @strXML = REPLACE(@strXML,‘<‘,‘+|’)
    SET @strXML = REPLACE(@strXML,‘<‘,‘-|’)

DECLARE @T TABLE (
Pos INT IDENTITY(1,1), Elemento VARCHAR(1000))

    INSERT INTO @T SELECT
        node.value(N’fn:local-name(.[1])’, N’varchar(1000)’)
    FROM @xml.nodes(N’//*’) T(Node)

    ;WITH Niveis (Elemento, Nivel) As (
        SELECT Elemento, MIN(Pos)
        FROM @T GROUP BY Elemento)

    DELETE FROM @T
    FROM @T As T
    INNER JOIN Niveis As N ON
        T.Elemento = N.Elemento AND T.Pos > N.Nivel

    DECLARE @i INT = 0
    DECLARE @f INT = (SELECT MAX(Pos) FROM @T)
    DECLARE @Elemento VARCHAR(1000) = ”

    WHILE @i <= @f – 1
    BEGIN
        SET @Elemento = (
            SELECT Elemento FROM @T WHERE Pos = @i + 1)

        SET @strXML = REPLACE(@strXML,‘+|’ + @Elemento +
‘ ‘,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|’ + @Elemento + ‘ ‘)
        SET @strXML = REPLACE(@strXML,‘+|’ + @Elemento + ‘-|’,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|’ + @Elemento + ‘-|’)
        SET @strXML = REPLACE(@strXML,‘+|’ + @Elemento + ‘/-|‘,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|’ + @Elemento + ‘/-|’)
        SET @strXML = REPLACE(@strXML,‘+|/’ + @Elemento + ‘-|’,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|/’ + @Elemento + ‘-|’)
        SET @i += 1
    END

    SET @strXML = REPLACE(@strxml,CHAR(10),‘</e><e>’)
    SET @strXML = ‘<E><e>’ + @strXML + ‘</e></E>’

    SET @XML = CAST(@strXML As XML)

    INSERT INTO @Registros
    SELECT REPLACE(REPLACE(E.e.value(‘.’,‘VARCHAR(MAX)’),‘+|’,‘<‘),‘-|’,‘>’)
    FROM @XML.nodes(‘/E/e’) E(e)

    DELETE FROM @Registros WHERE Registro = ”

    RETURN
END

Agora tentemos o BCP novamente com algumas adaptações:

BCP “SELECT * FROM dbo.RetornaXMLGRID((SELECT ResultadoXML FROM vLancamentoXML))” queryout “C:\Lancamentos.xml” -T -c -d ExportXML

O comando SELECT ResultaadoXML FROM vLancamentoXML é apenas para retornar um texto (ou um tipo XML) no formato de um documento XML. É possível substituir essa parte do comando por qualquer consulta XML. O documento agora veio formatado após abrir o arquivo XML:

Um teste para garantir que o XML só precisa ser váido por ser feito com outra consulta:

SELECT * FROM dbo.RetornaXMLGRID((
SELECT db.name As banco, t.name As tabela, c.name As coluna FROM sys.databases As db INNER JOIN sys.tables As t ON db.database_id = db_id() INNER JOIN sys.columns As c ON t.object_id = c.object_id for xml auto, elements))

Parte do resultado é exibido abaixo:

<db>
<banco>ExportXML
</banco>
<t>
   <tabela>Clientes
   </tabela>
    <c>
     <coluna>ClienteID
     </coluna>
    </c>
    <c>
     <coluna>ClienteNome
     </coluna>
    </c>
</t>
<t>
   <tabela>Contas
   </tabela>
    <c>
     <coluna>ClienteID
     </coluna>

Acho que já são visíveis algumas limitações desse método. Primeiro porque com tantas conversões, essa alternativa não será escalável quando houver documentos XML muito grandes. Segundo, porque se houver elementos com o mesmo nome pertencentes a níveis diferentes, essa função também irá falhar. Isso torna claro que o T-SQL é poderoso, mas não foi feito para resolver todos os problemas. Tentemos uma abordagem com o CLR para esse mesmo fim.

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

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void UspGerarArquivoXML(SqlString cmdSQL, SqlString local)
    {
        SqlConnection sc = new SqlConnection(“Context Connection=true”);
        SqlCommand sCmd = new SqlCommand(cmdSQL.ToString(), sc);

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

// Declara um objeto XmlReader
XmlReader xr = sCmd.ExecuteXmlReader();

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

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

// Inicia o documento
xd.Load(xr);

        // Salva o documento
        xd.Save(local.ToString());
    }
};

Agora que o código em C# está pronto, basta colocá-lo em uma pasta qualquer (C:\SQLCLR\UspGeraArquivoXML.cs) e compilá-lo com o comando de PROMPT

“C:\Windows\Microsoft.NET\Framework\V2.0.50727\csc.exe” /out:C:\SQLCLR\UspGeraArquivoXML.dll /target:library C:\SQLCLR\UspGeraArquivoXML.cs

Os próximos passos são habilitar o CLR e importar o ASSEMBLY:

EXEC sp_configure ‘clr enabled’,1
GO

RECONFIGURE WITH OVERRIDE
GO

USE ExportXML
GO

ALTER DATABASE ExportXML SET TRUSTWORTHY ON

CREATE ASSEMBLY ExportarArquivosXML
FROM ‘C:\SQLCLR\UspGeraArquivoXML.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

Agora que o ASSEMBLY foi gerado, basta criar a procedure e chamá-la:

— Cria a stored procedure
CREATE PROCEDURE UspGerarArquivoXML
@cmdSQL NVARCHAR(4000),
@local NVARCHAR(4000)
As
EXTERNAL NAME [ExportarArquivosXML].[StoredProcedures].[UspGerarArquivoXML]
GO

Por fim, podemos executar a SP com a consulta e o local do arquivo (é importante que a consulta tenha o nó ROOT e que o local tenha as devidas permissões).

— Exporta o Resultado para XML
EXEC dbo.UspGerarArquivoXML
    @cmdSQL = ‘
    SELECT
        Cliente.ClienteNome As Nome,Conta.ContaAgencia As Agencia,
        Conta.ContaNumero As Numero,Lancamento.Data As Data,
        Lancamento.Valor, Lancamento.Tipo
    FROM Clientes As Cliente
        LEFT OUTER JOIN Contas As Conta ON Cliente.ClienteID = Conta.ClienteID
        LEFT OUTER JOIN Lancamentos As Lancamento
        ON Conta.ClienteID = Lancamento.ClienteID AND Conta.ContaID = Lancamento.ContaID
        FOR XML AUTO, ROOT(”Lancamentos”)’,
    @local = ‘C:\SQLCLR\LancamentosCLR.xml’

O documento já vem identado, mas visivelmente o esforço é muito menor do que escrever código T-SQL para isso.

[ ]s,

Gustavo

8 Comentários

Publicado em DICAS

Com a tag BCP, CLR, FOR XML, SQL Server, XML

Mapeando dependências de objetos para colunas

Publicado em 28/10/2011 | Deixe um comentário

Boa Noite Pessoal,

Hoje vi no fórum de SQL Server da Microsoft uma necessidade relativamente comum que é a identificação de que objetos referenciam determinadas colunas de determinadas tabelas. É bem comum perguntarmos sobre essa dependências especialmente quando precisamos eliminar ou alterar uma ou outra coluna e sabemos que essas colunas são referenciadas por diversas views, procedures, functions, etc. O melhor para evitar tais situações é usar a opção SCHEMA BINDING sempre que possível, pois, ela evitará que uma ou outra coluna seja excluída e prejudique os objetos que delas dependam. É uma opção mais segura, mas ainda assim, para uma mudança é necessário mapear e isso o SCHEMA BINDING não faz.

Há muito tempo atrás, publiquei os artigos “Mapeando dependências entre tabelas” e “Mapeando dependências entre objetos” que trata sobre dependências com compatibilidade para o SQL Server 2005 (no 2008, os scripts funcionam, mas há outros objetos mais diretos). Ainda assim, não me atentei para uma necessidade de mapeamento para colunas. Vejamos então como podemos fazer isso:

CREATE TABLE Categorias (
    CodigoCategoria INT NOT NULL,
    NomeCategoria VARCHAR(15) NOT NULL,
    Descricao VARCHAR(MAX) NULL
    CONSTRAINT PK_Categorias PRIMARY KEY (CodigoCategoria))

CREATE TABLE Produtos (
    CodigoProduto INT NOT NULL,
    NomeProduto VARCHAR(40) NOT NULL,
    CodigoFornecedor INT NULL,
    CodigoCategoria INT NULL,
    QuantidadePorUnidade VARCHAR(25) NULL,
    PrecoUnitario MONEY NULL,
    UnidadesEstoque SMALLINT NULL,
    UnidadesPedidas SMALLINT NULL,
    NivelDeReposicao SMALLINT NULL,
    Descontinuado BIT NOT NULL,
    CONSTRAINT PK_Produtos PRIMARY KEY (CodigoProduto))

CREATE TABLE DetalhesPedido (
    NumeroPedido INT NOT NULL,
    CodigoProduto INT NOT NULL,
    PrecoUnitario MONEY NOT NULL,
    Quantidade SMALLINT NOT NULL,
    Desconto REAL NOT NULL,
    CONSTRAINT PK_DetalhesPedido PRIMARY KEY (NumeroPedido,CodigoProduto))

CREATE TABLE Pedidos (
    NumeroPedido INT NOT NULL,
    CodigoCliente INT NULL,
    CodigoFuncionario INT NULL,
    DataPedido DATE NULL,
    DataEntrega DATE NULL,
    DataEnvio DATE NULL,
    Frete MONEY NULL,
    CONSTRAINT PK_Pedidos PRIMARY KEY (NumeroPedido))

CREATE TABLE Clientes (
    CodigoCliente INT NOT NULL,
    NomeEmpresa VARCHAR(40) NOT NULL,
    NomeContato VARCHAR(30) NULL,
    CargoContato VARCHAR(30) NULL,
    Endereco VARCHAR(60) NULL,
    Cidade VARCHAR(15) NULL,
    Regiao VARCHAR(15) NULL,
    CEP CHAR(8) NULL,
    Pais VARCHAR(15) NULL,
    Telefone VARCHAR(24) NULL,
    Fax VARCHAR(24) NULL,
CONSTRAINT PK_Clientes PRIMARY KEY (CodigoCliente))

Agora que já criamos algumas tabelas, o próximo passo é criar algumas views, procedures, functions e triggers para criar o mapeamento dos objetos e das colunas desses objetos.

CREATE VIEW vProdutos As
SELECT NomeCategoria, NomeProduto
FROM Categorias As C
INNER JOIN Produtos As P ON C.CodigoCategoria = P.CodigoCategoria
GO

CREATE FUNCTION dbo.CalculaTotalPedido (@NumeroPedido INT)
RETURNS MONEY
As
BEGIN
DECLARE @Ret MONEY
SET @Ret = (SELECT Frete FROM Pedidos WHERE NumeroPedido = @NumeroPedido)
SET @Ret = @Ret + (
SELECT SUM(Quantidade * PrecoUnitario * (1 – Desconto))
FROM DetalhesPedido WHERE NumeroPedido = @NumeroPedido)

RETURN (@Ret)
END
GO

CREATE PROCEDURE UspRetornaClientesPedidos
As
SELECT NomeEmpresa, NomeContato, Cidade, Pais, CEP
FROM Clientes As C
LEFT OUTER JOIN Pedidos As P ON C.CodigoCliente = P.CodigoCliente
GO

CREATE TRIGGER trgEstoque ON DetalhesPedido
FOR INSERT, UPDATE, DELETE
As
BEGIN

    — Efetua os incrementos
    UPDATE Produtos SET UnidadesEstoque += D.Quantidade
    FROM Produtos As P
    INNER JOIN DELETED As D ON P.CodigoProduto = D.CodigoProduto

    — Efetua os decrementos
    UPDATE Produtos SET UnidadesEstoque -= I.Quantidade
    FROM Produtos As P
    INNER JOIN INSERTED As I ON P.CodigoProduto = I.CodigoProduto

END

Visualmente podemos ver que a view, a function, a procedure e a trigger fazem referência para algumas colunas de algumas triggers. Utilizando a view de catálogo sys.sql_dependencies podemos mapear as dependências entre os objetos.

SELECT
OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
OBJECT_NAME(object_id) As Objeto
FROM sys.sql_dependencies
ORDER BY ObjetoReferenciado

O resultado de fato mostra as dependências:

Objeto Referenciado	Objeto
Categorias	vProdutos
Categorias	vProdutos
Clientes	UspRetornaClientesPedidos
Clientes	UspRetornaClientesPedidos
Clientes	UspRetornaClientesPedidos
Clientes	UspRetornaClientesPedidos
Clientes	UspRetornaClientesPedidos
Clientes	UspRetornaClientesPedidos
DetalhesPedido	CalculaTotalPedido
DetalhesPedido	CalculaTotalPedido
DetalhesPedido	CalculaTotalPedido
DetalhesPedido	CalculaTotalPedido
Pedidos	CalculaTotalPedido
Pedidos	UspRetornaClientesPedidos
Pedidos	CalculaTotalPedido
Produtos	trgEstoque
Produtos	vProdutos
Produtos	vProdutos
Produtos	trgEstoque

Podemos ver todos os mapeamentos, mas o fato de haver duplicidades é no mínimo intrigante. Usar o DISTINCT resolveria isso fácil, mas se há repetição há alguma razão para isso. Vamos pesquisar algumas outras colunas que provocam essa repetição:

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(object_id) As Objeto,
    column_id, referenced_minor_id
FROM sys.sql_dependencies
ORDER BY ObjetoReferenciado

Eis o resultado:

Objeto Referenciado	Objeto	Column_ID	Referenced_minor_id
Categorias	vProdutos	0	1
Categorias	vProdutos	0	2
Clientes	UspRetornaClientesPedidos	0	1
Clientes	UspRetornaClientesPedidos	0	2
Clientes	UspRetornaClientesPedidos	0	3
Clientes	UspRetornaClientesPedidos	0	6
Clientes	UspRetornaClientesPedidos	0	8
Clientes	UspRetornaClientesPedidos	0	9
DetalhesPedido	CalculaTotalPedido	0	1
DetalhesPedido	CalculaTotalPedido	0	3
DetalhesPedido	CalculaTotalPedido	0	4
DetalhesPedido	CalculaTotalPedido	0	5
Pedidos	CalculaTotalPedido	0	1
Pedidos	UspRetornaClientesPedidos	0	2
Pedidos	CalculaTotalPedido	0	8
Produtos	trgEstoque	0	1
Produtos	vProdutos	0	2
Produtos	vProdutos	0	4
Produtos	trgEstoque	0	7

O column_id não ajudou muito, mas a coluna Referenced_minor_id já é uma boa pista, pois, é visível que ela provoca a duplicidade. Se olharmos o Books OnLine, essa coluna é mais específica (Referenced_minor_id – 1, referenced_minor_id is a column ID; or if not a column, it is 0.). Só precisamos então fazer um JOIN com a sys.columns e um filtro para garantir que estamos sempre falando de colunas.

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(D.object_id) As Objeto,
    C.name As Coluna
FROM sys.sql_dependencies As D
INNER JOIN sys.columns As C ON
    D.referenced_major_id = C.object_id AND D.referenced_minor_id = C.column_id
WHERE class_desc = ‘OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND’
ORDER BY ObjetoReferenciado

Eis o resultado:

Objeto Referenciado	Objeto	Coluna
Categorias	vProdutos	CodigoCategoria
Categorias	vProdutos	NomeCategoria
Clientes	UspRetornaClientesPedidos	CodigoCliente
Clientes	UspRetornaClientesPedidos	NomeEmpresa
Clientes	UspRetornaClientesPedidos	NomeContato
Clientes	UspRetornaClientesPedidos	Cidade
Clientes	UspRetornaClientesPedidos	CEP
Clientes	UspRetornaClientesPedidos	Pais
DetalhesPedido	CalculaTotalPedido	NumeroPedido
DetalhesPedido	CalculaTotalPedido	PrecoUnitario
DetalhesPedido	CalculaTotalPedido	Quantidade
DetalhesPedido	CalculaTotalPedido	Desconto
Pedidos	CalculaTotalPedido	NumeroPedido
Pedidos	UspRetornaClientesPedidos	CodigoCliente
Pedidos	CalculaTotalPedido	Frete
Produtos	trgEstoque	CodigoProduto
Produtos	vProdutos	NomeProduto
Produtos	vProdutos	CodigoCategoria
Produtos	trgEstoque	UnidadesEstoque

Podemos ver que todas as colunas foram retornadas conforme o esperado. Não somente as colunas usadas em SELECTs e UPDATEs, mas também as colunas utilizadas em JOINs (e como isso faz diferença). Será que realmente funciona para outros casos ? Vejamos um objeto com a opção SCHEMA_BINDING (requerida em views indexadas inclusive).

CREATE VIEW vProdutos2
WITH SCHEMABINDING
As
SELECT NomeCategoria, NomeProduto
FROM dbo.Categorias As C
INNER JOIN dbo.Produtos As P ON C.CodigoCategoria = P.CodigoCategoria
GO

Se tentarmos a consulta anterior, infelizmente a view vProdutos2 não será retornada. A razão é que o vínculo é com schema binding e aí precisamos alterar o predicado.

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(D.object_id) As Objeto,
    C.name As Coluna
FROM sys.sql_dependencies As D
INNER JOIN sys.columns As C ON
    D.referenced_major_id = C.object_id AND D.referenced_minor_id = C.column_id
WHERE class_desc IN (
    ‘OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND’,
    ‘OBJECT_OR_COLUMN_REFERENCE_SCHEMA_BOUND’)
ORDER BY ObjetoReferenciado

Eis o resultado:

Objeto Referenciado	Objeto	Coluna
Categorias	vProdutos	CodigoCategoria
Categorias	vProdutos	NomeCategoria
Categorias	vProdutos2	CodigoCategoria
Categorias	vProdutos2	NomeCategoria
Clientes	UspRetornaClientesPedidos	CodigoCliente
Clientes	UspRetornaClientesPedidos	NomeEmpresa
Clientes	UspRetornaClientesPedidos	NomeContato
Clientes	UspRetornaClientesPedidos	Cidade
Clientes	UspRetornaClientesPedidos	CEP
Clientes	UspRetornaClientesPedidos	Pais
DetalhesPedido	CalculaTotalPedido	NumeroPedido
DetalhesPedido	CalculaTotalPedido	PrecoUnitario
DetalhesPedido	CalculaTotalPedido	Quantidade
DetalhesPedido	CalculaTotalPedido	Desconto
Pedidos	CalculaTotalPedido	NumeroPedido
Pedidos	UspRetornaClientesPedidos	CodigoCliente
Pedidos	CalculaTotalPedido	Frete
Produtos	trgEstoque	CodigoProduto
Produtos	vProdutos	NomeProduto
Produtos	vProdutos	CodigoCategoria
Produtos	trgEstoque	UnidadesEstoque
Produtos	vProdutos2	CodigoProduto
Produtos	vProdutos2	NomeProduto

Agora sim funcionou. Para mapeamentos simples, essa view é uma mão na roda. Fica aí a dica.

[ ]s,

Gustavo

Deixe um comentário

Publicado em DICAS

Com a tag SQL Server; Mapeamentos ; Dependências ; Colunas

Exclusões em cascata e auto-referência no SQL Server

Publicado em 22/01/2011 | 4 Comentários

Oi Pessoal,

Ainda estou me acostumando com a mudança do blog (algumas coisas melhoraram muito, mas estou sentindo muita falta de outras). Iniciando o primeiro post do ano de 2011 (e o primeiro que não foi importado do Live), vou apontar uma rápida solução para uma thread que vi no fórum do MSDN. A thread referia-se a impossibilidade de utilizar a exclusão em cascata para auto-relacionamentos. Admito que nunca precisei fazer algo desse tipo, pois, normalmente a exclusão em cascata não é utilizada e quase sempre tabelas que se auto-relacionam são tabelas de referência e não são apagadas. Ainda assim, vejamos o que pode ser feito nessa situação.

— Cria uma tabela de Empregados
CREATE TABLE Empregados (
    EmpregadoID INT NOT NULL,
    EmpregadoNome VARCHAR(50) NOT NULL,
    SuperiorID INT NULL)

— Cria as Constraints
ALTER TABLE Empregados ADD CONSTRAINT PK_Empregado PRIMARY KEY (EmpregadoID)
ALTER TABLE Empregados ADD CONSTRAINT FK_Empregado_Empregado
FOREIGN KEY (SuperiorID) REFERENCES Empregados (EmpregadoID)

— Insere alguns registros
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (1,‘Sálvio’,NULL)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (2,‘Hélio’,1)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (3,‘Dênis’,2)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (4,‘Júnior’,2)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (5,‘Marcos’,3)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (6,‘Bruno’,3)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (7,‘Paulo’,5)

Podemos ver as seguintes relações hierárquicas:

Sálvio -> Hélio
Sálvio -> Hélio -> Dênis
Sálvio -> Hélio -> Dênis -> Marcos
Sálvio -> Hélio -> Dênis -> Marcos -> Paulo
Sálvio -> Hélio -> Júnior
Hélio -> Dênis
Hélio -> Dênis -> Marcos
Hélio -> Dênis -> Marcos -> Paulo
Hélio -> Júnior
Dênis -> Marcos
Dênis -> Marcos -> Paulo
Marcos -> Paulo

Agora que os registros estão populados, vamos tentar efetuar uma exclusão:

— Tenta excluir o registro do empregado Hélio
DELETE FROM Empregados WHERE EmpregadoID = 2

Como era de se esperar, a existência de subordinados ao empregado Hélio representadas pelo auto-relacionamento gerou um erro na exclusão:

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint “FK_Empregado_Empregado”. The conflict occurred in database “tempdb”, table “dbo.Empregados”, column ‘SuperiorID’.
The statement has been terminated.

Admitindo que a exclusão de um empregado deva excluir também os seus subordinados, poderíamos ativar a exclusão em cascata.

— Remove a constraint FK anterior
ALTER TABLE Empregados DROP CONSTRAINT FK_Empregado_Empregado

— Recria a constraint com a exclusão em cascata
ALTER TABLE Empregados ADD CONSTRAINT FK_Empregado_Empregado
FOREIGN KEY (SuperiorID) REFERENCES Empregados (EmpregadoID)
ON DELETE CASCADE

A exclusão da constraint ocorre com sucesso, mas a criação com a exclusão em cascata e a auto-referência gera um erro:

Msg 1785, Level 16, State 0, Line 2
Introducing FOREIGN KEY constraint ‘FK_Empregado_Empregado’ on table ‘Empregados’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

No SQL Server, o recurso de exclusão em cascata possui algumas restrições e uma delas é não poder ser utilizado para constraints que envolvam o auto-relacionamento. Não cheguei a testar esse comportamento em outros SGBDs como o ORACLE, DB2 e afins, mas entendo que essa restrição do SQL Server tenha certo fundamento. A exclusão em cascata com o auto-relacionamento pode levar a situações complexas de gerenciar bem como efeitos catastróficos. Excluir o registro do Sálvio por exemplo, incorreria em praticamente limpar a tabela de empregados se a exclusão em cascata funcionasse com o auto-relacionamento. Outra situação envolve referências circulares. Digamos que não estejamos tratando de uma relação de subordinação, mas de indicações ou até um amigo oculto. Em tais situações é bem fácil de acontecer uma referência circular do tipo K -> X -> Y -> Z -> K. A exclusão em cascata em uma situação dessas iria provocar um loop infinito.

Admitindo-se que não haja um ciclo, como fazer então para excluir em cascata registro de uma tabela que possui auto-relacionamento se a FK não dá essa possibilidade ? A solução que me veio a cabeça para resolver a situação é combinar a chave estrangeira com uma trigger de Instead Of. Só que há mais um detalhe, se a FK irá bloquear as exclusões que a violem, é preciso excluir os registros na ordem correta. Para fazer isso utilizei-me de uma CTE recursiva.

— Captura o ID do empregado Hélio
DECLARE @EmpregadoID INT
SET @EmpregadoID = (
SELECT EmpregadoID FROM Empregados
WHERE EmpregadoNome = ‘Hélio’)

— Lista todos os subordinados ao Hélio com o nível
;WITH Emp (EmpregadoID, EmpregadoNome, SuperiorID, Nivel)
As (
    SELECT EmpregadoID, EmpregadoNome, SuperiorID, 1 As Nivel
    FROM Empregados
    WHERE EmpregadoID = @EmpregadoID
    UNION ALL
    SELECT E.EmpregadoID, E.EmpregadoNome, E.SuperiorID, Nivel + 1
    FROM Empregados As E
    INNER JOIN Emp ON E.SuperiorID = Emp.EmpregadoID)

— Recupera os subordinados ao Hélio
SELECT EmpregadoID, EmpregadoNome, SuperiorID, Nivel FROM Emp

EmpregadoID	EmpregadoNome	SuperiorID	Nivel
2	Hélio	1	1
3	Dênis	2	2
4	Júnior	2	2
5	Marcos	3	3
6	Bruno	3	3
7	Paulo	5	4

Agora ficou bem mais fácil. A CTE recursiva retorna o registro e todos os subordinados. Para evitar violação de chave estrangeira, basta excluir do maior nível para o menor nível. Adicionei a lógica dentro da trigger.

— Cria uma trigger Instead Of Delete
CREATE TRIGGER trgIDEmpregados ON Empregados
INSTEAD OF DELETE
As

— Cria uma tabela temporária para armazenar os registros a excluir
DECLARE @Empregados TABLE (EmpregadoID INT, Nivel INT)

— Monta a CTE recursiva
;WITH Emp (EmpregadoID, SuperiorID, Nivel)
As (
    SELECT EmpregadoID, EmpregadoNome, SuperiorID, 1 As Nivel
    FROM Empregados
    WHERE EmpregadoID IN (SELECT EmpregadoID FROM Deleted)
    UNION ALL
    SELECT E.EmpregadoID, E.SuperiorID, Nivel + 1
    FROM Empregados As E
    INNER JOIN Emp ON E.SuperiorID = Emp.EmpregadoID)

— Insere os IDs dos registros a excluir
INSERT INTO @Empregados SELECT EmpregadoID, Nivel FROM Emp
ORDER BY Nivel DESC

— Exclui os registros
DECLARE @MaiorNivel INT
SET @MaiorNivel = (SELECT MAX(Nivel) FROM @Empregados)

WHILE EXISTS (SELECT * FROM @Empregados)
BEGIN
    — Exclui os empregados
    DELETE FROM Empregados WHERE EmpregadoID IN
        (SELECT EmpregadoID FROM @Empregados WHERE Nivel = @MaiorNivel)

— Exclui os empregados da tabela temporária
DELETE FROM @Empregados WHERE Nivel = @MaiorNivel

— Decrementa um nível
SET @MaiorNivel = @MaiorNivel – 1

END

Agora vamos ver alguns testes:

— Exclui o Hélio
— Só deve sobrar o Sálvio
BEGIN TRAN
DELETE FROM Empregados WHERE EmpregadoID = 2
SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

— Exclui o Dênis
— Só deve sobrar o Sálvio, Hélio e Júnior
BEGIN TRAN
DELETE FROM Empregados WHERE EmpregadoID = 3
SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

— Exclui o Marcos e o Júnior
— Só deve sobrar o Sálvio, Dênis e Bruno
BEGIN TRAN
DELETE FROM Empregados WHERE EmpregadoID IN (3,5)
SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

— Exclui o Sálvio
— Não sobra ninguém
BEGIN TRAN
DELETE FROM Empregados WHERE EmpregadoID = 1
SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

Como podemos ver, a trigger inclusive já é orientada a conjuntos permitindo múltiplas exclusões. Confesso que tirando a thread eu nunca tinha tido tal necessidade. Quem precisar é só adaptar.

[ ]s,

Gustavo

4 Comentários

Publicado em DICAS

Utilizando Common Table Expressions (CTEs) para estruturação de MENUs – Parte II

Publicado em 13/10/2010 | Deixe um comentário

Boa Noite Pessoal,

Na parte I do artigo, demonstrei como utilizar as recursive CTEs para exibir a montagem correta de um plano de contas. A maior dificuldade é realmente garantir a ordem correta de exibição dos itens, uma vez que itens posteriores podem ser inseridos no meio das contas já existentes. Minha solução foi baseada na materialização dos caminhos até a conta, motando assim uma coluna para ordenação, mas hoje pensei que talvez existam outras necessidades. Vou recolocar o plano para mostrar algumas outras consultas interessantes.

— Cria uma tabela de Plano de Contas
CREATE TABLE PlanoContas (
    ID INT NOT NULL,
    IDSup INT NULL,
    Descricao VARCHAR(50))

— Adiciona as constraints
ALTER TABLE PlanoContas ADD CONSTRAINT PK_Plano PRIMARY KEY (ID)

ALTER TABLE PlanoContas ADD CONSTRAINT FK_Plano FOREIGN KEY (IDSup)
REFERENCES PlanoContas (ID)

— Insere os registros no Plano de Contas
INSERT INTO PlanoContas VALUES (01,NULL,‘Ativo’)
INSERT INTO PlanoContas VALUES (02,01,‘Ativo Circulante’)
INSERT INTO PlanoContas VALUES (03,02,‘Disponibilidades’)
INSERT INTO PlanoContas VALUES (04,03,‘Caixa’)
INSERT INTO PlanoContas VALUES (05,04,‘Caixa Geral’)
INSERT INTO PlanoContas VALUES (06,03,‘Contas Bancárias’)
INSERT INTO PlanoContas VALUES (07,06,‘Conta Corrente’)
INSERT INTO PlanoContas VALUES (08,06,‘Conta Poupança’)
INSERT INTO PlanoContas VALUES (09,02,‘Créditos’)
INSERT INTO PlanoContas VALUES (10,09,‘Duplicatas’)
INSERT INTO PlanoContas VALUES (11,10,‘Duplicatas Descontadas’)
INSERT INTO PlanoContas VALUES (12,10,‘Duplicatas a Receber’)
INSERT INTO PlanoContas VALUES (13,06,‘Conta Investimento’)

;WITH Res (ID, IDSup, Descricao, Caminho) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’
    FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao, Caminho FROM Res ORDER BY Caminho

ID	Descricao	Caminho
01	Ativo	0001.
02	Ativo -> Ativo Circulante	0001.0002.
03	Ativo -> Ativo Circulante -> Disponibilidades	0001.0002.0003.
04	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa	0001.0002.0003.0004.
05	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral	0001.0002.0003.0004.0005.
06	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias	0001.0002.0003.0006.
13	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente	0001.0002.0003.0006.0007.
07	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança	0001.0002.0003.0006.0008.
08	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento	0001.0002.0003.0006.0013.
09	Ativo -> Ativo Circulante -> Créditos	0001.0002.0009.
10	Ativo -> Ativo Circulante -> Créditos -> Duplicatas	0001.0002.0009.0010.
11	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas	0001.0002.0009.0010.0011.
12	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber	0001.0002.0009.0010.0012.

A montagem da coluna "Caminho" foi necessária para garantir a ordem correta, fazendo assim com que a conta investimento (ID 13) ficasse logo abaixo das contas correnta e poupança de acordo com a ordem de cadastro. Será no entanto, que a ordem de cadastro é a ordem desejada ? E se fosse necessário exibir os itens pode ordem alfabética ? A consulta abaixo mostra como fazê-lo:

SELECT
    ID, Descricao, Caminho,
    LEFT(Caminho,LEN(Caminho)-5) As CaminhoSuperior
FROM Res
ORDER BY
    CaminhoSuperior, Descricao

ID	Descricao	Caminho	CaminhoSuperior
01	Ativo	0001.
02	Ativo -> Ativo Circulante	0001.0002.	0001.
03	Ativo -> Ativo Circulante -> Disponibilidades	0001.0002.0003.	0001.0002.
04	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa	0001.0002.0003.0004.	0001.0002.0003.
05	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral	0001.0002.0003.0004.0005.	0001.0002.0003.0004.
06	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias	0001.0002.0003.0006.	0001.0002.0003.
13	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente	0001.0002.0003.0006.0007.	0001.0002.0003.0006.
07	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança	0001.0002.0003.0006.0008.	0001.0002.0003.0006.
08	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento	0001.0002.0003.0006.0013.	0001.0002.0003.0006.
09	Ativo -> Ativo Circulante -> Créditos	0001.0002.0009.	0001.0002.
10	Ativo -> Ativo Circulante -> Créditos -> Duplicatas	0001.0002.0009.0010.	0001.0002.0009.
11	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber	0001.0002.0009.0010.0012.	0001.0002.0009.0010.
12	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas	0001.0002.0009.0010.0011.	0001.0002.0009.0010.

A montagem da coluna "CaminhoSuperior" reflete exatamente o caminho até o "pai" de uma determinada conta. Uma vez que determinadas contas tenham o mesmo pai (e por consequência o mesmo valor para a coluna "CaminhoSuperior"), basta ordená-las pela descrição da conta exatamente da mesma forma que o ORDER BY realiza. É possível evitar o uso do LEN, através de uma pequena adaptação na CTE aproveitando a montagem da coluna "Caminho" antes da recursividade:

;WITH Res (ID, IDSup, Descricao, Caminho, CaminhoSuperior) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’,
        CAST(” As VARCHAR(MAX))
    FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’, Caminho
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT
ID, Descricao, Caminho, CaminhoSuperior
FROM Res
ORDER BY
CaminhoSuperior, Descricao

ID	Descricao	Caminho	CaminhoSuperior
01	Ativo	0001.
02	Ativo -> Ativo Circulante	0001.0002.	0001.
03	Ativo -> Ativo Circulante -> Disponibilidades	0001.0002.0003.	0001.0002.
04	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa	0001.0002.0003.0004.	0001.0002.0003.
05	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral	0001.0002.0003.0004.0005.	0001.0002.0003.0004.
06	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias	0001.0002.0003.0006.	0001.0002.0003.
13	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente	0001.0002.0003.0006.0007.	0001.0002.0003.0006.
07	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança	0001.0002.0003.0006.0008.	0001.0002.0003.0006.
08	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento	0001.0002.0003.0006.0013.	0001.0002.0003.0006.
09	Ativo -> Ativo Circulante -> Créditos	0001.0002.0009.	0001.0002.
10	Ativo -> Ativo Circulante -> Créditos -> Duplicatas	0001.0002.0009.0010.	0001.0002.0009.
11	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber	0001.0002.0009.0010.0012.	0001.0002.0009.0010.
12	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas	0001.0002.0009.0010.0011.	0001.0002.0009.0010.

Não que seja uma necessidade comum, mas caso alguém deseje converter um modelo adjacente para um modelo de caminhos materializados, segue a CTE que pode fazê-lo.

;WITH Res (ID, IDSup, Descricao, Caminho, CaminhoSuperior, CaminhoMaterializado) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’,
        CAST(” As VARCHAR(MAX)),
        CAST(1 As VARCHAR(MAX))
    FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’, Caminho,
        CaminhoMaterializado + ‘.’ +
            CAST(ROW_NUMBER() OVER (
                PARTITION BY Caminho
                ORDER BY F.Descricao) As VARCHAR(MAX))
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT
ID, Descricao, Caminho, CaminhoMaterializado As CM
FROM Res
ORDER BY CaminhoMaterializado

ID	Descricao	Caminho	CM
01	Ativo	0001.	1
02	Ativo -> Ativo Circulante	0001.0002.	1.1
09	Ativo -> Ativo Circulante -> Créditos	0001.0002.0009.	1.1.1
10	Ativo -> Ativo Circulante -> Créditos -> Duplicatas	0001.0002.0009.0010.	1.1.1.1
12	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber	0001.0002.0009.0010.0012.	1.1.1.1.1
11	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas	0001.0002.0009.0010.0011.	1.1.1.1.2
03	Ativo -> Ativo Circulante -> Disponibilidades	0001.0002.0003.	1.1.2
04	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa	0001.0002.0003.0004.	1.1.2.1
05	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral	0001.0002.0003.0004.0005.	1.1.2.1.1
06	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias	0001.0002.0003.0006.	1.1.2.2
07	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente	0001.0002.0003.0006.0007.	1.1.2.2.1
13	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento	0001.0002.0003.0006.0013.	1.1.2.2.2
08	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança	0001.0002.0003.0006.0008.	1.1.2.2.3

Bem pessoal, acho que por hoje é só. Amanhã terei um longo dia pela frente.

[ ]s,

Gustavo

Deixe um comentário

Publicado em DICAS

Utilizando Common Table Expressions (CTEs) para estruturação de MENUs – Parte I

Publicado em 09/10/2010 | 7 Comentários

Boa Noite Pessoal,

Está realmente difícil estar presente e o backlog de atividades, artigos e tarefas está enorme. Bem, hoje vou postar uma dica rápida. Estou moderando diariamente os fóruns de SQL Server do MSDN e do Technet e hoje vi uma dúvida interessante envolvendo as CTEs. A dúvida é na montagem, ordenação e exibição dos registros de um plano de contas. Vejamos um exemplo prático:

A figura acima mostra a estrutura de um plano de contas de uma empresa fictícia. Ao lado da legenda do plano está relacionado também o seu ID. O script abaixo cria uma tabela e grava alguns registros para reproduzir o pano:

— Cria uma tabela de Plano de Contas
CREATE TABLE PlanoContas (
    ID INT NOT NULL,
    IDSup INT NULL,
    Descricao VARCHAR(50))

— Adiciona as constraints
ALTER TABLE PlanoContas ADD CONSTRAINT PK_Plano PRIMARY KEY (ID)

ALTER TABLE PlanoContas ADD CONSTRAINT FK_Plano FOREIGN KEY (IDSup)
REFERENCES PlanoContas (ID)

Há inúmeras possibilidades com uma CTE recursiva, mas uma necessidade comum, é montar um resultset para exibir exatamente o plano de contas com as respectivas identações ou ainda exibir os caminhos para chegar a uma determinada conta. Vejamos então algumas consultas utilizando as recursive CTEs.

— Mostra os caminhos até as contas
;WITH Res (ID, IDSup, Descricao) As (
    SELECT ID, IDSup, CAST(Descricao As VARCHAR(MAX)) FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT F.ID, F.IDSup, P.Descricao + ‘ -> ‘ + F.Descricao
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao FROM Res
ORDER BY ID

ID	Descricao
01	Ativo
02	Ativo -> Ativo Circulante
03	Ativo -> Ativo Circulante -> Disponibilidades
04	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa
05	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral
06	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias
07	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente
08	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança

— Mostra as contas aninhadas
;WITH Res (ID, IDSup, Descricao, Nivel) As (
    SELECT ID, IDSup, Descricao, 1 As Nivel FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT F.ID, F.IDSup, F.Descricao, Nivel + 1
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, ‘|’ + REPLICATE(‘–‘,Nivel) + Descricao As Descricao FROM Res
ORDER BY ID

ID	Descricao
01	\|–Ativo
02	\|—-Ativo Circulante
03	\|——Disponibilidades
04	\|——–Caixa
05	\|———-Caixa Geral
06	\|——–Contas Bancárias
07	\|———-Conta Corrente
08	\|———-Conta Poupança

Se o plano de contas ficasse estático ou se as contas sempre surgissem abaixo das contas existentes, seria fácil utilizar as consultas acima, mas como será que as consultas se comportam quando há inserções no meio das contas já existentes ? Vejamos a seguir:

INSERT INTO PlanoContas VALUES (09,02,‘Créditos’)
INSERT INTO PlanoContas VALUES (10,09,‘Duplicatas’)
INSERT INTO PlanoContas VALUES (11,10,‘Duplicatas Descontadas’)
INSERT INTO PlanoContas VALUES (12,10,‘Duplicatas a Receber’)
INSERT INTO PlanoContas VALUES (13,06,‘Conta Investimento’)

As novas contas mudam o organograma conforme a figura abaixo:

O novo plano de contas adicionou novos nós em sequência (09, 10, 11 e 12), mas uma nova conta (13) foi adicionada no plano e não manteve a ordem já que o 13 supostamente deve aparecer antes do 09. Vejamos como as consultas se comportam nesse caso

SELECT ID, Descricao FROM Res
ORDER BY ID

ID	Descricao
01	Ativo
02	Ativo -> Ativo Circulante
03	Ativo -> Ativo Circulante -> Disponibilidades
04	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa
05	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral
06	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias
07	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente
08	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança
09	Ativo -> Ativo Circulante -> Créditos
10	Ativo -> Ativo Circulante -> Créditos -> Duplicatas
11	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas
12	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber
13	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento

SELECT ID, ‘|’ + REPLICATE(‘–‘,Nivel) + Descricao As Descricao FROM Res
ORDER BY ID

ID	Descricao
01	\|–Ativo
02	\|—-Ativo Circulante
03	\|——Disponibilidades
04	\|——–Caixa
05	\|———-Caixa Geral
06	\|——–Contas Bancárias
07	\|———-Conta Corrente
08	\|———-Conta Poupança
09	\|——Créditos
10	\|——–Duplicatas
11	\|———-Duplicatas Descontadas
12	\|———-Duplicatas a Receber
13	\|———-Conta Investimento

Na primeira consulta, o caminho até a conta investimento (13) está correto, mas não apareceu na sequência esperada, ou seja, logo após a conta poupança (08) igual ao plano de contas. Na segunda consulta, a identação da conta investimento está correta (5⁰ nível), mas visivelmente aparece após as duplicatas e dá a falsa idéia de que essa conta está abaixo da conta "Duplicatas" o que não é verdade. A grande questão é elaborar a ordem em que as contas precisam ser recuperadas e por mais simples que pareça ela não é óbvia. Não é eficaz fazer a ordenação por ID e nem por nível. Demora algum tempo para perceber, mas a ordenação deve combinar ambos os fatores, ou seja, para um dado ID, organiza-se suas contas inferiores por níveis. A dificuldade está em elaborar o "ORDER BY". Uma alternativa é recorrer a materialização dos caminhos conforme o script abaixo:

;WITH Res (ID, IDSup, Descricao, Caminho) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        CAST(ID As VARCHAR(MAX)) + ‘.’ FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao, Caminho FROM Res ORDER BY Caminho

ID	Descricao	Caminho
01	Ativo	1.
02	Ativo -> Ativo Circulante	1.2.
03	Ativo -> Ativo Circulante -> Disponibilidades	1.2.3.
04	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa	1.2.3.4.
05	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral	1.2.3.4.5.
06	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias	1.2.3.6.
13	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento	1.2.3.6.13.
07	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente	1.2.3.6.7.
08	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança	1.2.3.6.8.
09	Ativo -> Ativo Circulante -> Créditos	1.2.9.
10	Ativo -> Ativo Circulante -> Créditos -> Duplicatas	1.2.9.10.
11	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas	1.2.9.10.11.
12	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber	1.2.9.10.12.

A idéia de materializar o caminho deixou as contas um pouco mais organizadas. Entretanto, os mais observadores irão perceber um pequeno erro que pode inclusive passar desapercebido por alguns. A conta investimento (13) foi recuperada antes das contas corrente (7) e poupança (8). Se o ID da conta investimento é 13 porque então essa conta foi retornada antes das contas de ID 7 e 8 ? A resposta é bem simples. Se considerássemos que 7 e 8 são números, o 13 viria posteriormente, mas considerando que a coluna "caminho" é um VARCHAR, a string 13 é recuperada antes das strings 7 e 8 e por isso ocorre essa pequena divergência. Para resolver esse pequeno detalhe é possível utilizar uma máscara que faça com que a ordem numérica e a ordem alfanumérica sejam iguais. Supondo um plano de até mil contas, pode-se alterar a consulta para a solução seguinte:

;WITH Res (ID, IDSup, Descricao, Caminho) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’ FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao, Caminho FROM Res ORDER BY Caminho

ID	Descricao	Caminho
01	Ativo	0001.
02	Ativo -> Ativo Circulante	0001.0002.
03	Ativo -> Ativo Circulante -> Disponibilidades	0001.0002.0003.
04	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa	0001.0002.0003.0004.
05	Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral	0001.0002.0003.0004.0005.
06	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias	0001.0002.0003.0006.
13	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente	0001.0002.0003.0006.0007.
07	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança	0001.0002.0003.0006.0008.
08	Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento	0001.0002.0003.0006.0013.
09	Ativo -> Ativo Circulante -> Créditos	0001.0002.0009.
10	Ativo -> Ativo Circulante -> Créditos -> Duplicatas	0001.0002.0009.0010.
11	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas	0001.0002.0009.0010.0011.
12	Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber	0001.0002.0009.0010.0012.

Agora me parece que a estrutura está correta e adequada para apresentação. Utilizando a mesma estratégia da coluna materializada chamada caminho, podemos arrumar a consulta baseada em aninhamento.

;WITH Res (ID, IDSup, Descricao, Nivel, Caminho) As (
    SELECT
        ID, IDSup, Descricao, 1 As Nivel,
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’ FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, F.Descricao, Nivel + 1,
        P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT
ID, ‘|’ + REPLICATE(‘–‘,Nivel) + Descricao As Descricao,
Nivel, Caminho
FROM Res
ORDER BY Caminho

ID	Descricao	Nível	Caminho
01	\|–Ativo	1	0001.
02	\|—-Ativo Circulante	2	0001.0002.
03	\|——Disponibilidades	3	0001.0002.0003.
04	\|——–Caixa	4	0001.0002.0003.0004.
05	\|———-Caixa Geral	5	0001.0002.0003.0004.0005.
06	\|——–Contas Bancárias	4	0001.0002.0003.0006.
07	\|———-Conta Corrente	5	0001.0002.0003.0006.0007.
08	\|———-Conta Poupança	5	0001.0002.0003.0006.0008.
09	\|———-Conta Investimento	5	0001.0002.0003.0006.0013.
10	\|——Créditos	3	0001.0002.0009.
11	\|——–Duplicatas	4	0001.0002.0009.0010.
12	\|———-Duplicatas Descontadas	5	0001.0002.0009.0010.0011.
13	\|———-Duplicatas a Receber	5	0001.0002.0009.0010.0012.

Agora sim, ambas as consultas estão corretas e acredito exibir adequadamente o resultado esperado. Espero que essa dica seja útil aqueles que tem de lidar com uma representação hierárquica baseada no modelo adjacente e que tenham que apresentar os resultados com base na posição hierárquica.

[ ]s,

Gustavo

7 Comentários

Publicado em DICAS

Como validar os nomes das colunas durante a criação de uma tabela – Parte I

Publicado em 28/06/2010 | 2 Comentários

Boa Noite Pessoal,

Em uma aula dessas nos cursos de SQL Server, durante a exposição do recurso Policy Based Management no SQL Server 2008, eu apresentei uma política que demonstrava como forçar que o nome de uma tabela obedecesse a um determinado padrão de nomenclatura (no caso as tabelas deveriam iniciar-se com tbl). Os alunos gostaram do exemplo principalmente porque boa parte não conhecia esse recurso e a melhor parte é que ele pode ser utilizado no SQL Server 2008 em conjunto com o SQL Server 2000 e 2005. O script a seguir reproduz o exemplo que utilizei (também disponibilizei o arquivo xml para importar a política em: http://cid-f4f5c630410b9865.office.live.com/self.aspx/ProjetosSQLServer/20100628%5E_pChecaNomeTabela.xml).

DECLARE @condition_id INT
EXEC msdb.dbo.sp_syspolicy_add_condition @name = N’cChecaNomeTabela’,
    @description = N”, @facet = N’ITableOptions’,
    @expression = N'<Operator>
<TypeClass>Bool</TypeClass>
<OpType>LIKE</OpType>
<Count>2</Count>
<Attribute>
    <TypeClass>String</TypeClass>
    <Name>Name</Name>
</Attribute>
<Constant>
    <TypeClass>String</TypeClass>
    <ObjType>System.String</ObjType>
    <Value>tbl%</Value>
</Constant>
</Operator>’, @is_name_condition = 2, @obj_name = N’tbl%’, @condition_id = @condition_id OUTPUT

DECLARE @object_set_id INT
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name = N’pChecaNomeTabela_ObjectSet’,
@facet = N’ITableOptions’, @object_set_id = @object_set_id OUTPUT

DECLARE @target_set_id INT
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name = N’pChecaNomeTabela_ObjectSet’,
@type_skeleton = N’Server/Database/Table’, @type = N’TABLE’, @enabled = True,
@target_set_id = @target_set_id OUTPUT

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id = @target_set_id,
@type_skeleton = N’Server/Database/Table’, @level_name = N’Table’,
@condition_name = N”, @target_set_level_id = 0

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id = @target_set_id,
@type_skeleton = N’Server/Database’, @level_name = N’Database’,
@condition_name = N”, @target_set_level_id = 0

DECLARE @policy_id INT
EXEC msdb.dbo.sp_syspolicy_add_policy @name = N’pChecaNomeTabela’,
@condition_name = N’cChecaNomeTabela’, @execution_mode = 1, @is_enabled = True,
@policy_id = @policy_id OUTPUT, @object_set = N’pChecaNomeTabela_ObjectSet’

Agora que a política está criada, o script a seguir verifica se ela está de fato funcionando. Como a política impede a criação de tabelas que não se iniciem com o prefixo tbl, a criação de tabelas com um prefixo diferente deve resultar em um erro:

CREATE TABLE tLancamentos (
    LancamentoID INT NOT NULL IDENTITY(1,1),
    LancamentoValor SMALLMONEY,
    LancamentoData DATE)

Policy ‘pChecaNomeTabela’ has been violated by ‘SQLSERVER:\SQL\MQ002\DEV\Databases\tempdb\Tables\dbo.tLancamentos’.
This transaction will be rolled back.
Policy condition: ‘@Name LIKE ‘tbl%”
Policy description: ”
Additional help: ” : ”
Statement: ‘CREATE TABLE tLancamentos (
    LancamentoID INT NOT NULL IDENTITY(1,1),
    LancamentoValor SMALLMONEY,
    LancamentoData DATE) ‘.
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

A política foi efetiva uma vez que ela impediu a criação de uma tabela tLancamentos, pois, ela não inicia com o prefixo tbl. A criação de uma tabela com esse prefixo funciona perfeitamente:

CREATE TABLE tblLancamentos (
    LancamentoID INT NOT NULL IDENTITY(1,1),
    LancamentoValor SMALLMONEY,
    LancamentoData DATE)

Command(s) completed successfully.

Imediatamente após mostrar esse exemplo, um aluno me perguntou se haveria como aplicar a mesma idéia na criação de colunas e de preferência para obedecer uma padrão de nomenclatura previamente estabelecido. Essa foi uma pergunta que me surpreendeu. Eu reconheço plenamente essa necessidade por partes do administradores de dados (ADs), mas nunca havia pensado em fazer algo do tipo com políticas. Até procurei na faceta Name, e embora ela consiga estabelecer nomes para vários objetos (tabelas, procedures, views, etc) não é possível utilizá-la para nomes de colunas. Isso era previsível, pois, como o gerenciamento de políticas é baseado em triggers DDL e estas não possuem disparo para colunas, o gerenciamento baseado em política também não haveria de fazê-lo. Ainda que isso fosse possível, normalmente padrões de nomenclatura não limitam-se a prefixos ou sufixos, mas a palavras combinadas para formar um nome válido (ex: NomeCompletoCliente). Mesmo com condições do tipo ExecuteSQL, dificilmente seria possível algo do tipo On Change Prevent para colunas obedecendo um padrão de nomenclatura. Lançado o desafio, comecei a pensar em uma forma de resolver esse problema.

Baseando-me no meu artigo anterior "Extraindo e validando nomes de uma string no padrão Camel Case", vou adotar a mesma tabela de prefixo para definição do nome de colunas. O script abaixo cria a tabela de definições:

— Cria uma tabela de Definições
CREATE TABLE Definicoes (
    DefID INT NOT NULL IDENTITY(1,1),
    DefAcronimo VARCHAR(100) NOT NULL,
    DefPalavra VARCHAR(200) NOT NULL)

— Insere alguns registros
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Num’,‘Número’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Cart’,‘Cartão’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Cred’,‘Crédito’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Deb’,‘Débito’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Cli’,‘Cliente’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Val’,‘Valor’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Par’,‘Parcela’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Venc’,‘Vencimento’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Dat’,‘Data’)

Utilizarei também uma das funções criadas no artigo:

— Criação da função
CREATE FUNCTION dbo.FnExtraiPalavras (@Nome VARCHAR(100))
RETURNS TABLE
RETURN (

WITH Nums As (
    SELECT 1 As Num
    UNION ALL
    SELECT Num + 1 FROM Nums
    WHERE Num + 1 <= LEN(@Nome)),

Analise As (

SELECT
Num As Posicao
FROM Nums
WHERE ASCII(SUBSTRING(@Nome,Num,1)) BETWEEN 65 AND 90),

Palavras As (

SELECT Posicao As Inicio,
ISNULL((SELECT MIN(Posicao) FROM Analise As TInt
WHERE TInt.Posicao > TOut.Posicao),LEN(@Nome) + 1) As Fim
FROM Analise As TOut)

SELECT SUBSTRING(@Nome, Inicio, Fim – Inicio) As Palavra, Inicio, Fim
FROM Palavras)

As triggers DDL introduzidas no SQL Server 2005 são disparadas após a ação que as disparou. Embora tenham a capacidade de reverter a ação através de um ROLLBACK, seus comandos só executam após a ocorrência de um evento. Em uma trigger DDL de CREATE TABLE, a trigger pode até reverter a criação da tabela, mas a tabela será criada antes da execução da trigger. Isso significa que no momento de execução da trigger, a tabela já existe e suas colunas podem ser consultadas. Seria fácil portanto analisar a relação das colunas com o uso da sys.columns e das funções criadas. Vejamos como ficaria o corpo da trigger:

CREATE TRIGGER TrgForcaNomenclaturaTabela ON DATABASE
FOR CREATE_TABLE
As
BEGIN
    — Captura o esquema e a tabela
    DECLARE @SchemaName SYSNAME
    DECLARE @ObjectName SYSNAME

SET @SchemaName = EVENTDATA().value(‘(/EVENT_INSTANCE/SchemaName/text())[1]’,‘SYSNAME’)
SET @ObjectName = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName/text())[1]’,‘SYSNAME’)

    — Verifica se há colunas que violem as regras estabelecidas
    DECLARE @Col VARCHAR(100), @Palavra VARCHAR(10), @MSG VARCHAR(200)
    SELECT TOP(1) @Col = Name, @Palavra = Palavra
    FROM sys.columns As Cols
        CROSS APPLY dbo.FnExtraiPalavras(Cols.name) As Pals
    WHERE object_id = object_id(@SchemaName + ‘.’ + @ObjectName) AND NOT EXISTS (
        SELECT * FROM Definicoes As DEF
        WHERE Def.DefAcronimo = Pals.Palavra)

    IF ISNULL(@Col,@Palavra) IS NOT NULL
    BEGIN
        SET @MSG = ‘A coluna ‘ + @Col + ‘ da tabela ‘ +
            @SchemaName + ‘.’ + @ObjectName +
            ‘ possui palavras inválidas ‘ + CHAR(10)
        SET @MSG = @MSG + ‘O acrônimo ‘ + @Palavra + ‘ é inválido’
        RAISERROR(@MSG,16,1)
        ROLLBACK
    END
END

Vejamos agora a trigger em ação. O script a seguir cria uma tabela com as colunas NumCartCred, ValDeb e ValParCli. As palavras que compõe cada uma dessas colunas são consideradas válidas uma vez que estejam contempladas nas definições previamente cadastradas.

CREATE TABLE tblDadosCliente (
    NumCartCred CHAR(19),
    ValDeb SMALLMONEY,
    ValParCli SMALLMONEY)

Command(s) completed successfully.

Agora vejamos como se comporta a criação de uma tabela onde uma das colunas contém palavras não contempladas nas definições previamente cadastradas:

CREATE TABLE tblDadosComplementares (
    ValCred SMALLMONEY,
    NumParCli SMALLMONEY,
    CodCli INT,
    CredAnt SMALLMONEY)

Msg 50000, Level 16, State 1, Procedure TrgForcaNomenclaturaTabela, Line 30
A coluna CodCli da tabela dbo.tblDadosComplementares possui palavras inválidas
O acrônimo Cod é inválido
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Nesse caso a coluna CodCli possui acrônimos que não estão presentes na tabela de definições. O acrônimo "Cli" significa cliente, mas o acrônimo Cod não está cadastrado e por isso a instrução de CREATE TABLE é rejeitada. A coluna CredAnt também é inválida, pois, apenas o acrônimo Cred está cadastrado enquanto Ant não está presente na tabela de definições. A trigger localiza apenas a primeira referência inválida uma vez que basta apenas uma para que o comando seja rejeitado.

Embora possível a determinação de nomes de colunas através de triggers DDL, a solução envolve uma certa dose de codificação. Espero que em uma release futura do SQL Server o recurso de políticas possa evoluir o suficiente para tornar esse trabalho mais fácil. Até lá, acredito que essa solução venha atender a muitos administradores de dados na garantia de padronização dos nomes das colunas desde a elaboração do modelo de dados até a sua implementação física no SQL Server.

[ ]s,

Gustavo

2 Comentários

Publicado em DICAS

Extraindo e validando nomes de uma string no padrão Camel Case

Publicado em 28/06/2010 | Deixe um comentário

Bom Dia Pessoal,

No mundo da programação é bem comum escrevermos nomes na notação Camel Case já que quase todas as linguagens de programação evitam trabalhar com espaços nas definições de nome. Para quem não entendeu nada da frase anterior, a prática Camel Case escreve vários nomes (abreviados ou não) em uma única string. Normalmente os nomes participantes iniciam-se com letras maiúsculas e as demais letras são minúsculas. Tomemos por exemplo as palavras número, cartão e crédito. Essas palavras podem ser combinadas para formar uma variável ou uma coluna em um banco de dados. Possivelmente o nome sugerido será NumeroCartaoCredito. Podemos visualizar que as três palavras (número, cartão e crédito) estão juntas em uma única string (NumeroCartaoCredito). Visualmente é possível perceber que essa string é composta por essas três palavras e ao visualizá-la já se tem idéia do que ela significa.

Uma das boas práticas de administração de dados é a utilização de um padrão de nomenclatura. Normalmente a presença desse padrão representa uma linguagem comum que auxilia os administradores de dados, desenvolvedores, analistas e outros profissionais que irão criar e manter esses modelos de dados. De forma análoga, o mesmo princípio é utilizado para declaração de variáveis nos códigos a serem utilizados pelos softwares desenvolvidos dentro da organização. Para evitar colunas e variáveis com nomes muito grandes é comum abreviar esses nomes. Em um padrão hipotético, as palavras número, cartão e crédito poderiam ser substituídas pelos acrônimos Num, Cart e Cred e a string NumeroCartaoCredito consequentemente seria NumCartCred.

Se existe um padrão de nomenclatura, naturalmente que suas definições serão armazenadas em algum local, pois, será necessário conferir o padrão na hora de criar e manter as colunas e variáveis. O script a seguir cria uma tabela com alguns acrônimos e suas respectivas palavras chave.

— Cria uma tabela de Definições
CREATE TABLE Definicoes (
    DefID INT NOT NULL IDENTITY(1,1),
    DefAcronimo VARCHAR(100) NOT NULL,
    DefPalavra VARCHAR(200) NOT NULL)

Agora que temos alguns acrônimos definidos, seria fácil montar alguns nomes e identificar seus respectivos significados:

Nome	Significado
NumCartCred	Número Cartão Crédito
ValDeb	Valor Débito
ValParCli	Valor Parcela Cliente
DatVencParc	Data Vencimento Parcela
NumParCli	Número Parcela Cliente

A idéia geral já está posta, mas como fazer para que a partir de um string se extraia o significado ? Se os nomes fossem delimitados por algum caractér como ; ou ainda | seria bem mais fácil. Bastaria utilizar algumas técnicas que já apresentei em artigos anteriores relacionados a Arrays (Parte I, II, III). Entretanto esse não parece ser o caso. A única regra é que as palavras se iniciam com uma letra maiúscula. Se tivéssemos que escolher um delimitador seria uma letra maiúscula, mas isso não representa um único caractér.

As letras maiúsculas no SQL Server iniciam-se na posição ASCII 65 representando a letra A e finalizam na posição ASCII 90 representando a letra Z. Todas as letras maiúsculas concetram-se nesse intervalo. O script abaixo demonstra esse intervalo

SELECT ASCII(‘A’) As Inicio, ASCII(‘Z’) As Fim
SELECT CHAR(65) As [65], CHAR(68) As [68], CHAR(84) As [84], CHAR(90) As [90]

Início	Fim
65	90

65	68	84	90
A	D	T	Z

No artigo Criando uma tabela com uma seqüência de números – Parte II demonstrei como gerar uma tabela de números utilizando as CTEs. Com essa mesma técnica é possível percorrer uma string é possível identificar exatamente quais são as posições que obedecem ao RANGE ASCII 65 – 90 e portanto serão maiúsculas. Algumas pessoas como o Paulo e o Thiago já fizeram algo semelhante e possivelmente seus respectivos blogs tem boas referências sobre CTEs (recomendo uma leitura posterior).

DECLARE @Nome VARCHAR(100)
SET @Nome = ‘NumCartCred’

;WITH Nums As (
    SELECT 1 As Num
    UNION ALL
    SELECT Num + 1 FROM Nums
    WHERE Num + 1 <= LEN(@Nome))

SELECT
    SUBSTRING(@Nome,Num,1) As Caracter,
    CASE WHEN ASCII(SUBSTRING(@Nome,Num,1))
        BETWEEN 65 AND 90 THEN ‘Sim’ ELSE ‘Não’ END As Maiuscula,
    Num As Posicao
FROM Nums

Caractér	Maiúscula ?	Posição
N	Sim	01
u	Não	02
m	Não	03
C	Sim	04
a	Não	05
r	Não	06
t	Não	07
C	Sim	08
r	Não	09
e	Não	10
d	Não	11

Com essa tabela, podemos ver claramente quais as posições que representam letra maiúsculas (1, 4 e 8) e letras minúsculas. O próximo passo seria ligar as posições das letras maiúsculas para poder montar as palavras. O script a seguir é uma aplicação da técnica utilizada no artigo

DECLARE @Nome VARCHAR(100)
SET @Nome = ‘NumCartCred’

;WITH Nums As (
    SELECT 1 As Num
    UNION ALL
    SELECT Num + 1 FROM Nums
    WHERE Num + 1 <= LEN(@Nome)),

Analise As (

SELECT
SUBSTRING(@Nome,Num,1) As Caracter,
Num As Posicao
FROM Nums
WHERE ASCII(SUBSTRING(@Nome,Num,1)) BETWEEN 65 AND 90)

SELECT Caracter, Posicao As Inicio,
ISNULL((SELECT MIN(Posicao) FROM Analise As TInt
WHERE TInt.Posicao > TOut.Posicao),LEN(@Nome)) As Fim
FROM Analise As TOut

Caractér	Início	Fim
C	01	04
N	04	08
N	08	11

Agora que os intervalos de início e fim de cada palavra são conhecidos, fica muito fácil montar as palavras (um pequeno ajuste na posição fim da última palavra):

DECLARE @Nome VARCHAR(100)
SET @Nome = ‘NumCartCred’

;WITH Nums As (
    SELECT 1 As Num
    UNION ALL
    SELECT Num + 1 FROM Nums
    WHERE Num + 1 <= LEN(@Nome)),

Analise As (

SELECT
Num As Posicao
FROM Nums
WHERE ASCII(SUBSTRING(@Nome,Num,1)) BETWEEN 65 AND 90),

Palavras As (

SELECT Posicao As Inicio,
ISNULL((SELECT MIN(Posicao) FROM Analise As TInt
WHERE TInt.Posicao > TOut.Posicao),LEN(@Nome) + 1) As Fim
FROM Analise As TOut)

SELECT SUBSTRING(@Nome, Inicio, Fim – Inicio) As Palavra, Inicio, Fim
FROM Palavras

Palavra	Início	Fim
Num	1	4
Cart	4	8
Cred	8	12

O próximo passo é encapsular esse processo em uma function para reuso:

— Criação da função
CREATE FUNCTION dbo.FnExtraiPalavras (@Nome VARCHAR(100))
RETURNS TABLE
RETURN (

WITH Nums As (
    SELECT 1 As Num
    UNION ALL
    SELECT Num + 1 FROM Nums
    WHERE Num + 1 <= LEN(@Nome)),

Analise As (

SELECT
Num As Posicao
FROM Nums
WHERE ASCII(SUBSTRING(@Nome,Num,1)) BETWEEN 65 AND 90),

Palavras As (

SELECT Posicao As Inicio,
ISNULL((SELECT MIN(Posicao) FROM Analise As TInt
WHERE TInt.Posicao > TOut.Posicao),LEN(@Nome) + 1) As Fim
FROM Analise As TOut)

SELECT SUBSTRING(@Nome, Inicio, Fim – Inicio) As Palavra, Inicio, Fim
FROM Palavras)

— Uso da função
SELECT Palavra FROM dbo.FnExtraiPalavras(‘DatVencParc’)

Palavra

Dat

Venc

Parc

Um Join com a tabela de definições pode inclusive dar o significado de cada palavra encontrada em um nome em particular:

— Mostra o significado das palavras em um nome específico
DECLARE @Nome VARCHAR(100)
SET @Nome = ‘ValParCli’

SELECT DefAcronimo As Acronimo, DefPalavra As Palavra
FROM Definicoes As Def
INNER JOIN dbo.FnExtraiPalavras(@Nome) As Pal ON Def.DefAcronimo = Pal.Palavra

Acrônimo	Palavra
Val	Valor
Par	Parcela
Cli	Cliente

Embora a função seja capaz de extrair as palavras de um string, o próximo passo é verificar a validade dessas palavras, ou seja, é necessário que cada palavra extraída possua uma definição previamente cadastrada. A seguir mostro como fazer isso com outra função:

— Cria uma função de validação
CREATE FUNCTION dbo.ValidaNome (@Nome VARCHAR(100))
RETURNS INT
AS
BEGIN

    — Declara uma variável de retorno
    DECLARE @Retorno INT
    SET @Retorno = 1

   — Se houver alguma palavra não cadastrada
    IF EXISTS (SELECT * FROM dbo.FnExtraiPalavras(@Nome) As Pal
        WHERE NOT EXISTS (SELECT * FROM Definicoes As Def
            WHERE Pal.Palavra = Def.DefAcronimo))
    SET @Retorno = 0
    RETURN (@Retorno)
END

— Testa a função
SELECT
    dbo.ValidaNome(‘NumParCli’) As NumParCli,
    dbo.ValidaNome(‘NumDebPar’) As NumDebPar,
    dbo.ValidaNome(‘NumIdenCli’) As NumIdenCli

NumParCli	NumDebPar	NumIdenCli
1	1	0

Para os nomes "NumParCli" e "NumDebPar" a função retornou 1 o que mostra que ambos são válidos já que todas as palavras constituintes estão previamente cadastradas nas definições. O nome "NumIdenCli" ao ser submetido para análise retornou 0 uma vez que uma ou mais palavras não estão cadastradas nas definições (no caso a palavra Iden).

[ ]s,

Gustavo

Deixe um comentário

Publicado em DICAS

Como monitorar e logar as colunas que foram atualizadas ?

Publicado em 06/05/2010 | 2 Comentários

Boa Noite Pessoal,

Para tirar o atraso de tantos posts e pendências, estou aqui mais uma vez postando um pouco de código TSQL. Meu backlog de dúvidas está imenso e vamos ver se consigo reduzí-lo. Já vi algumas vezes aparecer uma certa dúvida referente ao log de alterações de uma tabela, mas especificamente, como saber quais colunas que sofreram uma alteração de update e quais eram os valores antes (ou até depois) da alteração. Essa é sem dúvida uma necessidade bem comum e difícil de implementar. Há recursos muito mais modernos para fazer isso como o AUDIT, CDC (Change Data Capture) e o CT (Change Tracking), mas como nem todos estão com o SQL Server 2008 ainda vejamos uma forma de fazer isso com triggers.

— Cria uma tabela de operações financeiras
CREATE TABLE OperacaoFinanceira (
    IDOperacaoFinanceira INT NOT NULL, DataOpFinanceira DATE,
    DataProcessamento DATE, CodStatusOpFin TINYINT,
    CodTipoMovimento INT, IDUsuario VARCHAR(100),
    IDProduto INT, IDModalidade INT, Valor SMALLMONEY,
    BolEstorno TINYINT, BolAuditada TINYINT,
    CONSTRAINT PK_OperacaoFinanceira PRIMARY KEY (IDOperacaoFinanceira))

INSERT INTO OperacaoFinanceira VALUES (25,‘20100501’,‘20100503’,1,4,‘Diego_0003’,23,2,550.01,0,0)
INSERT INTO OperacaoFinanceira VALUES (26,‘20100501’,‘20100503’,1,5,‘Marco_0004’,15,1,945.13,1,0)
INSERT INTO OperacaoFinanceira VALUES (27,‘20100502’,‘20100503’,2,1,‘Tiago_0003’,28,3,126.67,1,1)
INSERT INTO OperacaoFinanceira VALUES (28,‘20100503’,‘20100503’,1,2,‘Fabio_0007’,11,4,437.55,0,1)
INSERT INTO OperacaoFinanceira VALUES (29,‘20100504’,‘20100504’,3,3,‘Diana_0008’,18,2,682.39,0,1)

— Cria uma tabela para logar as alterações
CREATE TABLE AudOF (IDAudOF INT IDENTITY(1,1),
IDOperacaoFinanceira INT, DataAlteracao DATETIME, Coluna SYSNAME,
ValorAntigo VARCHAR(100), ValorNovo VARCHAR(100))

Acredito que o script já seja suficientemente claro no que ele se propõe a fazer, ou seja, a cada alteração na tabela OperacaoFinanceira, loga-se o ID da operação financeira, a data da alteração, a coluna alterada e o valor antes e após a alteração. O próximo passo é a criação de uma trigger para poder gravar essas alterações na tabela AudOF. Segue uma primeira implementação.

— Cria uma trigger de atualização na tabela Operação Financeira
CREATE TRIGGER trgAU_AudOF ON OperacaoFinanceira
FOR UPDATE
AS
— Verifica se a atualização foi na coluna DataOpFinanceira
IF UPDATE(DataOpFinanceira)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘DataOpFinanceira’,
        CAST(Del.DataOpFinanceira As VARCHAR(100)), CAST(Ins.DataOpFinanceira As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna DataProcessamento
IF UPDATE(DataProcessamento)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘DataProcessamento’,
        CAST(Del.DataProcessamento As VARCHAR(100)), CAST(Ins.DataProcessamento As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna CodStatusOpFin
IF UPDATE(CodStatusOpFin)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘CodStatusOpFin’,
        CAST(Del.CodStatusOpFin As VARCHAR(100)), CAST(Ins.CodStatusOpFin As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna CodTipoMovimento
IF UPDATE(CodTipoMovimento)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘CodTipoMovimento’,
        CAST(Del.CodTipoMovimento As VARCHAR(100)), CAST(Ins.CodTipoMovimento As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna IDUsuario
IF UPDATE(IDUsuario)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘IDUsuario’,
        CAST(Del.IDUsuario As VARCHAR(100)), CAST(Ins.IDUsuario As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna IDProduto
IF UPDATE(IDProduto)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘IDProduto’,
        CAST(Del.IDProduto As VARCHAR(100)), CAST(Ins.IDProduto As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna IDModalidade
IF UPDATE(IDModalidade)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘IDModalidade’,
        CAST(Del.IDModalidade As VARCHAR(100)), CAST(Ins.IDModalidade As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna Valor
IF UPDATE(Valor)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘Valor’,
        CAST(Del.Valor As VARCHAR(100)), CAST(Ins.Valor As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna BolEstorno
IF UPDATE(BolEstorno)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘BolEstorno’,
        CAST(Del.BolEstorno As VARCHAR(100)), CAST(Ins.BolEstorno As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna Valor
IF UPDATE(BolAuditada)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘BolAuditada’,
        CAST(Del.BolAuditada As VARCHAR(100)), CAST(Ins.BolAuditada As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

A trigger utiliza a função UPDATE contra cada coluna, e no caso de alteração, os dados são logados na tabela AudOF. Como toda alteração irá produzir necessariamente uma entrada na INSERTED e na DELETED, o JOIN entre essas tabelas pode ser realizada com segurança. Esse JOIN utiliza a coluna IDOperacaoFinanceira que por ser chave primária artificial supostamente não deve sofrer alterações. O CAST é necessário, pois, as colunas ValorAntigo e ValorNovo na tabela AudOF são do tipo VARCHAR(100) que é o tamanho da coluna de maior comprimento na tabela OperacaoFinanceira (IDUsuario). O script a seguir faz um teste na trigger.

— Atualiza a coluna Valor cuja operação financeira tenha o ID igual a 25
UPDATE OperacaoFinanceira SET Valor = 750.01 WHERE IDOperacaoFinanceira = 25

— Atualiza a coluna IDModalidade cuja operação financeira tenha o ID igual a 26
UPDATE OperacaoFinanceira SET IDModalidade = 4 WHERE IDOperacaoFinanceira = 26

O resultado das alterações é produzido na tabela AudOF conforme mostrado abaixo:

IDAudOF	IDOperacaoFinanceira	DataAlteracao	Coluna	ValorAntigo	ValorNovo
1	25	2010-05-06 18:54:33.497	Valor	550.01	750.01
2	26	2010-05-06 18:54:33.497	IDModalidade	1	4

O script anterior fez uma alteração em uma única linha de apenas uma única coluna. Como será que a trigger lida com múltiplas alterações em uma linha, ou uma alteração em uma coluna para múltiplas linhas ou ainda múltiplas alterações em várias colunas de várias linhas ?

— Atualiza as colunas CodStatusOpFin e IDUsuario cuja operação financeira tenha o ID igual a 27
UPDATE OperacaoFinanceira SET CodStatusOpFin = 3, IDUsuario = ‘Renam_0003’ WHERE IDOperacaoFinanceira = 27

— Atualiza a coluna Valor onde as operações financeiras tenham data de operação igual ou superior a 03/05/2010
UPDATE OperacaoFinanceira SET Valor = Valor * 1.2 WHERE DataOpFinanceira >= ‘20100503’

O resultado das alterações é produzido na tabela AudOF conforme mostrado abaixo:

IDAudOF	IDOperacaoFinanceira	DataAlteracao	Coluna	ValorAntigo	ValorNovo
3	27	2010-05-06 18:55:07.930	CodStatusOpFin	2	3
4	27	2010-05-06 18:55:07.930	IDUsuario	Tiago_0003	Renam_0003
5	29	2010-05-06 18:55:10.170	Valor	682.39	818.87
6	28	2010-05-06 18:55:10.170	Valor	437.55	525.06

A trigger de fato loga alterações independente do número de linhas e de colunas. Não há na estrutura atual um agrupador para saber se as alterações foram ou não disparadas por um ou por vários comandos. Isso poderia ser resolvido com alguma lógica baseada na data ou ainda o uso de uma coluna auxiliar para capturar o SPID, mas o fato é que da forma que está a trigger tem um notório custo de desenvolvimento e implementação, pois, cada coluna tem um IF a parte que precisa ser mantido.

A função COLUMNS_UPDATED( )

O SQL Server dispõe da função COLUMNS_UPDATED para mapear a relação de colunas alteradas. Embora seja possível saber quais colunas foram alteradas, essa função não é muito trivial e sua utilização pode ser um pouco complicada, pois, o seu funcionamento varia quando uma tabela tem oito colunas, mais de oito colunas e mais de 32 colunas. Vejamos o seu comportamento com uma tabela com oito colunas.

— Cria uma tabela com oito colunas
CREATE TABLE T (C1 INT, C2 INT, C3 INT, C4 INT, C5 INT, C6 INT, C7 INT, C8 INT)

— Insere um registro
INSERT INTO T VALUES (1,2,3,4,5,6,7,8)

— Cria uma trigger
CREATE TRIGGER TR ON T
FOR UPDATE
AS
PRINT COLUMNS_UPDATED()

— Faz uma alteração
UPDATE T SET C1 = 0

O retorno da função COLUMNS_UPDATE mostra um valor em hexadecimal que representa a relação de colunas alteradas. O retorno após o update foi o hexadecimal 0x01 que significa que o número 1 (basta fazer um CAST para converter esse valor para inteiro) mostrando que a primeira coluna foi alterada. Vejamos um segundo exemplo:

— Faz três alterações
UPDATE T SET C2 = 0
UPDATE T SET C3 = 0
UPDATE T SET C4 = 0

— Converte o retorno
SELECT CAST(0x02 As INT) As C2, CAST(0x04 As INT) As C3, CAST(0x08 As INT) As C4

Dessa vez os retornos foram respectivamente 2, 4, 8. A função COLUMNS_UPDATE não retorna a posição da coluna alterada, mas sim um hexadecimal que quando convertido mostra uma combinação de valores em potência de 2. Se formos capturar os retornos das atualizações temos a seguinte lógica.

Retorno	Inteiro	Potência	Identificação	Coluna
0x01	1	2⁰	0 + 1	1
0x02	2	2¹	1 + 1	2
0x04	4	2²	1 + 2	3
0x08	8	2³	1 + 3	4

A lógica é a seguinte converte-se o valor em hexadecimal para um número inteiro. Obtêm-se a potência de base 2 que retorne esse inteiro. Extrai-se o expoente e incrementa-se uma unidade para obter o número da coluna alterada. Ex: O hexadecimal 0x08 quando convertido para inteiro retorna 8. O número 8 representa 2 elevado a 3. O expoente 3 somando mais uma unidade mostra que a quarta coluna foi alterada. Vejamos agora o que acontece quando múltiplas colunas são alteradas.

— Faz uma alteração de duas colunas
UPDATE T SET C1 = 0, C2 = 0
UPDATE T SET C3 = 0, C5 = 0
UPDATE T SET C2 = 0, C8 = 0

— Converte os retornos
SELECT CAST(0x03 As INT) As U1, CAST(0x14 As INT) As U2, CAST(0x82 As INT) As U3

Quando apenas uma coluna é atualizada é mais fácil identificá-las, mas quando duas ou mais colunas são atualizadas, as coisas ficam mais complicadas. Vejamos a lógica nesse caso.

Retorno	Inteiro	Potência	Identificação	Colunas
0x03	3	2⁰ + 2¹	0 e 1	1 e 2
0x14	20	2² + 2⁴	2 e 4	3 e 5
0x82	130	2¹ + 2⁷	1 e 7	2 e 8

De fato a lógica é um pouco complexa, mas vejamos agora uma forma de retornar exatamente o número das colunas afetadas na trigger.

— Altera a trigger
ALTER TRIGGER TR ON T
FOR UPDATE
AS
DECLARE @Num INT = COLUMNS_UPDATED(), @Inc SMALLINT = 1, @Cols VARCHAR(20) = ”

WHILE (@Num > 0)
BEGIN
    IF (@Num < POWER(2,@Inc))
    BEGIN
        SET @Cols = @Cols + CAST(@Inc As VARCHAR(4)) + ‘;’
        SELECT @Num = @Num – POWER(2,@Inc – 1), @Inc = 0
    END
SET @Inc = @Inc + 1
END

PRINT @Cols

— Faz uma alteração de várias colunas
UPDATE T SET C1 = 0
UPDATE T SET C1 = 0, C2 = 0, C3 = 0
UPDATE T SET C4 = 0, C6 = 0, C7 = 0, C8 = 0

O retornos "1;" , "3;2;1;" e "8;7;6;4;" mostram a relação de colunas alteradas de acordo com as instruções de UPDATE realizadas. Vejamos agora como a trigger se comporta se uma nova coluna for adicionada deixando a tabela com nove colunas.

— Adiciona uma nova coluna
ALTER TABLE T ADD C9 INT

— Faz os mesmo UPDATEs que os anteriores
UPDATE T SET C1 = 0
UPDATE T SET C1 = 0, C2 = 0, C3 = 0
UPDATE T SET C4 = 0, C6 = 0, C7 = 0, C8 = 0

Dessa vez os retornos foram bem diferentes do esperado.

Colunas Alteradas	Retorno Esperado	Resultado
C1	1;	9;
C1, C2, C3	3;2;1;	11;10;9;
C4, C6, C7, C8	8;7;6;4	16;15;14;12;

De fato, quando existem mais de oito colunas, a função COLUMNS_UPDATED se comporta um pouco diferente conforme detalhado no KB 232195 da Microsoft. Não vou entrar no mérito das modificações, mas uma modificação na trigger pode contemplar esse detalhe (adaptado de Trigger to detect columns updated – Columns_updated() more than 32 fields):

— Altera a trigger
ALTER TRIGGER TR ON T
FOR UPDATE
AS
DECLARE @Col INT, @Cols VARCHAR(20), @qCols INT
DECLARE @bitVerificador INT, @Pot INT

SET @Col = 0
SET @Cols = ”

— Conta quantas colunas existem na tabela contemplada pela Trigger
SET @qCols = (SELECT COUNT(*) FROM sys.columns WHERE object_id =
(SELECT Parent_ID FROM sys.triggers WHERE object_id = @@procid))

PRINT @Cols

— Efetua novos updates
UPDATE T SET C1 = 0
UPDATE T SET C4 = 0, C6 = 0, C7 = 0, C8 = 0
UPDATE T SET C4 = 0, C6 = 0, C7 = 0, C8 = 0, C9 = 0

— Adiciona novas colunas
ALTER TABLE T ADD C10 INT, C11 INT

— Efetua novos updates
UPDATE T SET C5 = 0
UPDATE T SET C6 = 0, C3 = 0, C9 = 0, C10 = 0
UPDATE T SET C2 = 0, C7 = 0, C8 = 0, C11 = 0, C10 = 0

— Elimina as colunas adicionais
ALTER TABLE T DROP COLUMN C9
ALTER TABLE T DROP COLUMN C10
ALTER TABLE T DROP COLUMN C11

— Efetua novos updates
UPDATE T SET C1 = 0
UPDATE T SET C4 = 0, C6 = 0, C7 = 0, C8 = 0

Após as alterações, a trigger fica adaptada para retornar a relação de colunas afetadas independente da quantidade de colunas que a tabela possuir. O interessante é que inclusões e exclusões de colunas não alteram o funcionamento da trigger dispensando inclusive a necessidade de recompilá-la. Os exemplos citados serviram para explicar o funcionamento da função COLUMNS_UPDATED. Uma vez que a numeração das colunas alteradas seja conhecida, fica fácil obter seus nomes com um JOIN contra a sys.columns. O código a seguir faz essa alteração:

— Altera a trigger
ALTER TRIGGER TR ON T
FOR UPDATE
AS
DECLARE @Col INT, @Cols VARCHAR(1000), @qCols INT, @NomeCol VARCHAR(50)
DECLARE @bitVerificador INT, @Pot INT

SET @Col = 0
SET @Cols = ”

— Conta quantas colunas existem na tabela contemplada pela Trigger
SET @qCols = (SELECT COUNT(*) FROM sys.columns WHERE object_id =
(SELECT Parent_ID FROM sys.triggers WHERE object_id = @@procid))

WHILE (@Col < @qCols)
BEGIN
    SET @Col = @Col + 1
    SET @Pot = (@Col – 1) % 8 + 1
    SET @Pot = POWER(2,@Pot – 1)
    SET @bitVerificador = ((@Col – 1) / 8) + 1
    IF (SUBSTRING(COLUMNS_UPDATED(),@bitVerificador, 1) & @Pot > 0)
    BEGIN
        SET @NomeCol = (
            SELECT Name FROM sys.columns WHERE object_id =
                (SELECT Parent_ID FROM sys.triggers
                WHERE object_id = @@procid) AND column_id = @Col)
        SET @Cols = @Cols + @NomeCol + ‘;’
    END
END

PRINT @Cols

— Efetua novos updates
UPDATE T SET C1 = 0
UPDATE T SET C4 = 0, C6 = 0, C7 = 0, C8 = 0

O retorno da trigger mostra o nome das colunas alteradas:

C1;
(1 row(s) affected)

C4;C6;C7;C8;
(1 row(s) affected)

Agora vejamos como utilizar o raciocínio da COLUMNS_UPDATED no exemplo de negócio no início do artigo.

— Altera a trigger de atualização na tabela Operação Financeira
ALTER TRIGGER trgAU_AudOF ON OperacaoFinanceira
FOR UPDATE
AS

DECLARE @Col INT, @qCols INT, @NomeCol VARCHAR(50)
DECLARE @bitVerificador INT, @Pot INT

SET @Col = 0

— Conta quantas colunas existem na tabela contemplada pela Trigger
SET @qCols = (SELECT COUNT(*) FROM sys.columns WHERE object_id =
(SELECT Parent_ID FROM sys.triggers WHERE object_id = @@procid))

        INSERT INTO AudOF (IDOperacaoFinanceira, DataAlteracao, Coluna)
        SELECT INS.IDOperacaoFinanceira, GETDATE(), @NomeCol FROM Inserted As Ins
    END
END

— Atualiza a coluna Valor cuja operação financeira tenha o ID igual a 25
UPDATE OperacaoFinanceira SET Valor = 650.01 WHERE IDOperacaoFinanceira = 25

Dessa vez a trigger foi capaz de capturar a coluna alterada, sem a necessidade de se especificar o nome dela através da função UPDATE apresentada na implementação anterior:

IDAudOF	IDOperacaoFinanceira	DataAlteracao	Coluna	ValorAntigo	ValorNovo
7	25	2010-05-06 14:49:44.913	Valor	NULL	NULL

O único problema é que a trigger capturou apenas o nome da coluna, mas não há referências aos valores anterior e posterior ao update. É sabido que o nome da coluna é conhecido, mas não há como recuperá-lo de forma direta. As construções abaixo parecem lógicas, mas não funcionam:

SELECT @Col FROM INSERTED
EXEC (‘SELECT ‘ + @Col + ‘ FROM INSERTED’)

O problema com a primeira construção é que @Col é uma variável e o SELECT retorna o valor da variável e não o valor da coluna cujo nome representa o valor da variável (uma instrução SELECT ‘A’ FROM INSERTED retorna a string ‘A’ assim como uma instrução SELECT @Col FROM INSERTED retorna o valor de @Col que no caso é o nome da coluna e não o seu valor). A segunda construção monta uma SQL dinâmica para executar o comando. A montagem em si está correta, mas o uso do EXEC abre uma sessão a parte e como as tabelas INSERTED e DELETED têm escopo por sessão e por isso não funcionam. Existem algumas formas de resolver isso com tabelas temporárias globais e XML. Particularmente já acho triggers pesadas e não acho interessante colocar uma carga extra envolvendo tabelas temporárias (sobretudo as globais). A versão final da trigger mostra como utilizar o XML para capturar o valor da coluna alterada.

— Altera a trigger de atualização na tabela Operação Financeira
ALTER TRIGGER trgAU_AudOF ON OperacaoFinanceira
FOR UPDATE
AS
DECLARE @Col INT, @qCols INT, @NomeCol VARCHAR(50)
DECLARE @bitVerificador INT, @Pot INT

SET @Col = 0

— Conta quantas colunas existem na tabela contemplada pela Trigger
SET @qCols = (SELECT COUNT(*) FROM sys.columns WHERE object_id =
(SELECT Parent_ID FROM sys.triggers WHERE object_id = @@procid))

— Coloca a tabela Deleted em uma variável XML
DECLARE @Deleted XML, @DeletedTMP XML
SET @Deleted = (SELECT * FROM Deleted FOR XML RAW, ROOT(‘Deleted’))

— Coloca a tabela Inserted em uma variável XML
DECLARE @Inserted XML, @InsertedTMP XML
SET @Inserted = (SELECT * FROM Inserted FOR XML RAW, ROOT(‘Inserted’))

— Substitui a TAG no XML da DELETED e faz a extração dos dados
SET @DeletedTMP = REPLACE(CAST(@Deleted As VARCHAR(MAX)),@NomeCol + ‘="’,‘Col="’)

— Substitui a TAG no XML da INSERTED e faz a extração dos dados
SET @InsertedTMP = REPLACE(CAST(@Inserted As VARCHAR(MAX)),@NomeCol + ‘="’,‘Col="’)

        INSERT INTO AudOF (IDOperacaoFinanceira, DataAlteracao, Coluna, ValorAntigo, ValorNovo)
        SELECT INS.IDOperacaoFinanceira, GETDATE(), @NomeCol,
            (SELECT E.e.value(‘
                (/Deleted/row[@IDOperacaoFinanceira = sql:column("INS.IDOperacaoFinanceira")]/@Col)[1]’,‘varchar(100)’)
            FROM @DeletedTMP.nodes(‘.’) E(e)) As ValorAntigo,
            (SELECT E.e.value(‘
                (/Inserted/row[@IDOperacaoFinanceira = sql:column("INS.IDOperacaoFinanceira")]/@Col)[1]’,‘varchar(100)’)
            FROM @InsertedTMP.nodes(‘.’) E(e)) As ValorNovo
        FROM Inserted As Ins
    END
END

— Atualiza as colunas CodStatusOpFin e IDUsuario cuja operação financeira tenha o ID igual a 27
UPDATE OperacaoFinanceira SET CodStatusOpFin = 4, IDUsuario = ‘Teles_0003’ WHERE IDOperacaoFinanceira = 27

— Atualiza a coluna IDProduto onde as operações financeiras tenham data de operação igual ou superior a 03/05/2010
UPDATE OperacaoFinanceira SET IDProduto = 21 WHERE DataOpFinanceira >= ‘20100503’

Após as modificações, a trigger foi capaz de capturar todas as colunas alteradas com seus valores anteriores e posteriores ao update conforme a tabela abaixo:

IDAudOF	IDOperacaoFinanceira	DataAlteracao	Coluna	ValorAntigo	ValorNovo
8	27	2010-05-06 19:00:57.730	CodStatusOpFin	3	4
9	27	2010-05-06 19:00:57.730	IDUsuario	Renam_0003	Teles_0003
10	29	2010-05-06 19:01:11.297	IDProduto	18	21
11	28	2010-05-06 19:01:11.297	IDProduto	11	21

Até então, a trigger da forma como está é bem dinâmica não necessitando informar as colunas existentes uma a uma. O último teste adicionará uma coluna à tabela e verificará como a trigger se comporta.

— Adiciona mais uma coluna na tabela OperacaoFinanceira
ALTER TABLE OperacaoFinanceira ADD IDInstituicao INT

— Atualiza todos os registros
UPDATE OperacaoFinanceira SET IDInstituicao = 1

— Verifica a tabela de Auditoria
SELECT IDAudOF, IDOperacaoFinanceira, DataAlteracao, Coluna, ValorAntigo, ValorNovo
FROM AudOF WHERE Coluna = ‘IDInstituicao’

IDAudOF	IDOperacaoFinanceira	DataAlteracao	Coluna	ValorAntigo	ValorNovo
12	29	2010-05-06 19:02:55.857	IDInstituicao	NULL	1
13	28	2010-05-06 19:02:55.857	IDInstituicao	NULL	1
14	27	2010-05-06 19:02:55.857	IDInstituicao	NULL	1
15	26	2010-05-06 19:02:55.857	IDInstituicao	NULL	1
16	25	2010-05-06 19:02:55.857	IDInstituicao	NULL	1

Após todo esse código TSQL, a trigger está pronta. Sua última proposta adapta-se totalmente a adição e exclusão de novas colunas conseguindo capturar perfeitamente a(s) coluna(s) alterada(s) bem como o(s) valor(es) antigo(s) e novo(s) sem a necessidade de informar previamente a relação de colunas da tabela evitando assim um custo de manutenção adicional nas triggers quando houver alterações na tabela de origem.

Embora o código seja bem eficaz não posso classificá-lo como eficiente. Se for feita uma análise superficial vemos que a trigger tem um processamento razoável (loops, consultas a tabelas de sistemas, conversões de tabelas para XML, etc). Desaconselho completamente a implementação dessa trigger em tabelas que sofram muitos updates principalmente se possuírem muitas colunas, pois, isso pode incorrer em problemas de desempenho. Aconselho a utilização de recursos mais efetivos como o AUDIT, CDC (Change Data Capture) e o CT (Change Tracking). São mais fáceis de entender e certamente mais performáticos.

[ ]s,

Gustavo

2 Comentários

Publicado em DICAS

Como descobrir a data do último acesso a uma tabela ?

Publicado em 05/03/2010 | 10 Comentários

Boa Noite Pessoal,

Quem nunca se deparou com a necessidade de saber quando a tabela foi acessada pela última vez ? Normalmente esse tipo de dúvida é comum para relacionar se uma tabela é ou não utilizada ou ainda qual o momento em que ocorreu algum leitura ou gravação indevida. No SQL Server 2000 havia alguns truques para descobrir, mas além de trabalhoso nem sempre os dados eram precisos. No SQL Server 2005 e posteriores, o processo é relativamente mais fácil, bastando apenas algumas consultas. Aproveitando um pouco os dados e explicações do meu último post, vejamos um exemplo prático:

— Cria cinco tabelas
CREATE TABLE T1 (ID INT NOT NULL, NOME VARCHAR(80))
CREATE TABLE T2 (COD INT NOT NULL, DESCRICAO VARCHAR(50))
CREATE TABLE T3 (MATRICULA CHAR(10) NOT NULL, CPF CHAR(11), NOME VARCHAR(80))
CREATE TABLE T4 (SEQ INT IDENTITY(1,1) NOT NULL, Detalhes XML)
CREATE TABLE T5 (CPF CHAR(11) NOT NULL, Lancamento MONEY)

— Cria três constraints
ALTER TABLE T1 ADD CONSTRAINT PKT1 PRIMARY KEY (ID)
ALTER TABLE T2 ADD CONSTRAINT PKT2 PRIMARY KEY (COD)
ALTER TABLE T3 ADD CONSTRAINT PKT3 PRIMARY KEY (MATRICULA)

— Cria dois índices
CREATE INDEX IX_CPF ON T3 (CPF)
CREATE INDEX IX_NOME ON T3 (NOME)

No último artigo, eu falei um pouco sobre a DMV sys.dm_db_index_usage_stats. Se toda tabela possui uma estrutura de indexação, essa DMV é útil para averiguar a utilização dessas estruturas e consequentemente a utilização das tabelas associadas. O script abaixo provoca intencionalmente acessos a essas tabelas com esperas propositais.

— Insere dois registros em T1
INSERT INTO T1 (ID, NOME) VALUES (1,‘Bruno’)
INSERT INTO T1 (ID, NOME) VALUES (2,‘Roberto’)

— Força uma espera
WAITFOR DELAY ’00:00:04′

— Insere dois registro em T2
INSERT INTO T2 (COD, DESCRICAO) VALUES (1,‘Nosso equilíbrio na balança global’)
INSERT INTO T2 (COD, DESCRICAO) VALUES (2,‘Educação por um país mais competitivo’)

— Força uma espera
WAITFOR DELAY ’00:00:05′

— Lê dados em T3 (mesmo que não haja registros, o comando é válido)
— Cover Index no CPF
SELECT CPF FROM T3

— Força uma espera
WAITFOR DELAY ’00:00:03′

— Cover Index no Nome
SELECT NOME FROM T3

— Força uma espera
WAITFOR DELAY ’00:00:08′

SELECT * FROM T3 WHERE MATRICULA = ‘MKT0000001’

— Excluí dados em T4 (mesmo que não haja registros, o comando é valido)
DELETE FROM T4

Após o acesso através de comandos de leitura e gravação, vejamos o retorno da sys.dm_db_index_usage_stats.

SELECT
OBJECT_NAME(object_id,database_id) As Tabela, Index_Id,
last_user_seek, last_user_scan, last_user_lookup, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID() And OBJECTPROPERTYEX(object_id,‘IsUserTable’) = 1
ORDER BY Tabela, Index_ID

Tabela	Index_ID	Seek	Scan	Lookup	Update
T1	1	NULL	NULL	NULL	2010-03-02 18:48:12.573
T2	1	NULL	NULL	NULL	2010-03-02 18:48:16.590
T3	1	2010-03-02 18:48:32.620	NULL	NULL	NULL
T3	2	NULL	2010-03-02 18:48:21.590	NULL	NULL
T3	3	NULL	2010-03-02 18:48:24.590	NULL	NULL
T4	0	NULL	2010-03-02 18:48:32.620	NULL	2010-03-02 18:48:32.620

As tabelas T1, T2 e T3 possuem uma chave primária clusterizada (padrão) e por isso a coluna Index_ID tem o valor 1. No caso da tabela T3, há ainda dois índices adicionais representados pelos valores 2 e 3 na coluna Index_ID. A tabela T4 não possui índice clustered e por isso a coluna Index_ID está com o valor igual 0 que representa que T4 é uma HEAP TABLE. T5 não foi referenciada e por isso não está contemplada na consulta. O script produziu várias ações de leitura e gravação nas tabelas e por isso as colunas Seek, Scan, Lookup e Update tem diferentes valores.

Para descobrir o momento mais recente em que a tabela que foi acessada, basta olhar a operação sobre os índices que tem a maior data. No caso da tabela T1 isso é fácil de ser realizado, pois, basta apenas olhar a coluna de update já que as demais colunas estão nulas (o que era esperado, pois, o script só fez gravações). O problema ocorre com tabelas que tem mais de um índice como é o caso de T3. É preciso fazer a comparação das colunas Seek, Scan, Lookup e Update para cada índice e verificar a maior data (no caso 02/03/2010 às 18:48:32.620). Isso poderia ser feito com alguns CASEs e GROUP BYs, mas ia tornar a consulta bastante poluída. Uma forma rápida de obter as datas desejadas é a utilização do operador UNPIVOT para transformar as colunas em linhas.

;WITH Utilizacao (Tabela, Index_ID, Seek, Scan, LookUp, [Update])
As (
SELECT
OBJECT_NAME(object_id,database_id) As Tabela, Index_Id,
last_user_seek, last_user_scan, last_user_lookup, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID() And OBJECTPROPERTYEX(object_id,‘IsUserTable’) = 1)

SELECT * FROM Utilizacao
UNPIVOT
(DataReferencia FOR Operacao IN ([Seek], [Scan], [LookUp], [Update])) As UP
ORDER BY Tabela, Index_ID

Tabela	Index_ID	Data de Referência	Operação
T1	1	2010-03-02 18:48:12.573	Update
T2	1	2010-03-02 18:48:16.590	Update
T3	1	2010-03-02 18:48:32.620	Seek
T3	2	2010-03-02 18:48:21.590	Scan
T3	3	2010-03-02 18:48:24.590	Scan
T4	0	2010-03-02 18:48:32.620	Scan
T4	0	2010-03-02 18:48:32.620	Update

Uma vez que as colunas com as datas foram transformadas em linhas, é muito fácil aplicar a função MAX, para descobrir a última data de referência. Aproveitei para fazer alguns "ajustes" na consulta como mostrar a última operação bem como contemplar as tabelas que não possuem entradas na sys.dm_db_index_usage_stats.

;WITH Utilizacao (object_id, Index_ID, Seek, Scan, LookUp, [Update])
As (
SELECT
T.object_id, Index_Id,
last_user_seek, last_user_scan, last_user_lookup, last_user_update
FROM sys.dm_db_index_usage_stats As I
INNER JOIN sys.tables As T ON I.object_id = T.object_id
WHERE database_id = DB_ID()),

Referencias (object_id, DataReferencia, Operacao)
As (
SELECT object_id, DataReferencia, Operacao FROM Utilizacao
UNPIVOT
(DataReferencia FOR Operacao IN ([Seek], [Scan], [LookUp], [Update])) As UP),

UltimoAcesso (object_id, UltimaData)
As (
SELECT object_id, MAX(DataReferencia) FROM Referencias
GROUP BY object_id),

UltimasOperacoes (object_id, UltimaData, Operacoes)
As (
SELECT U.*,
    (SELECT DISTINCT Operacao FROM Referencias As R
    WHERE U.object_id = R.object_id AND U.UltimaData = R.DataReferencia
    FOR XML AUTO)
FROM UltimoAcesso As U)

SELECT
Name As Tabela, UltimaData As UltimoAcesso,
REPLACE(REPLACE(Operacoes,‘"/>’,‘, ‘),‘<R Operacao="’,”) As UltimasOperacoes
FROM sys.tables As T
LEFT OUTER JOIN UltimasOperacoes As U ON T.object_id = U.object_id

Tabela	Último Acesso	Últimas Operações
T1	2010-03-02 18:48:12.573	Update,
T2	2010-03-02 18:48:16.590	Update,
T3	2010-03-02 18:48:32.620	Seek,
T4	2010-03-02 18:48:32.620	Scan, Update,
T5	NULL	NULL

A consulta mostra todas as tabelas, quando foi o último acesso e que operações estavam sendo realizadas naquele momento. Embora não tenha colocado no script, é uma boa sugestão retirar os segundos e milissegundos da consulta, pois, dificilmente haverá muitas operações no mesmo milissegundo (deixo essa como dever de casa).

Ainda que a consulta sirva de base para recuperar os últimos acessos a uma tabela, há um ponto "fraco" nessa estratégia. A DMV sys.dm_db_index_usage_stats é zerada toda vez que o SQL Server é reiniciado. Isso pode levar a falsas interpretações principalmente para relatórios trimestrais, anuais, etc caso o SQL Server tem sido reiniciado nesse meio tempo. É preciso levar isso em consideração quando consultas contra as DMVs são elaboradas.

[ ]s,

Gustavo

10 Comentários

Publicado em DICAS

Encontrando tabelas não utilizadas

Publicado em 28/02/2010 | 2 Comentários

Boa Tarde Pessoal,

Estou aqui no vôo de volta para Brasília após o Community Zone 2010. O vôo demora algum tempo e para matar o tédio já que não tem mais nada para fazer aqui, resolvi aproveitar o tempo para blogar alguma dica rápida. Mantendo a linha de um artigo recente (Encontrando índices não utilizados), irei postar uma forma razoável de listar as tabelas não utilizadas. Não raras às vezes aparecem situações nas quais a existência de uma ou mais tabelas é "questionável". Isso é bem comum de acontecer, pois, as aplicações evoluem e é normal que novas tabelas surjam e tabelas mais antigas deixem de ser utilizadas. Há também casos onde um acesso indevido (leia-se desenvolvedores com acesso a produção) acaba produzindo aquelas tabelas com o intuito de fazer algum teste rápido na área de produção (normalmente o teste é concluído, mas as tabelas não são excluídas e podem ficar lá durante anos). O fato é que mais tabelas incorrem em mais espaço, mais rotinas de administração, maior janela de backup e certamente um desperdício de recursos. Como fazer então para identificá-las ? Vejamos um exemplo prático:

— Cria três constraints
ALTER TABLE T1 ADD CONSTRAINT PKT1 PRIMARY KEY (ID)
ALTER TABLE T2 ADD CONSTRAINT PKT2 PRIMARY KEY (COD)
ALTER TABLE T3 ADD CONSTRAINT PKT3 PRIMARY KEY (MATRICULA)

— Cria dois índices
CREATE INDEX IX_CPF ON T3 (CPF)
CREATE INDEX IX_NOME ON T3 (NOME)

Uma das minhas DMVs preferidas é a sys.dm_db_index_usage_stats. Essa DMV mostra a utilização dos índices das tabelas de todos os bancos de uma instância. Como toda tabela fatalmente terá uma estrutura de indexação, essa DMV pode ser muito útil para descobrir quando e como essas estruturas foram utilizadas e por consequência a utilização da tabela.

SELECT * FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID() And OBJECTPROPERTYEX(object_id,‘IsUserTable’) = 1

A execução de um comando de SELECT contra essa DMV não irá retornar registros para as tabelas recém criadas já que como elas ainda não foram utilizadas, a DMV não populou os dados de utilização das estruturas de indexação dessas tabelas. Os comandos a seguir executam diversas instruções que referenciam algumas delas.

— Insere dois registros em T1
INSERT INTO T1 (ID, NOME) VALUES (1,‘Bruno’)
INSERT INTO T1 (ID, NOME) VALUES (2,‘Roberto’)

— Lê dados em T3 (mesmo que não haja registros, o comando é válido)
SELECT * FROM T3 WHERE MATRICULA = ‘MKT0000001’

— Excluí dados em T4 (mesmo que não haja registros, o comando é valido)
DELETE FROM T4

Agora que houve acesso por operações de leitura e gravação, é possível fazer algumas inferências usando a sys.dm_db_index_usage_stats.

Tabela	Index_ID	Seek	Scan	Lookup	Update
T1	1	NULL	NULL	NULL	2010-02-27 14:24:24.170
T2	1	NULL	NULL	NULL	2010-02-27 14:24:24.170
T3	1	2010-02-27 14:24:24.170	NULL	NULL	NULL
T3	2	NULL	2010-02-27 14:24:24.170	NULL	NULL
T3	3	NULL	2010-02-27 14:24:24.170	NULL	NULL
T4	0	NULL	2010-02-27 14:24:24.170	NULL	2010-02-27 14:24:24.170

SELECT Name FROM sys.tables As T
WHERE NOT EXISTS (
SELECT * FROM sys.dm_db_index_usage_stats As U
WHERE T.object_id = U.object_id AND U.database_id = DB_ID())

Como podemos ver, o único resultado é T5, pois, é justamente essa tabela que não sofreu nenhum acesso quer seja de leitura ou de gravação. A estratégia é interessante, mas é preciso lembrar que a sys.dm_db_index_usage_stats, assim como toda DMV, é automaticamente "zerada" quando o SQL Server é reiniciado. Se uma tabela é utilizada na produção de relatórios mensais, trimestrais, anuais, etc e houver a reinicialização do SQL Server entre esses intervalos, é possível inferir erroneamente que uma tabela não é utilizada (mesmo que ela seja).

Já vi alguns DBAs utilizando scripts semelhantes para excluir as tabelas não utilizadas. É perfeitamente compreensível já que muitas tabelas não utilizadas incorrem em alguns problemas como os abordados no início do artigo. Minha recomendação é que esse script seja utilizado para identificar as potenciais tabelas não utilizadas e bloquear o acesso. Nesse caso, quando alguém desejar acessá-las será possível descobrir que aplicações às utilizam. Se a exclusão for feita ao invés da negação do acesso, quando a tabela for novamente necessária, haverá muito mais esforço em recuperá-la.

[ ]s,

Gustavo

2 Comentários

Publicado em DICAS

Arquivo da categoria: DICAS

Exportando consultas para documentos XML

Mapeando dependências de objetos para colunas

Exclusões em cascata e auto-referência no SQL Server

Utilizando Common Table Expressions (CTEs) para estruturação de MENUs – Parte II

Utilizando Common Table Expressions (CTEs) para estruturação de MENUs – Parte I

Como validar os nomes das colunas durante a criação de uma tabela – Parte I

Extraindo e validando nomes de uma string no padrão Camel Case

Como monitorar e logar as colunas que foram atualizadas ?

Como descobrir a data do último acesso a uma tabela ?

Encontrando tabelas não utilizadas

Procure isso!

Entradas Recentes

Links