SQLServerDF Encontro V – PASS Summit e Disaster Recovery

Bom Dia Pessoal,

No dia 25/11 (a última quarta-feira) participei do 5º encontro do grupo SQL Pass no auditório da Microsoft no DF muito bem representado por nada menos que o Luciano Moreira Caixeta. O encontro foi divido em duas partes. Na primeira parte, o Luti expôs um pouco sobre o SQL Pass Summit 2009 que ocorreu em Seatle. Não participei desse evento, mas foi passada uma visão geral do evento, as sessões, o altíssimo nível dos palestrantes, os custos envolvidos e os passatempos das horas vagas (afinal não são 24 horas de SQL Server por dia de evento). O evento é sem dúvida fantástico e aqueles que querem tornar seus conhecimento sobre SQL Server mais sólidos, esse é o evento. Tem alguns pré-requisitos como estar com o inglês em dia, gostar muito de SQL Server e reservar alguns dólares para o evento e para as compras (não dá pra ir nos EUA e voltar de mãos vazias). As inscrições para as próximas edições do evento já estão disponíveis no link oficial da organização do SQL Pass em http://www.sqlpass.org/.

A segunda parte do evento, como é de praxe, foi voltada às apresentações que envolvem algo mais técnico do SQL Server como novas features, melhores práticas, etc. Aproveitando a palestra de Disaster Recovery – Tópicos Avançados que fiz no SQL Server Day, eu apresentei o mesmo tema no encontro do Pass.

Embora muito parecidas, uma das diferenças em relação à apresentação anterior (principalmente por conta dos vários feedbacks) foi a adição de alguns slides extras como os referentes ao recurso de Database Snapshot que não estava contemplado anteriormente.

No SQL Server Day, meu limite de tempo era de apenas uma hora. Já nesse encontro do SQL Server Pass DF, o tempo aumentou consideravelmente e pude palestrar por duas horas (embora tenha passado uns 15 minutos do tempo mesmo assim). Esse aumento foi muito positivo para incrementar e explorar mais o conteúdo, para as dúvidas e discussões que apareceram.

O tema "Disaster Recovery" é sem dúvida um dos mais importantes na carreira de qualquer DBA e profissionais de infraestrutura. Preparar-se para as potenciais falhas, saber o que fazer em momentos de crises e utilizar a estratégia certa para disponibilizar novamente o ambiente são pontos chaves.

Há várias implementações voltadas a Disaster Recovery. Database Mirroring, Log Shipping, Replicação, etc são alguns exemplos (Hot & Warm Server), mas dada à amplitude desse assunto, a apresentação foi limitada especificamente ao assunto Backup & Restore (Cold Server).

A apresentação foi dividida em duas partes. Na primeira parte revisei os conceitos básicos como a estrutura básica do log de transações, como as páginas são alteradas no SQL Server, os modelos de estruturação e comportamento do log (Recovery Models) e as estratégias básicas de backup como o backup full, o diferencial e o de log. Já na segunda parte, aproveitei para explorar algumas implementações mais avançadas como o backup de filegroup e suas possibilidades de restauração (em especial a restauração de filegroups individuais se desejável). O ponto interessante é que esse tipo de restauração normalmente é cercada de mitos e lendas urbanas como a perda dos relacionamentos das tabelas em filegroups separados ou ainda a desincronização dos índices. Na apresentação foi possível ver que nada disso é verdade e que o log de transações "sabe" de tudo para evitar que essas distorções ocorram. Ainda na segunda parte foram abordados a restauração via standby, o tail log e algumas opções relacionadas à gravação e consistência do backup como o CheckSum e o parâmetro CONTINUE_AFTER_ERROR.

Dentre as várias pessoas que assistiram a apresentação estavam boa parte da equipe de banco de dados do meu atual emprego. O maior desafio dessa equipe é manter toda a parte de armazenamento lógico de um sistema financeiro cooperativo em ampla expansão em um ambiente de missão crítica.

