Script – Coleta de Waits por Snapshot.

Olá,

Postando aqui um script de uso pessoal, segue um exemplo de coleta de waitstats baseada em snapshot: ideia antiga porém efetiva: realiza a coleta das esperas de determinado servidor em momento A e momento B e retorna a diferença.

A ideia é que, pelo fato da DMV ser cumulativa, uma consulta direta não responde muita coisa além de dar uma ideia geral (e bem abstrata) de quais waits possuem maior número de ocorrências. O problema é que estes dados isolados são difíceis de interpretar.

Já com snapshots, é possível verificar a contagem das esperas por intervalo de tempo de modo e isso auxilia demais análises de troubleshooting (muito mais efetivo se isso é feito no início de qualquer análise), sendo a diferença um número mais factível tendo algum contexto temporal.

Esperas são eventos normais e a maioria delas podem ser ignoradas em análises de troubleshooting e elas estão retratadas nos filtros via NOT IN.

O script abaixo pede apenas um parâmetro em segundos e processa as coletas.

[]’s

Referências
MSDN – sys.dm_os_wait_stats
Script Paul Randal: Script Paul Randal:

Copiar resultado do SSMS no Excel sem quebra

Já copiou o resultado de determinada consulta no SSMS (SQL Server Management Studio), colou em uma planilha do Excel e se surpreendeu com a bagunça?
O que era pra ser uma solução rápida e prática acabou virando uma zona?

Eis o cenário:

Consulta exemplo

Gerando um resultset pra teste

Pense na cópia mal sucedida

Copiando o resultset no Management Studio e copiando no Excel. Olha a bagunça!

O intuito deste post é explicar o que causa o problema e como resolver.

Problema

O problema acontece quando o resultado copiado possui caracteres como quebra de linha, tab, etc. Estes caracteres são interpretados no Excel, se copiados diretamente, como comandos e acabam por desconfigurar no momento de colar.
Vamos simular o problema:

1) Criando uma massa pra testes. Existe um propósito em manter os textos no INSERT com espaço. Deixe-os desta forma caso use o código abaixo.

2) Consultando a tabela recém-criada, gerando um resultset com caracteres de controle:

Gerando um resultset problemático de novo

Mesmo caso do exemplo que abriu o post.

3)  Copie o resultset (de preferência com o cabeçalho, botão direito no resultado da consulta, opção “Copy with Headers“, e cole no Excel:

Formatação quebrada

Olha a bagunça!

 Solução

Alguns usuários do SQL Server já abriram chamado na Microsoft para relatar o problema, que aparentemente não possui soluções definitivas, mas existe um workaround (aqui no Brasil usa-se o termo solução de contorno, alguns dizem gambiarra, etc).

Sabendo que o problema acontece quando o resultset possui caracteres como quebra de linha, tab, dentre outros (conhecidos como caracteres de controle), que são interpretados como comandos no Excel, podemos converter esses caracteres em espaço, podendo assim sacrificar a formatação original do campo em prol da realização da tarefa.

Na tabela ASCII, vamos identificar quais caracteres devem ser primariamente localizados:

Codes

Abaixo, segue dentro dos parênteses os códigos ASCII do que pretendemos remover:

  • CHAR(9) = Tab
  • CHAR(10) = Line Feed (LF)
  • CHAR(13) = Carriage Return (CR)

Tab é tab é dispensa explicações. Já Carriage Return e Line Feed são dois caracteres de controle que com frequência caminham de mãos dadas quando uma quebra de linha acontece. Em outras palavras, um ENTER, por exemplo, faz a quebra utilizando estes dois caracteres.

Pra facilitar o entendimento:

NaPratica

Podemos usar os códigos destes caracteres de controle dentro da função REPLACE e substituí-los por espaço, de modo que nossa cópia não fique desconfigurada. Note que você pode fazer o mesmo sem passar espaço, pode passar entre aspas vazias também.

Agora, vamos colar o resultado sem quebrar o resultset:

Gerando agora um resultset com o workaround

Gerando agora um resultset com o workaround

Copiando e colando no excel:

Agora foi!

Cópia bem sucedida.

O workaround, assim como mais informações a respeito do problema, foram postados nos dois links abaixo no connect:

https://connect.microsoft.com/SQLServer/feedback/details/788463/copy-and-paste-from-sql-2012-to-excel-breaks-rows-if-an-address-is-included

Conclusão

A solução apresentada permite a cópia sem maiores problemas sacrificando a formatação original de textos que possuem caracteres de controle. Caso seja realmente necessário manter a formatação, é possível realizar a importação de dados para o Excel de forma direta utilizando outros métodos, que não estão descritos neste post.

[]’s

Referências

  1. CHAR Function – http://msdn.microsoft.com/pt-br/library/ms187323.aspx
  2. REPLACE Function  – http://msdn.microsoft.com/pt-br/library/ms186862.aspx
  3. Carriage Return (CR)- http://pt.wikipedia.org/wiki/Carriage_return
  4. Line Feed (LF) – http://en.wikipedia.org/wiki/Newline
  5. Tabela ASCII – http://www.theasciicode.com.ar/ascii-control-characters/horizontal-tab-ascii-code-9.html

 

Script – Informações de espaço em disco

Espaço em Disco

Olá,

O código a seguir tem a finalidade de capturar o espaço atual em disco. É uma mão na roda principalmente quando se usa o Central Management Servers pra disparar a mesma consulta para várias bases diferentes (possibilitando assim uma verificação rotineira mais rápida e prática).

Código

Saída:

Resultado

<Clique na imagem>

Área de Scripts

Nova seção de scripts!

Bom dia,

A partir de hoje, vou postar alguns scripts de uso pessoal por dois motivos:

1) Talvez ajude alguém;

