Transformando linhas em colunas sem PIVOT

Olá pessoas,

Na semana passada, nós vimos como transformar linhas em colunas no SQL Server com PIVOT. Mas como nem sempre dá para contar com este recurso, e algumas vezes, ele não supre 100% das nossas necessidades, vou apresentar alguns exemplos de como fazer PIVOT sem utilizar PIVOT.

Para estes exemplos, estarei utilizando da mesma massa de dados da semana anterior:

DECLARE @CONTAS TABLE (
	ANO SMALLINT, 
	BANCO VARCHAR(100), 
	TIPO VARCHAR(100), 
	VALOR MONEY
)

INSERT INTO @CONTAS VALUES
(2009,'BANCO ALVORADA S/A','INVESTIMENTOS',6175979775.42),
(2010,'BANCO ALVORADA S/A','INVESTIMENTOS',6486892688.53),
(2011,'BANCO ALVORADA S/A','INVESTIMENTOS',7905663406.86),
(2012,'BANCO ALVORADA S/A','INVESTIMENTOS',9613906084.01),
(2009,'BANCO ARBI S/A','INVESTIMENTOS',8102644.84),
(2009,'BANCO ARBI S/A','OUTROS',174343.35),
(2010,'BANCO ARBI S/A','INVESTIMENTOS',7935411.15),
(2010,'BANCO ARBI S/A','OUTROS',119885.82),
(2011,'BANCO ARBI S/A','INVESTIMENTOS',8202652.29),
(2011,'BANCO ARBI S/A','OUTROS',114215.13),
(2012,'BANCO ARBI S/A','INVESTIMENTOS',8407843.72),
(2012,'BANCO ARBI S/A','OUTROS',81746.25)

Conforme o primeiro exemplo da semana anterior, transformamos os registros de contas do tipo INVESTIMENTOS e OUTROS em duas colunas, utilizando PIVOT:

SELECT U.ANO, U.BANCO, U.INVESTIMENTOS, U.OUTROS
FROM @CONTAS AS C
PIVOT (
	SUM(C.VALOR) FOR
	C.TIPO IN (INVESTIMENTOS, OUTROS)  
) AS U

Sem PIVOT, é possível fazer o mesmo procedimento com CASE WHEN:

SELECT C.ANO, C.BANCO, 
	INVESTIMENTOS = SUM(CASE WHEN C.TIPO = 'INVESTIMENTOS' THEN C.VALOR END),
	OUTROS = SUM(CASE WHEN C.TIPO = 'OUTROS' THEN C.VALOR END)
FROM @CONTAS AS C
GROUP BY C.ANO, C.BANCO

E no SQL Server 2012+, com IIF:

SELECT C.ANO, C.BANCO, 
	INVESTIMENTOS = SUM(IIF(C.TIPO = 'INVESTIMENTOS', C.VALOR, NULL)),
	OUTROS = SUM(IIF(C.TIPO = 'OUTROS', C.VALOR, NULL))
FROM @CONTAS AS C
GROUP BY C.ANO, C.BANCO

Também é possível fazer com subqueries, mas como não é performático, então ignoraremos esta alternativa.

O segundo exemplo, transformamos os registros de anos em colunas respectivas a estes anos, utilizando PIVOT:

SELECT U.BANCO, U.TIPO, U.[2009], U.[2010], U.[2011], U.[2012]
FROM @CONTAS AS C
PIVOT (
	SUM(C.VALOR) FOR
	C.ANO IN ([2009], [2010], [2011], [2012])  
) AS U

Sem PIVOT, é possível fazer o mesmo procedimento com CASE WHEN:

SELECT C.BANCO, C.TIPO,
	[2009] = SUM(CASE WHEN C.ANO = 2009 THEN C.VALOR END),
	[2010] = SUM(CASE WHEN C.ANO = 2010 THEN C.VALOR END),
	[2011] = SUM(CASE WHEN C.ANO = 2011 THEN C.VALOR END),
	[2012] = SUM(CASE WHEN C.ANO = 2012 THEN C.VALOR END)
FROM @CONTAS AS C
GROUP BY C.BANCO, C.TIPO

E no SQL Server 2012+, com IIF:

SELECT C.BANCO, C.TIPO,
	[2009] = SUM(IIF(C.ANO = 2009, C.VALOR, NULL)),
	[2010] = SUM(IIF(C.ANO = 2010, C.VALOR, NULL)),
	[2011] = SUM(IIF(C.ANO = 2011, C.VALOR, NULL)),
	[2012] = SUM(IIF(C.ANO = 2012, C.VALOR, NULL))
FROM @CONTAS AS C
GROUP BY C.BANCO, C.TIPO

E agora, algumas questões que são mais fáceis de resolver com CASE WHEN e IIF, como criar regras mais específicas para as colunas utilizando outras funções e outras regras de agrupamento:

SELECT U.BANCO, U.TIPO,
	U.[2008], U.[2009], U.[2010], U.[2011], U.[2012], 
	[2009_2010] = U.[2009] + U.[2010],
	[2011_2012] = U.[2011] + U.[2012], 
	TOTAL = U.[2009] + U.[2010] + U.[2011] + U.[2012]
FROM @CONTAS AS C
PIVOT (
	SUM(C.VALOR) FOR
	C.ANO IN ([2008], [2009], [2010], [2011], [2012])  
) AS U

SELECT C.BANCO, C.TIPO,
	[2008] = SUM(CASE WHEN C.ANO = 2008 THEN C.VALOR ELSE 0 END),
	[2009] = SUM(CASE WHEN C.ANO = 2009 THEN C.VALOR ELSE 0 END),
	[2010] = SUM(CASE WHEN C.ANO = 2010 THEN C.VALOR ELSE 0 END),
	[2011] = SUM(CASE WHEN C.ANO = 2011 THEN C.VALOR ELSE 0 END),
	[2012] = SUM(CASE WHEN C.ANO = 2012 THEN C.VALOR ELSE 0 END),
	[!2012] = SUM(CASE WHEN C.ANO <> 2012 THEN C.VALOR ELSE 0 END),
	[<2012] = SUM(CASE WHEN C.ANO < 2012 THEN C.VALOR ELSE 0 END),
	[2009_2010] = SUM(CASE WHEN C.ANO IN (2009, 2010) THEN C.VALOR ELSE 0 END),
	[2011_2012] = SUM(CASE WHEN C.ANO BETWEEN 2011 AND 2012 THEN C.VALOR ELSE 0 END),
	TOTAL = SUM(C.VALOR),
	MEDIA = AVG(C.VALOR)
FROM @CONTAS AS C
GROUP BY C.BANCO, C.TIPO

SELECT C.BANCO, C.TIPO,
	[2008] = SUM(IIF(C.ANO = 2008, C.VALOR, 0)),
	[2009] = SUM(IIF(C.ANO = 2009, C.VALOR, 0)),
	[2010] = SUM(IIF(C.ANO = 2010, C.VALOR, 0)),
	[2011] = SUM(IIF(C.ANO = 2011, C.VALOR, 0)),
	[2012] = SUM(IIF(C.ANO = 2012, C.VALOR, 0)),
	[!2012] = SUM(IIF(C.ANO <> 2012, C.VALOR, 0)),
	[<2012] = SUM(IIF(C.ANO < 2012, C.VALOR, 0)),
	[2009_2010] = SUM(IIF(C.ANO IN (2009, 2010), C.VALOR, 0)),
	[2011_2012] = SUM(IIF(C.ANO BETWEEN 2011 AND 2012, C.VALOR, 0)),
	TOTAL = SUM(C.VALOR),
	MEDIA = AVG(C.VALOR)
FROM @CONTAS AS C
GROUP BY C.BANCO, C.TIPO

E a composição de um agrupamento de linhas para colunas com considerando mais de uma coluna:

SELECT BANCO = COALESCE(U_I.BANCO, U_O.BANCO), 
	INV_2009 = U_I.[2009], 
	INV_2010 = U_I.[2010], 
	INV_2011 = U_I.[2011], 
	INV_2012 = U_I.[2012], 
	OUT_2009 = U_O.[2009], 
	OUT_2010 = U_O.[2010], 
	OUT_2011 = U_O.[2011], 
	OUT_2012 = U_O.[2012]