Além das responsabilidades de administrar e manter o DB2 repousando sobre um ZLinux no Mainframe, a parte de SQL Server representa pouco mais de 700 bases distribuídas por 20 servidores (todos clusterizados) em um ambiente clusterizado ocupando cerca de 8TB em produção (só não aumenta por conta do expurgo). Há muitas implementações voltadas para o SQL Server 2000 e 2005.

A presença dessa equipe na apresentação foi muito importante, pois, além dos exemplos básicos, pudemos utilizar vários exemplos do nosso ambiente em situações do nosso cotidiano. Dúvidas muito interessantes como "Qual deve ser o tamanho do meu arquivo de Log ?", "Como funciona exatamente a marcação das páginas que vão para o backup diferencia ?", etc complementaram bastante a apresentação.

Gostaria de agradecer a todos que participaram de mais esse encontro e assistiram à palestra. Eu não percebi na hora, mas o Luti fez a gravação e a transmitiu em tempo real (achei que só seria gravado, mas não transmitido). Agradeço também aqueles que assistiram à transmissão. Espero que muitos outros como esse se repitam. O material será disponibilizado na lista oficial do grupo em http://groups.google.com/group/sqlserverdf. Aos interessados o link abaixo possui o PDF da apresentação.

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/Webcast/20091125%5E_SQLServerPassDF/20091025%5E_SQL%5E_Server%5E_Pass%5E_DF%5E_Disaster%5E_Recovery.pdf

[ ]s,

Gustavo

SQL Server Day – Depois do Evento

Boa Noite Pessoal,

O SQL Server Day acabou e na minha opinião foi um tremendo sucesso. Todo evento ao vivo tem seus imprevistos e particularidades e com esse não foi diferente. Tivemos sim atrasos em algumas apresentações, alguns PPTs fora do lugar e os tradicionais problemas de transmissão por conta da banda e do Live Meeting. Há um destaque para o "latido do cachorro" e a introdução de música do Michael Jackson durante algumas das apresentações (foi muito criativa a música). Após o SQL Server Day, o meu vocabulário de TI do dia a dia foi completamente enriquecido com algumas expressões como "queries assassinas no servidor", "mentes brilhantes podem tentar fazer a consulta desse jeito" e "usar software pirata é uma discussão muito mais filosófica" e "mudar o toalete de lugar ao invés de modificar a aplicação".

Não consegui assistir todas as 12 horas de palestras, mas das que pude presenciar duas me chamaram bastante a atenção. As palestras "Solução Avançada de Problemas com Extended Events" ministrada por Vladimir Magalhães e Análise de Desempenho utilizando as Estatísticas de Espera ministrada por Alex Rosa. Além dessas vi outras palestras muito boas, mas esses dois temas possuem material muito escasso e foram muito bem conduzidos pelos apresentadores. Com certeza passarei a utilizar os conceitos abordados dessas apresentações nas salas de aula, no ambiente de trabalho e nos clientes.

A minha apresentação estava prevista para às 19:00. Em virtude dos atrasos acabei apresentando um pouco depois das 20:00. Tive que acelerar, pois, o tempo estava curto e ainda havia mais gente para apresentar. Embora tenha me programado para apresentar em uma hora, é realmente difícil explicar tudo o que eu gostaria e da forma como gostaria em apenas uma hora. Se fosse uma aula, possivelmente um tempo de quatro horas me permitiria explicar mais tranqüilamente. Não pude repassar o tópico do Partial Restore e realmente fiquei devendo. Estou pensando em refazer a apresentação por conta própria, pois, assim poderia "extender" o tempo da forma que fosse necessário. Se eu o fizer certamente publicarei para todos.

Após terminar a apresentação muitos perguntaram-me se eu iria disponibilizá-la juntamente com os scripts. Assim como os demais, todo o material será disponibilizado no site oficial do evento na sessão de downloads. Aproveitei para disponibilizar parte do material (a apresentação não está inclusa) também no blog. Aos interessados seguem os links para Download:

Apresentação (em formato PDF)

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/Webcast/20091107%5E_SQLServerDay%5E_DR/SQL%5E_Server%5E_Day%5E_Disaster%5E_Recovery%5E_Gustavo%5E_Maia%5E_Aguiar.pdf

Scripts Utilizados (Formato SQL Server 2008)

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/Webcast/20091107%5E_SQLServerDay%5E_DR/Disaster%20Recovery%5E_SSD%5E_Scripts.rar

Gostaria novamente de agradecer a todos que assistiram a apresentação, ao Alexandre pelo convite para participar do evento, aos moderadores Marcondes, Júnior Galvão e Vladimir Magalhães e demais por terem me ajudado na moderação, aqueles que apoiaram antes, durante e após o eventos, aqueles que deixaram seus elogios, críticas e sugestões e a todos que de uma forma ou de outra contribuíram para o evento.

[ ]s,

Gustavo

SQL Server Day

Boa Noite Pessoal,

Talvez vocês já tenham visto a notícia em outros sites, mas mesmo com atraso é bom avisar para que ninguém fique de fora. Gostaria de convidar a todos para participar do SQL Server Day. Trata-se de um evento de 12 horas de palestras dedicadas ao SQL Server. Esse evento será feito por alguns profissionais incluindo MVPs, influenciadores e líderes de comunidades e células acadêmicas. O evento ocorrerá no dia 07/11/2009 (está bem próximo) e começará às 09:30 e finaliza às 22:00. Abaixo a grade de palestras programadas:

09:30 – 10:00 – Abertura do Evento
10:00 – 11:00 – Resource Governor e Policy Management
11:00 – 12:00 – Entenda porque o Query Optimizer é mais esperto que você
12:00 – 13:00 – SQL Azure Database
13:00 – 14:00 – Novos Recursos de Desenvolvimento do SQL Server 2008

14:00 – 15:00 – Compressão de Dados e Backup no Microsoft SQL Server 2008
15:00 – 16:00 – Entendendo TDE (Transparent Data Encryption)
16:00 – 17:00 – Entendendo as Common Table Expressions (CTE)
17:00 – 18:00 – Powershell Coletando e Analisando os Dados

18:00 – 19:00 – Solução Avançada de Problemas com Extended Events
19:00 – 20:00 – Disaster Recovery – Backup, Restore e Tópicos Avançados
20:00 – 21:00 – Integre seu código .NET com o SQL Server usando o CLR
21:00 – 22:00 – Análise de Desempenho utilizando as Estatísticas de Espera

Como podem ver há muitas palestras interessantes para assistir. As palestras serão todas transmitidas pela Internet gratuitamente. Recomenda-se apenas fazer uma inscrição prévia, pois, há limite de pessoas nas sessões a serem apresentadas. O evento foi dividido em três partes para que os participantes possam conciliar melhor as sessões que desejam assistir. Eu tive a honra de ser convidado a fazer parte desse time e palestrarei das 18:00 às 19:00 quando falarei sobre Disaster Recovery, mais especificamente sobre backups e restores.

Aos interessados, seguem alguns links úteis:

Site oficial do Evento – http://www.sqlserverday.com.br
Rede Social do Evento (Ning) – http://sqlserverday.ning.com
Inscrição do Evento (Parte I) – http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032430235&Culture=pt-BR
Inscrição do Evento (Parte II) – http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032430237&Culture=pt-BR
Inscrição do Evento (Parte III) – http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032430239&Culture=pt-BR

Aguardo vocês no dia 07 então. Não deixem de participar.

[ ]s,

Gustavo

Monitorando o seu banco de dados com o Performance Studio do SQL Server 2008

Boa Noite Pessoal,