2) Abandonar a preguiça de usar o blog;

A categoria SCRIPTS na parte superior do blog  também será atualizada conforme as postagens!

 

Menor privilégio possível. Mas nem sempre.

Deixar o usuário ver apenas o que lhe interessa e nada mais

Boa noite =)
Hoje um amigo tinha uma dúvida, que também já tive, sobre visualizações de bases desnecessárias. Achei um bom assunto pra postar, pois gosto da temática segurança e apesar de parecer óbvio, nem sempre o SQL Server oferece um dos seus princípios básicos, o princípio do menor privilégio. Vamos conversar sobre isso?

Bem, nada melhor do que um case pra passar a ideia…

1 – A demanda

Você é o DBA de uma única instância SQL Server chamada NINJACORP.
Existem várias bases confidenciais em tal instância, e você é um DBA vigilante por default, pra combinar com o ambiente. Preza muito pela segurança. E essa é sua filosofia de vida.

Segue a lei da segurança máxima e menor privilégio como se fosse uma rígida religião.

Em um belo dia, com sol brilhando, umidade do ar agradável e pássaros cantando no céu, chega uma demanda pra inclusão de login de um desenvolvedor em produção. Seu coração pulsa de forma desequilibrada quando lê que, por A mais B, será necessário criar um sql login.

O chamado em resumo:

“Solicito a inclusão de login sql chamado servidorPublico, senha p@ssw0rd134#@3_2 e que tenha acesso de leitura ao banco DbInformacoesSagradas”

O DBA, relutante, faz a criação do sql login (e queria mesmo usar o Windows Authentication, mas não teve jeito) e do usuário sem maiores problemas.

–> Cria login do sujeito

CREATE LOGIN servidorPublico WITH PASSWORD = ‘p@ssw0rd134#@3_2’ , CHECK_POLICY = OFF

–> Cria usuário para a base

USE [DbInformacoesSagradas]
GO
CREATE USER usrServidorPublico FOR LOGIN [servidorPublico] WITH DEFAULT_SCHEMA = dbo

–> Adiciona o usuário para permissão de leitura (Role datareader)

EXEC sp_addrolemember ‘db_datareader’,’usrServidorPublico’

Passada a dor… Tá na hora do teste..

2 – A descoberta

Até que você, DBA terminou a tarefa e concluiu a demanda. Mas… curioso,pela primeira vez na vida, pois foi um DBA que sempre trabalhou com Windows Authentication (eu sei, muito difícil, mas vamos imaginar!), resolveu logar com o login recém criado, o ServidorPublico.

isNotAccessible

Olha só! Apesar do usuário ter apenas uma base como leitura, ele consegue visualizar os demais bancos. Vale ressaltar: ele consegue visualizar os bancos mas não possui acesso aos mesmos, sendo que uma expansão de qualquer banco resulta em erro (vide imagem acima).

Você precisa garantir de qualquer modo que o login consiga visualizar apenas o banco de dados que possui algum vínculo. Você quer manter o nome das bases em segredo. Quer seguir sua segurança instintiva. Mas como fazer? Bem, pausa pro café e vamos falar agora sobre a ponta do iceberg.

3 – Mínimo privilégio mas nem sempre

O que é mínimo privilégio possível? Bem, em suma, é oferecer o mínimo de permissão possível para que determinada conta/usuário consiga realizar, sem maiores problemas, sua atividade fim.

Você, pra matar uma barata, precisa de um chinelo ou de um fuzil?

Isso deveria ser sagrado em nosso trabalho, e não é só na área de Administração de Banco de Dados, mas em toda a nossa vida, em suas devidas proporções e sem radicalismos.  Tenho uma vontade enorme de falar sobre esse princípio e do “Security by default”, mas vamos nos limitar à definição dada acima pra não esfriar o pão de queijo (no caso, o código).