FROM 
(SELECT BANCO, ANO, VALOR FROM @CONTAS WHERE TIPO = 'INVESTIMENTOS') AS C_I
PIVOT (
	SUM(C_I.VALOR) FOR
	C_I.ANO IN ([2009], [2010], [2011], [2012])  
) AS U_I
FULL OUTER JOIN
(SELECT BANCO, ANO, VALOR FROM @CONTAS WHERE TIPO = 'OUTROS') AS C_O
PIVOT (
	SUM(C_O.VALOR) FOR
	C_O.ANO IN ([2009], [2010], [2011], [2012])  
) AS U_O
ON U_I.BANCO = U_O.BANCO
SELECT C.BANCO,
	INV_2009 = SUM(CASE WHEN C.ANO = 2009 AND TIPO = 'INVESTIMENTOS' THEN C.VALOR ELSE 0 END),
	INV_2010 = SUM(CASE WHEN C.ANO = 2010 AND TIPO = 'INVESTIMENTOS' THEN C.VALOR ELSE 0 END),
	INV_2011 = SUM(CASE WHEN C.ANO = 2011 AND TIPO = 'INVESTIMENTOS' THEN C.VALOR ELSE 0 END),
	INV_2012 = SUM(CASE WHEN C.ANO = 2012 AND TIPO = 'INVESTIMENTOS' THEN C.VALOR ELSE 0 END), 
	OUT_2009 = SUM(CASE WHEN C.ANO = 2009 AND TIPO = 'OUTROS' THEN C.VALOR ELSE 0 END), 
	OUT_2010 = SUM(CASE WHEN C.ANO = 2010 AND TIPO = 'OUTROS' THEN C.VALOR ELSE 0 END), 
	OUT_2011 = SUM(CASE WHEN C.ANO = 2011 AND TIPO = 'OUTROS' THEN C.VALOR ELSE 0 END), 
	OUT_2012 = SUM(CASE WHEN C.ANO = 2012 AND TIPO = 'OUTROS' THEN C.VALOR ELSE 0 END)
FROM @CONTAS AS C
GROUP BY C.BANCO
SELECT C.BANCO,
	INV_2009 = SUM(IIF(C.ANO = 2009 AND TIPO = 'INVESTIMENTOS', C.VALOR, 0)),
	INV_2010 = SUM(IIF(C.ANO = 2010 AND TIPO = 'INVESTIMENTOS', C.VALOR, 0)),
	INV_2011 = SUM(IIF(C.ANO = 2011 AND TIPO = 'INVESTIMENTOS', C.VALOR, 0)),
	INV_2012 = SUM(IIF(C.ANO = 2012 AND TIPO = 'INVESTIMENTOS', C.VALOR, 0)), 
	OUT_2009 = SUM(IIF(C.ANO = 2009 AND TIPO = 'OUTROS', C.VALOR, 0)), 
	OUT_2010 = SUM(IIF(C.ANO = 2010 AND TIPO = 'OUTROS', C.VALOR, 0)), 
	OUT_2011 = SUM(IIF(C.ANO = 2011 AND TIPO = 'OUTROS', C.VALOR, 0)), 
	OUT_2012 = SUM(IIF(C.ANO = 2012 AND TIPO = 'OUTROS', C.VALOR, 0))
FROM @CONTAS AS C
GROUP BY C.BANCO

Transformando linhas em colunas com PIVOT

Olá pessoas,

A mágica de transformar linhas em colunas no SQL Server com PIVOT não é tão simples, o que requer um pouco de domínio de T-SQL, para saber como utiliza-la, além de não ser pego por alguns erros comuns.

Neste primeiro artigo, vou apresentar alguns exemplos simples, e apresentar um problema comum de se trabalhar com PIVOT.

De início, temos uma massa de dados:

DECLARE @CONTAS TABLE (
  ANO SMALLINT,
  BANCO VARCHAR(100),
  TIPO VARCHAR(100),
  VALOR MONEY
)