Mal cheguei de férias e fui escolhido para ministrar um Webcast sobre o Performance Studio. Tirando a correria de um DBA recém chegado no trabalho, foi muito proveitoso. Eu conhecia o Performance Studio um pouco de relance e alguns livros mas nunca tinha ido mais a fundo. O legal de um Webcast de um tema que você não domina tanto é que você é intimado a estudar sobre o assunto. Foi ótimo, aproveitei para pesquisar bastante material sobre esse assunto e me convenci ainda mais de que essa é uma funcionalidade do 2008 que realmente vale muito ser mais explorada.

Felizmente as avaliações do Webcast foram positivas. Fico satisfeito de ter tido uma boa aprovação e que o pessoal tenha gostado. Realmente foi proveitoso para todos. Infelizmente não foi possível responder a todas as perguntas e um ficou de fora.

“Os recursos do Performance Studio estão disponíveis em todas as versões ?”

De fato o Performance Studio não está disponível para todas as versões. O SQL Server Express não foi contemplado, mas ao contrário do que poderia parecer, as edições Standard, Workgroup e Web também possuem os recursos do Performance Studio. Os detalhes estão no link abaixo:

http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx

Gostaria de agradecer novamente aqueles que compareceram ao Webcast. Conforme prometido o link abaixo permite efetuar o download da apresentação:

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/Webcast/20090325%7C_PerformanceStudio/20090325%7C_Webcast%7C_Performance%7C_Studio%7C_SQL%7C_Server%7C_2008%7C6.pdf

Aqueles que desejarem executar o código para criar os itens de coleta personalizados segue o código abaixo:

USE MSDB;

BEGIN TRY;

BEGIN TRANSACTION;

    DECLARE @collection_set_id INT
    EXEC [dbo].[sp_syscollector_create_collection_set]
        @name = N‘Coletor de Desempenho’,
        @collection_mode = 1,
        @description = N‘Coletar dados para mensurar o desempenho’,
        @days_until_expiration = 7,
        @collection_set_id = @collection_set_id OUTPUT

DECLARE @collector_type_uid UNIQUEIDENTIFIER
    = (SELECT TOP 1 collector_type_uid
        FROM [dbo].[syscollector_collector_types]
        WHERE name = N‘Generic T-SQL Query Collector Type’)

DECLARE @CollItemID INT

EXEC [dbo].[sp_syscollector_create_collection_item]
    @Name = N‘Conexões’,
    @parameters = N‘<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
            <Query>
                <Value>
                SELECT COUNT(*) As TotConexoes
                FROM sys.dm_exec_connections
                </Value>
                <OutputTable>Conexoes</OutputTable>
            </Query>
            <Databases UseSystemDatabases="false" UseUserDatabases="true"/>
        </ns:TSQLQueryCollector>’,
    @collection_item_id = @CollItemID OUTPUT,
    @frequency = 15,
    @collection_set_id = @collection_set_id,
    @collector_type_uid = @collector_type_uid

SET @collector_type_uid =
    (SELECT TOP 1 collector_type_uid
        FROM [dbo].[syscollector_collector_types]
        WHERE name = N‘Generic SQL Trace Collector Type’)

EXEC [dbo].[sp_syscollector_create_collection_item]
    @name = N‘Eventos do Profiler’,
    @parameters = N‘<ns:SqlTraceCollector xmlns:ns="DataCollectorType" use_default="0">
        <Events>
            <EventType name="Stored Procedures">
                <Event id="10" name="RPC:Completed" columnslist="1,3,11,35,12,28,13" />
            </EventType>
            <EventType name="TSQL">
                <Event id="12" name="SQL:BatchCompleted" columnslist="1,3,11,35,12,28,13" />
                <Event id="13" name="SQL:BatchStarting" columnslist="1,3,11,35,12,28,13" />
            </EventType>
    </Events>
            <Filters>
                <Filter columnid="35" columnname="DatabaseName"
                    logical_operator="AND" comparison_operator="LIKE"
                    value="AdventureWorks"/>
            </Filters>
        </ns:SqlTraceCollector>’,
    @collection_item_id = @CollItemID OUTPUT,
    @frequency = 15,
    @collection_set_id = @collection_set_id,
    @collector_type_uid = @collector_type_uid