O SQL Server no geral segue tal princípio na parte de segurança, mas, curiosamente, no caso de visualizar bases de dados desnecessariamente, parece não ser uma preocupação tão crítica, observado que  isso já foi motivo de reclamação (antiga, inclusive) no Microsoft Connect e os caras não ligaram tanto assim.

Seguindo este mesmo princípio ainda, e lembrando do exemplo da barata:

Se o usuário deve ter disponível apenas o que é necessário, qual seria a razão de enxergar qualquer base que não tenha relação com ele?

Parando pra pensar, nenhuma.

Aqui existe uma óbvia contradição no princípio de mínimo privilégio. Pra quê visualizar bases que não são necessárias? Pode parecer bobo, mas isso abre brechas diversas que enfraquecem a segurança. Uma delas, é que, pra um desenvolvedor, realmente interessa saber que naquela instância existe um banco do RH? Bem, não vou entrar no campo ético da coisa, mas já imagina-se as possíveis situações em que algo aparentemente bobo pode se tornar… Basta um pouco de engenharia social, criatividade e um pouco de atitude…

Existe uma forma  de contornar essa situação de ocultar a visualização das bases (que deveria ser default, na minha opinião, segundo motivos já explicados do menor privilégio). Não resolve 100%, mas quase. Infelizmente, a solução a seguir oculta todas as bases, inclusive as que se tem algum vínculo.

4 – A forma mais aproximada de se resolver o problema

Existe uma permissão à nível de servidor chamada VIEW ANY DATABASE, que possibilita o usuário ver metadados relacionados aos bancos de dados, o que inclui suas visualizações na:

a) sys.databases

b) sys.sysdatabases

c) sp_helpdb

E afeta também o modo visual do Management Studio (ssms), o que é extremamente importante.

Em suma: mesmo o usuário servidorPublico tendo acesso a um só banco, ele consegue, utilizando esses três itens mencionados acima, enxergar alguns metadados de outros bancos.

O legal dessa história é que essa permissão VIEW ANY DATABASE é automaticamente concedida ao Public, ou seja, todo usuário ou conta que se conecta no SQL Server automaticamente ganha as permissões adquiridas desta role. Tecnicamente, isso equivale a dizer que, a menos que você negue tal permissão pro public, todas as contas/usuários do banco podem enxergar metadados que não são de seu interesse (acredita-se) por padrão.

Mas como contornar o problema?

Sabendo que todo login (inclusive o que acabamos de criar) pertence a uma role public (que é default), temos duas possibilidades:

1) Alterar o login desejado;

USE MASTER
GO
DENY VIEW ANY DATABASE TO servidorPublico

É uma solução interessante. O usuário não enxerga banco nenhum no modo visual mas consegue acessar a base (que possui permissão) normalmente através de T-SQL. Aqui cabe uma observação minha… Seria extremamente desejável que ele pudesse enxergar as bases na qual possui vínculo. Ou seja, seria interessante aqui termos uma permissão a mais (no final do artigo eu chego nesse ponto).

2) Alterando a role public

USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC

É uma solução “broadcast”, hehe . Lembre-se que TODOS os usuário criados são atribuidos ao public, e qualquer efeito sobre o mesmo pode afetar permissões de todo mundo que está interligado. Lembrando que poucas roles escapam da  restrição deste método, como por exemplo, os sysadmins.

TRIVIA: de todas as server roles, PUBLIC é a única que pode sofrer operações de DCL. Tente dar DENY em outras server roles, como por exemplo, no securityadmin.

5 – O fim

Você, DBA grilado, implementa a primeira solução logado em sua conta de sysadmin, volta pro login criado e percebe que agora, apesar do login não conseguir nem ver a base descrita no client do ssms, consegue ler (intenção desejada) os dados e só consegue enxergar na tempdb e na master informações relacionadas às suas permissões. Independente do método utilizado acima, o resultado é:

(Clique na imagem para ampliar)

p2

Pra testar a sp_helpdb, você  resolve testar com nome de outro banco qualquer:

SP_HELPDB ‘DbFinanceiro’

E recebe o seguinte erro (Totalmente justificável):

Msg 15010, Level 16, State 1, Procedure sp_helpdb, Line 45

The database ‘DbFinanceiro’ does not exist. Supply a valid database name. To see available databases, use sys.databases.

Antigamente sem o DENY na ALTER VIEW ANY DATABASES, o usuário conseguiria enxergar alguns metadados relacionados aos bancos sem maiores dificuldades. Agora não mais.

E lá vai você, o DBA ninja, tomar um café pra encerrar essa empreitada aqui narrada.

6 – Considerações finais