INSERT INTO @CONTAS VALUES
(2009,'BANCO ALVORADA S/A','INVESTIMENTOS',6175979775.42),
(2010,'BANCO ALVORADA S/A','INVESTIMENTOS',6486892688.53),
(2011,'BANCO ALVORADA S/A','INVESTIMENTOS',7905663406.86),
(2012,'BANCO ALVORADA S/A','INVESTIMENTOS',9613906084.01),
(2009,'BANCO ARBI S/A','INVESTIMENTOS',8102644.84),
(2009,'BANCO ARBI S/A','OUTROS',174343.35),
(2010,'BANCO ARBI S/A','INVESTIMENTOS',7935411.15),
(2010,'BANCO ARBI S/A','OUTROS',119885.82),
(2011,'BANCO ARBI S/A','INVESTIMENTOS',8202652.29),
(2011,'BANCO ARBI S/A','OUTROS',114215.13),
(2012,'BANCO ARBI S/A','INVESTIMENTOS',8407843.72),
(2012,'BANCO ARBI S/A','OUTROS',81746.25)

SELECT ANO, BANCO, TIPO, VALOR FROM @CONTAS

Para criar transformar as linhas de contas do tipo ‘INVESTIMENTOS’ e ‘OUTROS’ em linhas com uma coluna com os valores respectivos a ‘INVESTIMENTOS’ e outra a ‘OUTROS’, utilizaremos a expressão PIVOT sobre a tabela @CONTAS, somando os valores da coluna ‘VALOR’ para onde a coluna ‘TIPO’ apresenta os valores ‘INVESTIMENTOS’ e ‘OUTROS’, assim temos a primeira consulta e resultado:

SELECT U.ANO, U.BANCO, U.INVESTIMENTOS, U.OUTROS
FROM @CONTAS AS C
PIVOT (
  SUM(VALOR) FOR
  TIPO IN (INVESTIMENTOS, OUTROS)
) AS U

Desta forma, os registros da tabela @CONTAS se transformam na PIVOT com o alias/apelido U (pode ser outra alias, sem problemas), assim as colunas TIPO e VALOR deixaram de existir, e são agrupadas por ANO e BANCO nas colunas INVESTIMENTOS e OUTROS.

Obs.: Não é obrigatório especificar as colunas no SELECT, pois ele só reconhecerá as colunas do alias U, mas especifiquem para ficar organizado.

Abaixo, um exemplo utilizando a coluna VALOR agrupada por BANCO e TIPO para cada um dos anos da coluna ANO:

SELECT U.BANCO, U.TIPO, U.[2009], U.[2010], U.[2011], U.[2012]
FROM @CONTAS AS C
PIVOT (
  SUM(C.VALOR) FOR
  C.ANO IN ([2009], [2010], [2011], [2012])
) AS U

Uma preocupação que se deve ter ao utilizar PIVOT é especificar claramente as colunas que serão agrupadas e quais as colunas que não serão, pois o SQL Server ainda não lê pensamentos, o que gera um erro bem comum, como apresentado abaixo.

“Então, estou com aquele script de PIVOT (A), mas quando removi a coluna ANO, para agrupar pelas colunas BANCO, INVESTIMENTOS e OUTROS, mas não deu certo, começou a aparecer duplicado a coluna BANCO (B).”

(A):

SELECT U.BANCO, U.INVESTIMENTOS, U.OUTROS
FROM @CONTAS AS C
PIVOT (
  SUM(VALOR) FOR
  TIPO IN (INVESTIMENTOS, OUTROS)
) AS U

(B):

Este problema se dá ao fato, que independente de você tirar as colunas especificadas no SELECT, a PIVOT continuará sendo feita sobre todas as colunas da tabela, ou seja, tirou a coluna ‘ANO’ do SELECT, não quer dizer que a PIVOT vai esquecer que a coluna ‘ANO’ existe.

Para evitar este problema, ao invés de utilizar diretamente a tabela para a PIVOT, especifique em uma subconsulta, quais colunas a PIVOT utilizará, exemplo:

SELECT U.BANCO, U.INVESTIMENTOS, U.OUTROS
FROM (
  SELECT BANCO, TIPO, VALOR
  FROM @CONTAS
) AS C
PIVOT (
  SUM(C.VALOR) FOR
  C.TIPO IN (INVESTIMENTOS, OUTROS)
) AS U