SET @collector_type_uid =
    (SELECT TOP 1 collector_type_uid
        FROM [dbo].[syscollector_collector_types]
        WHERE name = N‘Performance Counters Collector Type’)

EXEC [dbo].[sp_syscollector_create_collection_item]
    @name = N‘Contadores do Perfmon’,
    @parameters = N‘
        <ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">
            <PerformanceCounters Objects="Processor" Counters="% Processor Time" Instances="*"/>
            <PerformanceCounters Objects="$(INSTANCE):General Statistics" Counters="User Connections"/>
            <PerformanceCounters Objects="$(INSTANCE):SQL Statistics" Counters="Batch Requests/sec"/>
        </ns:PerformanceCountersCollector>’,
    @collection_item_id = @CollItemID OUTPUT,
    @frequency = 15,
    @collection_set_id = @collection_set_id,
    @collector_type_uid = @collector_type_uid

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(),
        @ErrorSeverity INT = ERROR_SEVERITY(),
        @ErrorState INT = ERROR_STATE(),
        @ErrorNumber INT = ERROR_NUMBER(),
        @ErrorLine INT = ERROR_LINE(),
        @ErrorProcedure NVARCHAR(200) = ISNULL(ERROR_PROCEDURE(),’-‘)

    RAISERROR(14684,@ErrorSeverity,1,@ErrorNumber,@ErrorSeverity,@ErrorState,
            @ErrorProcedure, @ErrorLine, @ErrorMessage)

END CATCH
GO

O Webcast deverá estar disponível em alguns dias no site do MSDN, mas é possível acessá-lo através do link original. Aos interessados segue o link do Webcast

Monitorando o seu banco de dados com o Performance Studio do SQL Server 2008
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032409545&EventCategory=4&culture=pt-BR&CountryCode=BR

[ ]s,

Gustavo

Mais dicas e mais truques sobre consultas complexas no SQL Server

Boa Noite Pessoal,

Eu gostaria de agradecer aos que estiveram presentes no meu recente Webcast que tratava sobre dicas e truques sobre consultas complexas no SQL Server (focado no SQL Server 2005). Segui nos mesmo moldes do Webcast anterior, ou seja, demonstrar como problemas complexos podem ser resolvidos de forma simples.

Infelizmente devido há alguns problemas, me parece que o link não estava acessível e diversas pessoas não assistiram o Webcast. Acho realmente uma pena, pois, dessa vez trabalhei melhor algumas falhas do Webcast anterior e acredito que esse saiu ainda melhor. Aqueles que estiveram presentes devem ter percebido que ficou melhor que o anterior (novamente meus agradecimentos a esses que conseguiram). Para aqueles que não conseguiram, vou disponibilizar a resolução de cada um dos problemas nas próximas postagens do blog. Os interessados em assistir o Webcast gravado, utilizem o link abaixo:

Mais dicas e mais truques sobre consultas complexas no SQL Server
https://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=pt-BR&EventID=1032392772&CountryCode=BR

Dessa vez eu testei o link e baixei o Webcast.

[ ]s,

Gustavo

Mesa Redonda – Engine relacional do SQL Server

Boa Noite Pessoal,

 

Há uma semana atrás eu apresentei mais um Webcast sobre SQL Server juntamente o Luti. Esse Webcast foi diferente dos usuais já que foi em um novo formato. Não era sobre nenhum tema específico.

 

Inicialmente fui entrevistado sobre as tarefas do meu dia a dia, o papel do DBA, estudos, etc. A segunda parte do Webcast foi direcionada para dúvidas sobre o Engine relacional do SQL Server incluindo SQL Server Express, Índices, Fragmentação, Snapshot Isolation, etc.

 