A parte ruim dessa história toda é que não existe essa permissão de visualizar por banco. Seria legal que pudéssemos setar um ‘VIEW DATABASE’ (Se existissse) pra apenas as bases que gostaria, porém, isso não seria possível. Ou seja, seria desejável que o SQL Server permitisse a visualização de bancos que fossem interessantes ao login/user em específico. Imagino esse recurso voltado para os desenvolvedores.

Espero que tenha passado informações úteis.

Se você tiver alguma crítica, sugestão, correção, adendo, proposta de emprego ou comentário, fique à vontade também. Esse blog é nosso 😉

Conversa sobre a cláusula GO

Cláusula GO, funcionamento básico e seus conceitos
Vamos falar um pouco sobre o comando GO?
Duas pequenas observações antes de começar o artigo:

1) Nada nessa vida, nem uma cláusula de programação, é por acaso;

2) GO não é um comando T-SQL, muito menos SQL padronizado…É um comando usado pelo client para separação de batch (lote).

Introdução

O GO não é um comando T-SQL e também não é reconhecido como padrão na linguagem SQL. É um comando usado pelos aplicativos ‘clients’ que se conectam à instâncias do  SQL Server, como, por exemplo, o  sqlcmd e o Management Studio, que entendem o GO como um separador de instruções, e por isso enviam o código  de modo organizado, por sequência, usando GO como um separador. Se você usar o GO em qualquer client que não tenha suporte à cláusula, o seu código dará erro.

Pra ilustrar o que é batch e como o GO age no SQL Server, vamos a um exemplo básico:

Cláusula Go 1

O segundo SELECT dará erro por motivos óbvios. Alguns pensam “mas esse erro não tem lógica! Eu já declarei a variável na janelinha do Management Studio e a janelinha representa uma sessão aberta (até aqui correto), então, a variável, já que foi declarada na sessão, deveria ser reconhecida em qualquer parte da sessão (e aqui mora o erro). Engano clássico.

É justamente este pensamento  que possibilita a ocorrência de uma quantidade assustadora de erros em programação T-SQL, simplesmente por ignorar que existem escopos e escopos.

Ilustrando da melhor forma possível como funciona a separação de lotes a mesma consulta acima:

go2

Perceba que o GO delimita um lote de código de acordo com sua declaração, considerando qualquer código até que haja outro GO, que é a delimitação deste comando.O Management Studio, nesse caso, diz pra cada lote até onde ele deve executar. Em declarações de variáveis com o uso de GO, o Management Studio limita o escopo (alcance) da variável @data.

Outro detalhe, o comando GO precisa estar sozinho na linha, caso contrário, dará erro. Se quiser pode testar deixando um GO mesmo antecedido com ponto e vírgula (;) e notará que dará erro. Só comprova que ele é um comando com característica procedural, daqueles da moda antiga, que gostam realmente de separar instruções por lote de código (lote =  batch):

E pra que eu uso o GO afinal sendo que ele pode me levar à erros?

O comando tem uma propriedade de contador (inteiro). Isso pode ser usado em nosso favor quando usamos um client com suporte à cláusula. Segue um exemplo fantasioso que preenche uma tabela com registros totalmente aleatórios. Agora, vamos trabalhar com o modo de separação que é nativo do GO além do atributo de repetição.

go3

Basicamente, o GO vai executar 1000 vezes as instruções que foram declaradas desde o último GO.  Agora, uma pergunta. Se você der um F5, CTRL+E, PLAY, o que seja, pra executar o código, o que acontece?

a)      Um erro;

b)      O SQL Server cria a tabela #serial e insere mil vezes nessa tabela através do comando INSERT INTO;

c)       Vai  executar MIL VEZES o comando SELECT * from #serial;

d)      Vai dar USE no banco MASTER mil vezes;

Imagine mentalmente o GO dizendo assim: “Quero executar TUDO que tá emcima de mim. Tudo é meu, ATÉ QUE EU ENCONTRE OUTRO GO, aí o lote de código não é meu. Irmão não invade território de irmão!”.

Aliás, de curiosidade,  saiba  que o comando GO possui implícitamente a forma GO 1, pois ele só executa aquela instrução de código uma vez. Resumindo o que acontece na questão…

– O primeiro GO vai executar uma vez  o comando USE máster

–  O segundo GO vai executar o comando CREATE TABLE #serial e o INSERT mil vezes e…

Isso mesmo. A opção correta era a A. Ele vai criar a tabela, inserir uma vez e vai voltar de novo no loop, só que na segunda vez, ele vai voltar pro primeiro comando do lote que é um CREATE TABLE, porém como a tabela já existe, vai dar erro.

Msg 2714, Level 16, State 6, Line 3

There is already an object named ‘#serial’ in the database.