Próximas semanas, eu vou apresentar o UNPIVOT e também ensinar as limitações destas cláusulas e mágicas, assim como explicar como fazer as mesmas coisas sem utilizar PIVOT e UNPIVOT.

Entendendo EXCEPT, INTERSECT e UNION do SQL

Olá pessoas!

Não é de hoje que vejo o pessoal que está iniciando com SQL tendo problemas como EXCEPT, INTERSECT e UNION, ou não sabendo que eles existem. Desta forma, vamos à explicação!

A principal ideia destas cláusulas é ‘somar’ e ‘subtrair’ os registros de duas ou mais consultas.

___

UNION ALL e UNION

Iniciando com o UNION ALL, ele permite unir os registros de duas consultas. No exemplo abaixo, temos a tabela @A com os registros José, Maria e João, e a tabela @B com os registros Maria, Guilherme e Vitória.

Como no caso o registro Maria com ID 2 está presente nas duas tabelas, com UNION ALL, o registro irá se repetir. Caso queira que os registros iguais não se repitam, basta usar UNION, conforme o exemplo abaixo.

Internamente no SQL Server, o que o UNION faz é um UNION ALL seguindo de um DISTINCT, ou seja, as duas consultas abaixo tem o mesmo plano de execução no SQL Server e funcionam da mesma forma.

SELECT ID, NOME FROM @A
UNION
SELECT ID, NOME FROM @B
SELECT DISTINCT ID, NOME
FROM (
	SELECT ID, NOME FROM @A
	UNION ALL
	SELECT ID, NOME FROM @B
) AS C

Plano de execução:

Desta forma, evite usar UNION quando UNION ALL já é o suficiente.

___

EXCEPT

Quando se quer que apareçam os registros da tabela A que não são iguais aos da tabela B, temos a opção de ‘subtrair’ os registros utilizando EXCEPT.

___

INTERSECT

Por fim, o uso de INTERSECT permite uma intersecção entre as consultas. Que diabos é intersecção? Então, seria como exibir somente os registros que as duas consultas têm em comum.

___

Considerações finais

Abaixo a consulta completa deste post:

DECLARE @A TABLE (
	ID INT NOT NULL,
	NOME VARCHAR(10) NOT NULL
)

DECLARE @B TABLE (
	ID INT NOT NULL,
	NOME VARCHAR(10) NOT NULL
)

INSERT @A
VALUES (1, 'José'), (2, 'Maria'), (3, 'João')

INSERT @B
VALUES (2, 'Maria'), (3, 'Guilherme'), (4, 'Vitória')

SELECT Título = 'UNION ALL'

SELECT ID, NOME FROM @A
UNION ALL
SELECT ID, NOME FROM @B

SELECT Título = 'UNION'

SELECT ID, NOME FROM @A
UNION
SELECT ID, NOME FROM @B

SELECT Título = 'EXCEPT'

SELECT ID, NOME FROM @A
EXCEPT
SELECT ID, NOME FROM @B

SELECT Título = 'INTERSECT'

SELECT ID, NOME FROM @A
INTERSECT
SELECT ID, NOME FROM @B

___

Artigos relacionados:

Entendendo os ‘JOIN’s do SQL

Verificar a versão dos dados de uma tabela por CHECKSUM

Olá pessoas!

Recentemente, encontrei um script bem interessante para realizar o CHECKSUM de todos os dados de todas as colunas de uma tabela, fiz algumas alterações mínimas para reduzir o código do script original, e ficou desta forma:

DECLARE @schema_name sysname
SET @schema_name = 'dbo'

DECLARE @table_name sysname
SET @table_name = 'Nome da tabela'

DECLARE @column_list VARCHAR(MAX)

SELECT @column_list =
	COALESCE(@column_list + ', ', '') + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
AND TABLE_SCHEMA = @schema_name

DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT CHECKSUM_AGG(CHECKSUM({@column_list})) FROM [{@schema_name}].[{@table_name}]'

SET @sql = REPLACE(@sql, '{@column_list}', @column_list)
SET @sql = REPLACE(@sql, '{@schema_name}', @schema_name)
SET @sql = REPLACE(@sql, '{@table_name}', @table_name)

EXEC (@sql)