Foi uma sessão bem interessante e as dúvidas bem pertinentes. Os interessados em conhecer mais sobre esse Webcast podem pesquisar no link abaixo:

 

https://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032386231&EventCategory=4&culture=pt-BR&CountryCode=BR

 

[ ]s,

 

Gustavo

Dicas e Truques sobre consultas complexas no SQL Server

Boa Tarde Pessoal,

 

Ontem fiz a apresentação do Webcast "Dicas e Truques sobre consultas complexas no SQL Server" onde demonstrei como utilizar o TSQL para elaborar consultas avançadas com foco no SQL Server 2005.

 

O objetivo do Webcast era demonstrar algumas necessidades de consultas comuns que aparecem, bem como apontar soluções para essas dúvidas e listar algumas práticas recomendadas.

 

Foram apresentados 5 problemas bem freqüentes. Posto também os scripts de forma resumida.

 

Totalização de resultados
Como elaborar uma coluna de saldo acumulando valores

 

— Tabela de Lançamentos para exemplificar o Subtotal
CREATE TABLE tblLancamentos (
    DataLancamento SMALLDATETIME,
    ValorLancamento SMALLMONEY)

 

— Insere os registros
INSERT INTO tblLancamentos VALUES (‘20080623’,100)
INSERT INTO tblLancamentos VALUES (‘20080624’,-250)
INSERT INTO tblLancamentos VALUES (‘20080625’,380)
INSERT INTO tblLancamentos VALUES (‘20080626’,200)
INSERT INTO tblLancamentos VALUES (‘20080627’,-300)

 

— Listar o saldo automático

SELECT DataLancamento, ValorLancamento,
    (SELECT SUM(ValorLancamento) FROM tblLancamentos
    WHERE DataLancamento <= QE.DataLancamento) AS Saldo
FROM tblLancamentos AS QE

 

Concatenação e delimitação
Como juntar vários resultados em uma única linha com algum caractér delimitador

 

— Cria a tabela de Clientes para exemplificar a concatenação
CREATE TABLE tblClientes (
    CodigoCli INT,
    NomeCli VARCHAR(50))

 

— Insere os registros
INSERT INTO tblClientes VALUES (1,‘Amanda’)
INSERT INTO tblClientes VALUES (2,‘Ângela’)

 

— Cria a tabela de Pedidos para exemplificar a concatenação
CREATE TABLE tblPedidos (
    CodigoCli INT,
    NumPedido INT,
    DataPedido SMALLDATETIME)

 

— Insere os registros
INSERT INTO tblPedidos VALUES (1,3310,‘20080620’)
INSERT INTO tblPedidos VALUES (1,5340,‘20080624’)
INSERT INTO tblPedidos VALUES (1,6723,‘20080628’)
INSERT INTO tblPedidos VALUES (2,4456,‘20080621’)
INSERT INTO tblPedidos VALUES (2,5219,‘20080625’)
INSERT INTO tblPedidos VALUES (2,5780,‘20080630’)

 

— Utilizar a natureza semiestruturada para concatenar valores
SELECT NomeCli, REPLACE(REPLACE(PedidosXML,‘</row>’,‘ ; ‘),‘<row>’,”) AS Pedidos FROM (
    SELECT NomeCli,
        (SELECT NumPedido AS [text()] FROM tblPedidos AS PED
        WHERE CLI.CodigoCli = PED.CodigoCli FOR XML PATH) AS PedidosXML
    FROM tblClientes AS CLI) AS Res

 

O TOP “N”
Como retornar a N ocorrências relacionadas a um conjunto de registros

 

— Cria a tabela de Clientes para exemplificar o TOP 2 para cada cliente
CREATE TABLE tblClientes (
    CodigoCli INT,
    NomeCli VARCHAR(50))

 