** An error was encountered during execution of batch. Continuing.

Batch deu erro e foi abortado. Simples assim. Então, imagine que você não tem essa tabela #serial. Drope-a:

DROP TABLE #serial

Repare a diferença testando o código abaixo (Não liga para os rabiscos paint-fast-like):

go4

O código acima irá funcionar. Vai executar apenas o INSERT (e não o CREATE) mil vezes. Simples assim.

FAQ (Recapitulando)

É certo dizer que GO é um comando?

Reposta: É sim.

Mas você disse que ele não é um comando..

Resposta: De novo, é um comando sim, relaxa. Do client, e não do SQL  ANSI ou T-SQL. Tanto que se você tentar executar uma procedure com GO, dará erro, porque o SQL Server é um Server e não sabe o que significa a cláusula GO.

Posso executar código por código então, sem usar o GO, uma porção de cada vez, no mouse, sabe, na mão?

Claro que pode. E isso, inclusive, é execução em “batch” também (teoricamente). A diferença é que você está fazendo sua própria separação de lotes, mas repare que o conceito é o mesmo.

Para maiores dúvidas:

GO: Documentação do próprio MSDN 

Batch: Documentação do próprio MSDN

Qualquer dúvida, feedback, sugestão, sinta-se à vontade para comentar.

Impressões sobre o Exame 70-461: SQL Server 2012 – Querying

query

Boa noite 🙂

Quarta passada tive êxito no exame 70-461: Querying, o primeiro capítulo da trilogia MCSA.

Se você não conhece o funcionamento sobre a nova trilha certificação, verifique este link, baixe os webcasts e veja como funciona.

Dei uma folheada Training Kit 70-461 e usei como base séria o Skills Measure . Pra não sair no escuro, dei uma lida também, durante um bom tempo, em impressões de outros profissionais que fizeram essa prova. Você pode dar uma pesquisada na internet que com certeza acha. Só pra saber onde eu direcionava mais atenção. E meu post tem esse intuito, de quem sabe, fazer essas dicas úteis pra quem vai prestar o exame.

A forma de prática foi o Prep-Kit, que vem junto com o Training Kit.
Recomendo o uso do Prep-kit apenas para reforçar conceitos e revisar comandos e não para pegar o ritmo da prova, que é bem diferente, sendo supérfluo e sem complexidade alguma! Não tem como comparar o nível de dificuldade do Prep-kit com a prova, btw.

O que mais me prejudicou na prova, e sim, prepare pra rir: marquei, sem querer, a prova em Português. Não cometa esse erro jamais na sua vida. As traduções em português não estão “ruins”, só não estão compatíveis com o bom senso. Por exemplo, é fácil reconhecer VIEWS em banco de dados, mas não “Modos de Exibição”. Teve vários termos, que eu nem consigo mais lembrar de tão exóticos que são. Alguns traduzidos de forma extremamente literal. Até demais. “Larga de frescura rapaz!” – disse alguém. Mas quero ver esse alguém sacar NA HORA o que significa “Função definida pelo usuário com valor de tabela”. Rola um WAITFOR na cabeça, pelo menos na minha. A massa cinza até processa a tradução mas não na hora 🙂

Enfim, compartilho algumas dicas importantes pra quem for prestar o exame:

– Aprenda e PRATIQUE todos os tipos de função, principalmente as TVF (Table Value Functions);

– FOR XML PATH, AUTO e RAW. Vai cair, saiba o que cada cláusula traz e qual é a estrutura do XML. Saiba olhar para determinado XML e imaginar qual FOR XML gerou;

– Modos de Isolamento. Saiba qual causa mais bloqueios, o padrão do SQL Server, o que “versiona” os registros e o que evita leitura suja…

– Views indexadas. Saiba usar SCHEMABINDING. Aliás, views estão bastante em foco neste exame, inclusive para operações DML. De foco nessa parte!

– Relembre: INTERSECT, UNION, UNION ALL, DISTINCT,  ALL, SOME, ANY, RANK FUNCTION…

– CONSTRAINTS em geral. Computed Columns, inclusive com funções;

– Questões DRAG and DROP parecem fáceis. Mas o ‘cão’ mora nos detalhes…Preste bastante atenção no enunciado da questão. Por exemplo, se na questão disse que o usuário USRXPTO já possui acesso de leitura na tabela XPTO, e você deve ALTERAR a tabela mantendo as permissões, você não vai dar um DROP, CREATE e GRANT ou seja lá o que for desnecessários, certo? Se você for alterar, nesse caso, também não precisa do GRANT. Saiba também que criar um campo NOT NULL em tabela já populada não é nenhum almoço grátis. Saiba como resolver esse tipo de pedido.Enfim, parece besteira, mas quem não pratica isso ou nunca teve experiência, pode perder muitos pontos com questões do gênero.