Esta consulta retorna o CHECKSUM de todos os dados da tabela de forma rápida (na maioria das vezes), e permite verificar por meio deste CHECKSUM se a tabela foi ou não alterada, sendo uma ótima alternativa comparar alterações em tabelas similares em bancos distintos e criar uma estrutura simples de versionamento.

A única limitação que encontrei para este script é a necessidade de um tratamento diferenciado para colunas XML:

Msg 8116, Level 16, State 4, Line 1
Argument data type xml is invalid for argument X of checksum function.

Referências

Script original:
http://stackoverflow.com/questions/1560306/calculate-hash-or-checksum-for-a-table-in-sql-server

Função CHECKSUM:
http://msdn.microsoft.com/en-us/library/ms189788.aspx

Função CHECKSUM_AGG:
http://msdn.microsoft.com/en-us/library/ms188920.aspx

Artigos relacionados:

Comparando a estrutura de tabelas diferentes
http://sqlfromhell.wordpress.com/2010/01/09/query-from-hell-comparando-a-estrutura-de-tabelas-diferentes/

Red Gate – Overview – SQL Data Compare
http://sqlfromhell.wordpress.com/2011/05/23/red-gate-overview-sql-data-compare/

Visual Studio 2010 – Data Compare
http://sqlfromhell.wordpress.com/2011/01/27/visual-studio-2010-data-compare/

Aniversário de 3 anos do SQL From Hell!!

Há três anos iniciou o SQL From Hell, sem muita perspectiva do que iria se tornar, e hoje, me surpreendo que um blog escrito em português sobre assuntos técnicos bem específicos, tenha conseguido ajudar tantas pessoas, além das várias críticas e elogios que tem recebidos.

Procurei nestes três anos tratar assuntos que eu gosto, como SMO, SQL CLR, Service Broker, FOR XML e XQuery, também artigos que escrevi com o objetivo de disponibilizar tutoriais para ajudar no dia-a-dia dos novos DBAs, como habilitar SQL Authentication, habilitar conexão remota, alterar as portas de conexão do SQL Server, usuários orfãos, scripts para validar CNPJ, entender JOINs, scripts de cidades, scripts para o dicionários de dados e vários outros assuntos, que algumas vezes não estavam diretamente ligados com SQL Server, como os tutoriais de NoSQL e alguns artigos sobre gestão e carreira.

Obrigado todos aqueles que contribuíram com esta iniciativa!!!

Monitorando Deadlocks do SQL Server com SMO

Na semana passada vimos um exemplo de Deadlock com SQL Server, e para monitorar e rastrear eventos deste gênero podemos optar por várias alternativas, dentre elas a utilização de Trace Flags e SQL Server Profiler, e também podemos monitorar de forma bem transparente com os eventos do SMO.

De uma maneira bem simples, o código abaixo monitora os eventos de Deadlock de uma determinada instancia do SQL Server, e quando ocorre uma Deadlock, ele informa os SPID que estão relacionadas a este evento, e gera um Deadlock File para obter maiores detalhes sobre o deadlock.

using System;
using System.IO;
using System.Linq;
using System.Xml.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    static class DemoEvent3
    {
        public static void Run()
        {
            const string serverName = @".\SQLEXPRESS";

            var server = new Server(serverName);

            var eventSet = new ServerTraceEventSet(
                ServerTraceEvent.LockDeadlock,
                ServerTraceEvent.LockDeadlockChain,
                ServerTraceEvent.DeadlockGraph
                );

            server.Events.SubscribeToEvents(eventSet, OnDeadlock);

            server.Events.StartEvents();

            Console.ReadKey();

            server.Events.StopEvents();

            server.Events.UnsubscribeFromEvents(eventSet);
        }

        private static void OnDeadlock(object sender, ServerEventArgs e)
        {
            switch (e.EventType)
            {
                case EventType.DeadlockGraph:
                    DeadlockGraph(e);
                    break;
                case EventType.LockDeadlockChain:
                    LockDeadlockChain(e);
                    break;
                case EventType.LockDeadlock:
                    Console.WriteLine("Data/Hora: {0:dd/MM/yyyy HH:mm}", e.PostTime);
                    Console.WriteLine("Vítima escolhida: SPID {0}", e.Spid);
                    break;
            }

            Console.WriteLine();
        }

        private static void LockDeadlockChain(ServerEventArgs e)
        {
            var textData = e.Properties.FirstOrDefault(prop => prop.Name == "TextData");

            if (textData == null) return;

            Console.WriteLine("Data/Hora: {0:dd/MM/yyyy HH:mm}", e.PostTime);
            Console.WriteLine(((string)textData.Value).TrimEnd());
        }

        private static void DeadlockGraph(ServerEventArgs e)
        {
            var path = string.Format(@"C:\TEMP\{0}.xdl", Guid.NewGuid());

            var textData = e.Properties.FirstOrDefault(prop => prop.Name == "TextData");

            if (textData == null) return;

            var xml = XDocument.Parse(((string)textData.Value))
                .Elements("TextData")
                .Elements("deadlock-list")
                .FirstOrDefault();

            if (xml == null) return;

            using (var file = new StreamWriter(path)) file.Write(xml.ToString(SaveOptions.DisableFormatting));

            Console.WriteLine("Data/Hora: {0:dd/MM/yyyy HH:mm}", e.PostTime);
            Console.WriteLine("Arquivo gerado: {0}", path);
        }
    }
}

