Arquivo da tag: SQL Server

Índices e Foreing Keys

Boa Noite Pessoal,

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

Por que criar índices sobre Foreign Keys ?

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

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

— Retira a nulabilidade
SET ANSI_NULL_DFLT_OFF ON

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

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

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

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

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

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

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

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

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

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

Índices e Foreign Keys (Index Seek)

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

Uma segunda opinião

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

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

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

Índices e Foreign Keys (Clustered Index Scan)

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

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

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

Índices e Foreign Keys (GROUP BY)

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

O papel do índice em relacionamentos fortes ou identificados

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

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

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

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

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

Agora vejamos o impacto nas consultas que fizemos anteriormente:

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

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

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

20120815_04_FK

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

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

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

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

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

DELETE FROM Pessoas WHERE ID = 1

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

Índices e Foreign Keys (Exclusões)

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

Índices e Foreign Keys (Exclusões)

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

Algumas conclusões

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

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

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

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

— Retira a nulabilidade
SET ANSI_NULL_DFLT_OFF ON

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

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

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

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

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

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

O resultado sai exatamente conforme o esperado:

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

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

[ ]s,

Gustavo

WEBCAST – Concorrência de dados no SQL Server (depois da apresentação)

Bom Dia Pessoal,

Hoje faz uma semana que fiz a apresentação do webcast “Concorrência de dados no SQL Server”. Já tinha um tempinho que eu não fazia apresentações e deixando alguns apertos (microsoft, atualização do live meeting, vms, etc) consegui iniciá-lo sem problemas (um leve atraso de dez minutos).

Foram praticamente duas horas de apresentação e terminamos um pouco depois das 23h e eu estava sem a água do lado, mas correu tudo bem. Eu não consegui ver a lista de participantes (apenas dei um rápida visualizada, mas não deu pra memorizar). De qualquer forma eu agradeço a todos que acompanharam (Crespi, Raionan, Sulamita eu vi vocês lá). Também agradeço ao Felipe e a Andressa pelo suporte e espero poder apresentar em situações futuras (se o tempo permitir).

Nos bastidores, foi um webcast difícil de montar. Se pesquisar o funcionamento dos outros SGBDs como o Oracle e o DB2 já é difícil, se simular o efeito do Double Reads gastou metade de uma madrugada (até DEADLOCK no SELECT eu consegui), o que realmente deu trabalho foi fazer o PPT animado. Enfim, foi trabalhoso, mas faço de bom grado para a comunidade. Críticas e sugestões são bem vindas (não deu tempo no Webcast).

Aos que não puderam assistir não tem problema. Acredito que o time do Virtual Pass irá disponibilizar a palestra em breve, mas eu já me antecipei e coloquei a palestra no meu canal do Youtube (em vídeos picados). A quem se interessar, segue o link:

Concorrência de Dados
http://www.youtube.com/playlist?list=PL70D92D06E08E6653&feature=plcp

Aos que desejarem os scripts e a base utilizados, vocês podem obter em:

http://cid-f4f5c630410b9865.office.live.com/self.aspx/ProjetosSQLServer/20120322_Concorrencia.rar

Só não entendi porque o Camtasia deu um zoom indesejado em algumas partes do vídeo, mas enfim… Espero que não ocorra na próxima. Agora é preparar o PPT do SQL Saturday 127.

[ ]s,

Gustavo

WEBCAST – Concorrência de dados no SQL Server

Olá Pessoal,

Começando a colocar os planos de SQL Server no ano 2012 em ação e a iniciativa de apostar um pouco mais em vídeo nesse ano, estou indo para mais uma gravação.

O VirtualPassBR está com uma iniciativa de transmitir WebCasts todas as terças e quintas e tive o prazer de ser convidado para fazer o primeiro de uma série de eventos que virão.

No dia 15 desse mês, falarei sobre concorrência em banco de dados com um foco maior no SQL Server embora deva dar algumas palavrinhas sobre esse assunto em outros SGBDs. O assunto realmente me desperta bastante interesse.

A concorrência é cruel

Entender a concorrência por dados é peça na chave na construção de aplicações que possam escalar. Meia dúzia de usuários podem conviver mesmo que a aplicação tenha erros, mas dificilmente veremos um grande banco transacionando sem nos atentarmos para o assunto concorrência. Infelizmente há muitos implementadores que desconhecem esse assunto e confiam na mão invisível (o Adam Smith do banco) ou apelam para o NOLOCK. Pretendo explorar um pouco mais assunto e tentarei cobrir essas brechas.

Falarei dos principais problemas envolvidos em situações concorrentes, os modelos de concorrência, os níveis de isolamento e algumas situações que acompanho no fórum.

Se você deseja conhecer um pouco mais sobre esse assunto faça já a inscrição clicando aqui. Vejo vocês por lá.

[ ]s,

Gustavo

Exportando consultas para documentos XML

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:

<Lancamentos>
  <Cliente Nome=Ronaldo>
    <Conta Agencia=3590-4Numero=97532-8>
      <Lancamento Data=2011-12-20Valor=6500.0000Tipo=C/>
      <Lancamento Data=2011-12-21Valor=1200.0000Tipo=D/>
      <Lancamento Data=2011-12-21Valor=2500.0000Tipo=D/>
    </Conta>
    <Conta Agencia=3590-4Numero=856413-0“>
      <Lancamento Data=2011-12-21Valor=3500.0000Tipo=C/>
      <Lancamento Data=2011-12-21Valor=2600.0000Tipo=C/>
    </Conta>
    <Conta Agencia=3590-4Numero=948766-0>
      <Lancamento />
    </Conta>
  </Cliente>
  <Cliente Nome=Adalton>
    <Conta Agencia=1004-9Numero=974322-8>

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

<Lancamentos><Cliente Nome=”Ronaldo”><Conta Agencia=”3590-4″ Numero=”97532-8″><Lancamento Data=”2011-12-20″ Valor=”6500.0000″ Tipo=”C”/>

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:

<Lancamentos>
<Cliente Nome=”Ronaldo”>
  <Conta Agencia=”3590-4″ Numero=”97532-8″>
   <Lancamento Data=”2011-12-20″ Valor=”6500.0000″ Tipo=”C”/>
   <Lancamento Data=”2011-12-21″ Valor=”1200.0000″ Tipo=”D”/>
   <Lancamento Data=”2011-12-21″ Valor=”2500.0000″ Tipo=”D”/>
  </Conta>
  <Conta Agencia=”3590-4″ Numero=”856413-0″>
   <Lancamento Data=”2011-12-21″ Valor=”3500.0000″ Tipo=”C”/>
   <Lancamento Data=”2011-12-21″ Valor=”2600.0000″ Tipo=”C”/>
  </Conta>
  <Conta Agencia=”3590-4″ Numero=”948766-0″>
   <Lancamento/>
  </Conta>
</Cliente>
<Cliente Nome=”Adalton”>
  <Conta Agencia=”1004-9″ Numero=”974322-8″>

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