– Essa prova tem um foco exagerado em views (já falei, mas reforço). Pratique inserir/atualizar/deletar alguns registros nela (sim, é possível, via trigger instead of). Inclusive em views que possuem SELECT em mais de uma tabela (dica de ouro);

– Planos de Execução: Aprenda as formas de visualizá-lo;

– Tabelas inserted e deleted + triggers são essenciais. Aliás triggers foram assuntos bem presentes, não tanto quanto VIEWS, mas quase;

– Cairam algumas cláusulas novas: LEAD, LAG , EOMONTH, FORMAT, TRY_CONVERT, PARSE, dentre outros. Inclusive, caiu uma questão de EOMONTH combinada com DATEADD que me confundiu bastante, principalmente por não ter oportunidade de validar o código ):

– Saiba o que faz um PIVOT, UNPIVOT e APPLY (Outer e Cross). Não precisa focar seu estudo nisso (estou pensando em economia de tempo), apenas saiba em que situação aplicar;

– Diferencie newid e newsequentialid, saiba o que cada um faz e TESTE!

– Pratique abrir transações com tratamento de erros. Nada complexo pra quem já trabalha com T-SQL: TRY, CATCH, @@ERRORCOUNT e outras variáveis comuns à transações podem cair. Conhecer o funcionamento do XACT_ABORT e conhecer Triggers que fazem o uso deste tipo de programação é fundamental!

Creio que são informações úteis para quem está estudando. Claro que a prova vai muito além, e esses tópicos acima estiveram presentes no exame que fiz, então, reforço, não deixe de estudar pelo Skills Measure e não se limite ao que foi dito acima.

Uma coisa que me chamou bastante a atenção é que conhecer T-SQL, mas não praticar pode ser um perigo potencial no exame. Existem alguns peguinhas que só quem codifica no dia a dia e executa vai identificar. Parece uma frase clichê, e não deixa de ser, mas é uma verdade crua nessa prova.

E  Socorro, se tiver lendo isto, obrigado pelas dicas e conhecimentos trocados, especialmente as de XML o/

E é isso pessoal.

Estou estudando agora para a 70-462 agora e já tô preocupado com a 70-463 (BI).

[]’s

Resposta do Desafio T-SQL #2 – MERGE e OUTPUT

BIRD

Boa noite pessoal 🙂

1) Segue resposta do desafio anterior.

Eu coloquei o script com BEGIN TRAN, pra que possamos ver o resultado sem commit no banco, pra que testemos de novo o script sem necessidade de rodar tudo de novo.

Vamos primeiro traduzir alguns termos no contexto de um comando MERGE:

TARGET:  A tabela alvo, que você tem por objetivo atualizar. Eu prefiro traduzir TARGET como destino pra assimilar com mais facilidade;

SOURCE:  A tabela fonte de onde vem a informação necessária para se atualizar a tabela alvo. Eu prefiro traduzir SOURCE neste caso como origem pra assimilar com mais facilidade;

Sobre o algoritmo: Quero usar MERGE na tabela Empregado, que é o destino da atualização. Quero pra isso utilizar a tabela ListaMudancasRH, que é a fonte que irei utilizar para atualizar minha tabela Empregado. Eu quero associar as duas utilizando o nome do funcionário. Tendo isso em mente, é fácil entender qual a utilidade do MATCH…

#1 – WHEN MATCHED AND SOURCE.ativo = 1 THEN UPDATE…

Significa: Quando o nome do funcionário coincidir nas duas tabelas (ou seja, fizer o MATCH sucessivamente) e na tabela origem com as novas informações, tiver o ativo =1, significa que o funcionário ainda está empregado. Logo, devo atualizar o cargo na tabela destino utilizando a origem como base

#2 WHEN MATCHED AND SOURCE.ativo = 0 THEN DELETE…

Significa : Quando o nome do funcionário coincidir nas duas tabelas (ou seja, fizer o MATCH sucessivamente)  e na tabela origem com as novas informações. tiver o ativo =0, significa que o funcionário está/será desempregado. Logo, devo DELETAR ele na tabela de destino utilizando a origem como base (É importante ressaltar que isso é um exemplo apenas. Em casos reais, dependendo da modelagem dos sistemas que apontam para o banco, registros de pessoas comumente não são deletados, e sim atualizados para algo que indique desuso, que nesse caso poderia ser simplesmente a flag ativo da destino (tabela Empregados) atualizada para 0.

#3 – WHEN NOT MATCHED BY TARGET THEN INSERT…

Significa: Quando o nome do funcionário não coincidir na TARGET, ou seja, na tabela destino, se faz necessária a inserção de um funcionário que não existia antes na tabela de destino. Veja só, aqui inserimos os dados da tabela origem na tabela destino, a de Empregados. —

2) A segunda questão pede que façamos uma tabela de auditoria. Lembram que recomendei o uso da cláusula output? Não foi por acaso. Bem, existem várias formas de se realizar auditoria de um registro, e uma delas é utilizando as famosas “tabelas especiais” deleted e inserted,  que são duas tabelas utilizadas pelo SQL Server na execução de tarefas DML (que manipulam dados).