— Insere os registros
INSERT INTO tblClientes VALUES (1,‘Amanda’)
INSERT INTO tblClientes VALUES (2,‘Ângela’)

 

— Cria a tabela de Pedidos para exemplificar o TOP 2 para cada cliente
CREATE TABLE tblPedidos (
    CodigoCli INT,
    NumPedido INT,
    DataPedido SMALLDATETIME)

 

— Insere os registros
INSERT INTO tblPedidos VALUES (1,3310,‘20080620’)
INSERT INTO tblPedidos VALUES (1,5340,‘20080624’)
INSERT INTO tblPedidos VALUES (1,6723,‘20080628’)
INSERT INTO tblPedidos VALUES (2,4456,‘20080621’)
INSERT INTO tblPedidos VALUES (2,5219,‘20080625’)
INSERT INTO tblPedidos VALUES (2,5780,‘20080630’)

 

— O operador CROSS APPLY é indicado para situações TOP "N"
SELECT CLI.NomeCli, PED.NumPedido, PED.DataPedido
FROM tblClientes AS CLI
CROSS APPLY (
    SELECT TOP 2 NumPedido, DataPedido FROM tblPedidos
    WHERE CodigoCli = CLI.CodigoCLI
    ORDER BY DataPedido) AS PED

 

PIVOT & PIVOT dinâmico
Como transformar linhas em colunas usando o Operador PIVOT e superar suas limitações

 

— Cria a tabela de Marcas para exemplificar o PIVOT
CREATE TABLE Marcas (
    CodigoCli INT,
    Marca CHAR(1))

 

— Insere os registros
INSERT INTO Marcas VALUES (1, ‘A’)
INSERT INTO Marcas VALUES (1, ‘B’)
INSERT INTO Marcas VALUES (2, ‘A’)
INSERT INTO Marcas VALUES (3, ‘B’)

 

— Criando e formatando o resultado

SELECT Cliente,
    CASE A WHEN 1 THEN ‘S’ ELSE ‘N’ END AS MarcaA,
    CASE B WHEN 1 THEN ‘S’ ELSE ‘N’ END AS MarcaB
FROM (
SELECT CodigoCli AS Cliente, [A], [B] FROM
(SELECT CodigoCli, Marca FROM Marcas) AS Marcas
PIVOT
( COUNT(Marca) FOR Marca IN ([A],[B]) ) AS PivotTable) AS Q

 

— Criando e formatando o resultado sem especificar as marcas

DECLARE
    @Marca CHAR(1),  @Marcas VARCHAR(100), @cmdSQLHeader VARCHAR(100),
    @cmdSQLAux VARCHAR(100), @cmdSQLFooter VARCHAR(1000),
    @cmdSQL VARCHAR(1500)

 

    SET @cmdSQLHeader = ‘SELECT Cliente’ + CHAR(10)
    SET @cmdSQLAux = ‘, CASE ? WHEN 1 THEN ”S” ELSE ”N” END AS Marca?’ + CHAR(10)
    SET @cmdSQLFooter = ‘
        FROM (
        SELECT CodigoCli AS Cliente, ?
        FROM
        (SELECT CodigoCli, Marca 
            FROM Marcas) AS MarcasFormatadas
        PIVOT
        ( COUNT(Marca) FOR Marca IN (?) ) AS P) AS Q’

— Recuperar a lista de marcas existentes
DECLARE cMarcas CURSOR
FOR SELECT DISTINCT Marca FROM Marcas
FAST_FORWARD

 

OPEN cMarcas

 

FETCH NEXT FROM cMarcas INTO @Marca

SET @Marcas = ”

SET @cmdSQL = @cmdSQLHeader

 

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmdSQL = @cmdSQL + REPLACE(@cmdSQLAux,‘?’,@marca)
    SET @Marcas = @Marcas + ‘[‘ + @Marca + ‘],’
    FETCH NEXT FROM cMarcas INTO @Marca