A execução do código:

Monitorando Deadlock com SMO

Os arquivos gerados:

Deadlock Files

E um arquivo de deadlock file gerado pelo SMO aberto no SQL Server Management Studio, possuindo informações sobre os processos, os objetos em lock e a query que foi executada.

Deadlock File

Assim terminamos os artigos sobre SMO, espero que tenham gostado de conhecer um pouco mais sobre esta feature.

Exemplo de Deadlock no SQL Server

Deadlock acontece quando dois ou mais processos são impedidos de prosseguir pois um estar bloqueando o outro, como exemplo, duas pessoas estão querendo usar o mesmo telefone para ligar para números diferentes, enquanto uma delas não ceder sua vez, nenhuma irá conseguir ligar.

No nível de transações de banco de dados não é muito diferente, teremos duas tabelas em nosso banco de dados:

CREATE TABLE Pagamentos (ID INT PRIMARY KEY)
GO
CREATE TABLE Contas (ID INT PRIMARY KEY)
GO

Agora, imagine que um cliente, o senhor 52, irá abrir uma transação para inserir um registro na tabela de pagamentos:

BEGIN TRAN
GO
INSERT INTO Pagamentos VALUES(1)
GO

E ao mesmo tempo, outro cliente, o senhor 55, irá inserir um registro na tabela de contas:

BEGIN TRAN
GO
INSERT INTO Contas VALUES(1)
GO

Até ai, tudo bem, as transações estão abertas, o senhor 55 e o senhor 52 estão utilizando tabelas totalmente diferentes. Mas o senhor 55 lembra que além de inserir um registro na tabela de contas, ele precisa inserir um registro na tabela de pagamentos na mesma transação, assim ele terá que esperar que o senhor 52 termine a transação dele primeiro.

INSERT INTO Pagamentos VALUES(1)
GO

Executando...

Por enquanto nada crítico, é só um cliente esperando o outro terminar.

Mas o senhor 52 também inventa de querer inserir um registro na tabela de Contas:

INSERT INTO Contas VALUES(1)
GO

Dai acontece o Deadlock, o senhor 52 esperando o senhor 55 terminar a transação dele, e o senhor 55 esperando o senhor 52. Como se trata de uma Deadlock bem simples, o SQL Server vai rapidamente escolher qual dos dois clientes tem a preferência, e por consequência definirá sua vítima, que é o cliente que terá que morrer para que aquele que possui preferência consiga terminar seu processo.

E assim, no meu cenário, senhor 52 foi brutalmente assassinado (a transação sofre um ROLLBACK pelo próprio SQL Server, de forma que todos os rastros da vítima sejam apagados):

RIP

Msg 1205, Level 13, State 47, Line 4
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

E em homenagem a todos os processos que diariamente são vítimas de Deadlock:

Em homenagem a todos os processos que diariamente são vítimas de Deadlock

|| EDIT 2012-03-29
|| Via: http://pessoalex.wordpress.com/2012/03/29/exemplo-de-deadlock-no-sql-server/

Deadlock