Esse post não pretende cobrir assunto , mas você pode vê-lo aqui e recomendo que o faça caso precise aprender novas possibilidade pra debugging e auditoria… Mas enfim… Cada vez que o código faz um:

DELETE: Internamente, no SQL Server, é salva uma cópia do registro apagado na tabela DELETED.

INSERT: Internamente, no SQL Server é salva uma cópia do registro recém-inserido na tabela INSERTED

UPDATE: Internamente, no SQL Server, é salva o registro na tabela DELETED e insere o novo no INSERTED.

Em resumo, combinando com a cláusula OUTPUT (maiores informações aqui), que a grosso modo é um “SELECT pra registros que estão sendo manipulados em expressões DML”, é possível capturar os registros que o SQL Server joga nas tabelas INSERTED e DELETED e assim sendo, persistir estes dados com o uso de uma cláusula INSERT INTO!

Para praticar, e já considerando que a cláusula MERGE soa mais familiar pra você, leitor, mostro uma nova possibilidade: Realizar o MERGE e manipulando os registros modificados (apagados, atualizados e inseridos).

Informações adicionais… COALESCE compara dois nomes e traz o primeiro resultado não nulo que encontrar. Inevitavelmente o nome do funcionário se encontrará NULO se o registro sofreu operações de DELETE na inserted e encontrará NULO também na deleted se foi feita uma operação de inserção.

No caso de um UPDATE, os dois campos estarão preenchidos, pois uma atualização consiste, internamente para o SQL Server, de uma deleção + inserção. A variável $ACTION guarda internamente qual operação foi realizada naquele registro, e que aqui foi mantida em inglês por motivos de praticidade. Você pode tratar isso com um CASE caso queira registrar um outro nome diferente do retornado ao servidor.

Essa foi a resolução básica! Tem várias outras formas de se resolver o problema, sendo esta apenas uma delas.

Espero que tenha passado alguma informação útil. Caso tenha alguma crítica, correção, ou opinião, sinta-se à vontade para comentar!
[]’s

SQL Server 2012 – Logical Functions e mais economia de código

logic

Boa noite 🙂

Outro post básico e rápido sobre funções lógicas (do mesmo grupo das já conhecidas estruturas lógicas, que envolvem WHILE, IF, CASE)…

Duas agradáveis adições foram realizadas no SQL Server 2012. Duas funções lógicas que já são velhas conhecidas para quem já programa(ou): IIF e CHOOSE.

IIF

(Documentação Oficial na MSDN para maior apronfudamento)

Também conhecido como IF ternário, nada mais, no contexto de algumas linguagens de programação, uma forma mais organizada de se fazer realizar uma escolha condicional entre dois valores utilizando uma expressão.

Estrutura:

IIF (boolean_expression, true_value, false_value) ou em bom português IIF (expressão_booleana, valorSeVerdadeiro, valorSeFalso)

É justamente pela estrutura que vem o nome de IF Ternário, por possui três elementos (parâmetros) em sua condição.

Seguem três exemplos pra posteriores comentários.

Em suma, como IF não pode ser utilizado em comandos SELECT deliberadamente para realizar condições, sendo utilizado mais em Stored Procedures e Funções, IIF serve como sintaxe alternativa para a função CASE, que faz exatamente o que a instrução IIF faria em uma instrução SELECT.  O que acho bacana nesse comando:

  • Sintaxe simples, limpa e direta (embora de começo pareça complexa);
  • Segue padrões .net (ao trabalhar com Integration Services, por exemplo, é uma cláusula largamente utilizada para faze comparações) e de outras linguagens;
  •  Economia de código, facilitando legibilidade;

Tem só um pequeno detalhe… Não estou dizendo que é um substituto pra CASE, por um simples e humilde motivo, e lógico ainda por cima:

CASE, assim como IF (em seu devido lugar) permitem mais de duas expressões lógicas. IIF é X, se não for X, ah meu amigo, é Y. Sem choro nem vela.

Códigos para expressar o sentimento:

Logo, é necessário avaliar qual a melhor construção para a sua lógica.

CHOOSE

Documentação Inicial na MSDN

Resumindo, assim como o IIF, o CHOOSE é outra cláusula condicional que pode, dependendo do caso, substituir o CASE. É mais uma construção bastante utilizada em .net e inclusive no Integration Services (Olha a MS casando particularidades de produtos “distintos” da suite  do SQL Server aos poucos aí!)