END

 

CLOSE cMarcas

DEALLOCATE cMarcas

 

— Executando o comando

SET @Marcas = LEFT(@Marcas,LEN(@Marcas)-1)
SET @cmdSQL = @cmdSQL + REPLACE(@cmdSQLFooter,‘?’,@Marcas)

EXEC(@cmdSQL)

 

Consultas Hierárquicas
Como elaborar consultas em relacionamentos hierárquicos (autorelacionamento)

 

— Cria a tabela de Funcionários para exemplificar a hierarquia
CREATE TABLE tblFuncionarios (
    IDFuncionario INT,
    NomeFuncionario VARCHAR(50),
    IDSuperior INT NULL,
    ValorVendido SMALLMONEY)

 

— Insere os registros
INSERT INTO tblFuncionarios VALUES (1,‘Thelma’,NULL,350.00)
INSERT INTO tblFuncionarios VALUES (2,‘Denise’,1,250.00)
INSERT INTO tblFuncionarios VALUES (3,‘Tamara’,2,200.00)
INSERT INTO tblFuncionarios VALUES (4,‘Herline’,3,100.00)
INSERT INTO tblFuncionarios VALUES (5,‘Ricardo’,NULL,600.00)
INSERT INTO tblFuncionarios VALUES (6,‘Cléber’,5,500.00)
INSERT INTO tblFuncionarios VALUES (7,‘Eduardo’,5,300.00)

 

— Utilização de uma CTE Recursiva
WITH Superiores
AS (
SELECT IDFuncionario, NomeFuncionario,
    IDSuperior, ValorVendido,
    CASE WHEN IDSuperior IS NULL THEN IDFuncionario
    ELSE IDSuperior END AS ID
FROM tblFuncionarios
WHERE IDSuperior IS NULL

 

UNION ALL

 

SELECT FUNC.IDFuncionario, FUNC.NomeFuncionario,
  FUNC.IDSuperior, FUNC.ValorVendido,
  SUP.ID
FROM Superiores AS SUP
INNER JOIN tblFuncionarios AS FUNC ON SUP.IDFuncionario = FUNC.IDSuperior)

 

SELECT FUNC.IDFuncionario, FUNC.NomeFuncionario, FUNC.ValorVendido,
    SUM(SUP.ValorVendido) – FUNC.ValorVendido AS VendasIndiretas,
    SUM(SUP.ValorVendido) AS TotalVendas
FROM
    tblFuncionarios AS FUNC
    INNER JOIN Superiores AS SUP ON FUNC.IDFuncionario = SUP.ID
GROUP BY
    FUNC.IDFuncionario, FUNC.NomeFuncionario, FUNC.ValorVendido

 

Assim que a data e o link do Webcast foram definidos, fiz uma divulgação nos principais grupos de SQL Server que faço parte. Fiquei muito satisfeito com a quantidade de pessoas que assistiram a apresentação (cerca de 80 pessoas). Foi muito gratificante poder levar conhecimento a um grupo tão grande já que normalmente uma apresentação no Webcast tem um quorum menor de pessoas.

 

A essas pessoas que assistiram a apresentação eu gostaria de agradecer novamente pelo comparecimento, interesse e pela avaliação. Mesmo com os problemas de áudio a avaliação foi positiva e recebi muitos feedbacks. Estou sugerindo para a equipe organizadora que façamos uma segunda apresentação. Se de fato for acontecer, divulgarei a data e o link logo que disponíveis.

 

Conforme prometido no Webcast, segue o link para efetuar o download do projeto em SQL Server 2005 com os scripts utilizados na apresentação.

 

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/20080807%7C_TSQLTricks01.zip

 

O Webcast foi gravado e acredito que nos próximos dias deva estar disponível. O link original do Webcast é:

 

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=pt-BR&EventID=1032383203&CountryCode=BR

 

Obrigado a todos,

 

Gustavo