Vamos falar antes de sua construção:

A função CHOOSE vai retornar exatamente o valor que corresponde ao index passado no primeiro parâmetro:

O protocolo é claro. Existem cinco parâmetros dentro do CHOOSE. O primeiro vale um (1) e o último cinco (5). O número (index) passado é Quatro (4) então ele vai buscar o quarto valor nos parâmetros. No segundo exemplo, como ele não acha o parâmetro cujo valor é sete (7) ele simplesmente retorna NULO. A mesma coisa vale para o ZERO.
Outra aplicação prática:

But, contudo, todavia, todos os parâmetros devem ser do mesmo tipo, pois caso um dos valores esteja fora do padrão, ocorrerá um erro na execução. Veja só:

(6 row(s) affected)
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value ‘DOMINGO’ to data type int.

Enfim, alguns pontos positivos:

  • As mesmas vantagens do IIF

Minha única observação sobre a função é que poderia ser MUITO, mas MUITO útil caso fosse possível utilizar intervalos de valores, como é possível com CASE (usando BETWEEN por exemplo) e outras possibilidades lógicas. Acho o comando um pouco limitado no geral assim como o IIF. Mas tem quem gostará, com certeza. Qual a melhor escolha? A resposta pra praticamente qualquer coisa no SQL Server é: It’s depends.

Espero ter sido claro e ter ajudado alguém. Se sim, Já valeu a postagem.

Obrigado pela visita and…

Keep Querying !

[]’s

Desafio #2 T-SQL – MERGE e OUTPUT

Merge

MERGE é uma cláusula sensacional que foi implementada no SQL Server 2008. Pouca gente usa, e eu imagino que seja pela complexidade de declaração se comparando com outros comandos DML (Como por exemplo, DELETE, UPDATE e INSERT).

Leia mais sobre o comando aqui

Devo lembrar que a prova 70-461 vai cobrar esse conhecimento e provavelmente em mais de uma questão (assim como aconteceu na 70-433) e é bastante interessante que o conhecimento da mesma seja algo natural e intuitivo, além de ser um ótimo modo de se pensar/programar DML. Qual a forma que encontrei falar sobre a cláusula? Bem, claro, com um desafio! Vamos ao Desafio T-SQL #2 – MERGE e OUTPUT

Case

A empresa Trupe123 resolve, depois de anos sem dar aumento para seus funcionários, tomar uma atitude. Muito foi discutido sobre plano de crescimento, meritocracia, etc e depois de muita discussão, polêmica e briga. Como várias pessoas também saíram da empresa em um curto período de tempo, o RH aproveitou o momento para realizar mudanças no quadro de funcionários (acrescentando e/ou retirando profissionais) ou alterando cargos.O responsável pelo RH, chamado Pedro Bial entregou a você, o DBA, uma lista de mudanças que devem ser refletidas na tabela de empregados.

Com o material necessário pronto para fazer o seu trabalho, algumas instruções lhe são impostas: O pedido do RH foi bastante claro: associar pelo nome que já existe no sistema.

1) Quem estiver com ativo = 0, foi desligado. Logo, deve-se apagar da tabela de Empregados quem foi desligado.

2) O Profissional que estiver na lista de Mudanças do RH mas não estiver na tabela de Empregados deve ser incluído.

3) Pode ter havido alguma alteração de cargo para quem não foi desligado. O valor que deve permanecer é o que vem da lista de Mudanças do RH, desde que o funcionário exista tanto na tabela como na Lista do RH.

4) O profissional que estiver na tabela de Empregados mas não estiver na Lista de Mudanças deve ser mantido como está.

Agora, como dizia Bane: LET THE GAMES BEGIN! O desafio, composto de duas etapas:

1) Construa uma instrução MERGE que esteja dentro do pedido do Pedro Bial, do RH;

2) Construa uma tabela de auditoria temporária que irá receber:

a) O nome do funcionário envolvido na atualização;

b) O nome do evento (INCLUSÃO, EXCLUSÃO OU ATUALIZAÇÃO) seguido do valor antigo e do valor novo, se necessário;

c) Um campo de DATA que informe DATA E HORÁRIO de quando o registro foi incluído na tabela de auditoria; PS: Para o desafio de no.2, sinta-se a vontade para recriar toda a estrutura e adapte a sua estrutura MERGE utilizando a cláusula OUTPUT.

Outra coisa: como você provavelmente vai testar bastante tais comandos, vale a pena abrir algumas transações e ver o resultado por lá mesmo. É isso. Na próxima postagem volto com o resultado. Provavelmente final de semana. Novamente, obrigado pela sua visita e sinta-se a vontade.