Arquivo da categoria: SQL SERVER

Mais um ano como MVP de SQL Server

Bom Dia Pessoal,

O ano de 2010 se inicia e ao abrir meus e-mails recebo a tão esperada confirmação de renovação do meu título de MVP:

Dear Gustavo Aguiar,

Congratulations! We are pleased to present you with the 2010 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in SQL Server technical communities during the past year.

Exatamente há um ano atrás, em janeiro de 2009, recebi um e-mail parecido falando da minha nomeação. No ano de 2008 eu havia feito muitas contribuições e consegui obter o título. Em 2009 as contribuições continuaram, mas muitas vezes fiquei um pouco incerto sobre o processo de renovação. Em alguns momentos senti uma certa vontade de perguntar sobre a renovação, mas a curiosidade deu lugar à discrição. Com um pouco mais de paciência, felizmente a boa notícia acabou chegando.

Gostaria de agradecer a todos que diretamente ou indiretamente ajudaram para a minha renovação. Estou certo que as votações no fórum, a presença nos Webcasts, a recomendação de artigos, entre muitas outras contribuições foram imprescindíveis para que, por mais um ano, eu possa eu tenha o título de MVP. Prometo retribuir esse reconhecimento da comunidade através da postagem de mais artigos, responder mais dúvidas, publicação de mais vídeos.

Muito obrigado e um excelente 2010 para todos nós.

[ ]s,

Gustavo

Objetivos e Metas para 2010

Olá Pessoal,

Dizem que o poder das palavras tem muito mais força do que imaginamos. O simples fato de pensar já faz uma enorme diferença no tempo e no esforço para se conseguir alguma coisa. Enquanto as idéias estão no pensamento elas estão em curso, mas a partir do momento em que elas são escritas e explicitadas sua força se torna muito maior.

O ano de 2009 foi um ano excelente. Fui nomeado MVP em SQL Server, tive a oportunidade de ir à sede da Microsoft em Redmond através do MVP Summit, iniciei no grupo SQL Server DF (grupo vinculado ao Pass) palestrando em dois encontros, ministrei várias aulas em centros autorizados, escrevi dois artigos técnicos para revistas especializadas, entre muitas outras coisas boas.

Boa parte do aconteceu já eram objetivos há algum tempo atrás. Para o ano de 2010 eu espero continuar esse ciclo de realizações. Se pensar já me ajudou no passado, dessa vez vou escrever e compartilhar os objetivos e metas para 2010 de SQL Server. Não sei se vou conseguir realizar tudo o que proponho, mas vou correr atrás do que for necessário:

Meta 01 – Aumentar o portfolio de certificações
Ao longo dos últimos anos venho acumulando certificações de SQL Server. O trajeto foi difícil, mas as recompensas foram bem gratificantes.

  • Microsoft Certified Database Administrator (MCDBA) (obtida em 29/04/2004)
  • Microsoft Certified Technology Specialist (MCTS) (SQL Server 2005) (obtida em 08/12/2006)
  • Microsoft Certified Trainer (MCT) (obtida em 25/06/2007)
  • Microsoft Certified IT Professional Database Developer (MCITP) (SQL Server 2005) (obtida em 05/09/2007)
  • Microsoft Certified IT Professional Database Administrator (MCITP) (SQL Server 2005) (obtida em 21/12/2007)
  • Microsoft Most Value Professional (MVP) (SQL Server) (obtida em 01/01/2009)
  • Microsoft Certified Technology Specialist (MCTS) (SQL Server 2008) – Database Development (obtida em 30/09/2009)
  • Microsoft Certified IT Professional Database Developer (MCITP) (SQL Server 2008) (obtida em 30/09/2009)
  • Microsoft Certified Technology Specialist (MCTS) (SQL Server 2008) – Implementation and Maintenance (obtida em 06/11/2009)
  • Microsoft Certified IT Professional Database Administrator (MCITP) (SQL Server 2008) (obtida em 06/11/2009)

Desde o início de 2008 que estou querendo obter as certificações relacionadas a Business Intelligence de SQL Server 2005 e SQL Server 2008. Minhas experiências em BI começaram no final de 2004 (até então eram teóricas) e mesmo trabalhando com os produtos mais recentes e ministrando diversos treinamentos, estou a tempos adiando essas as provas de certificações. Em 2010, pretendo obter as seguintes certificações de Business Intelligence.

  • MCTS: Microsoft SQL Server 2005 Business Intelligence
  • MCITP: Business Intelligence Developer (SQL Server 2005)
  • MCITP: Business Intelligence Developer 2008 (SQL Server 2008)
  • MCTS: SQL Server 2008, Business Intelligence Development and Maintenance

Meta 02 – Publicar pelo menos dois artigos técnicos

Em 2009 consegui publicar dois artigos técnicos para a revista Mundo .NET. O primeiro artigo foi sobre o uso de Event Notifications para controlar alterações em objetos no banco de dados e o segundo foi sobre o uso de Common Table Expressions em um caso prático (o marketing de rede). Em 2010 espero conseguir publicar mais dois artigos em revistas de alcance nacional.

Meta 03 – Aumentar em no mínimo 25% a taxa de visitas ao blog

No ano de 2009 consegui em um único dia a marca de 427 visitas ao blog. Esse quantidade está bem acima do usual (há uma média de 150 visitas diárias). Alguns artigos são bem atrativos para visitação a exemplo de “Como transformar linhas em colunas”, “Matrizes”, “Como importar dados em XML”, etc. Espero em 2010 conseguir chegar a marca de 200 visitas diárias em média e um pico superior a 500 visitas em um único dia (o negócio é ficar de olho).

Meta 04 – Manter a frequência de publicações

Escrever um artigo para o blog por semana em média não é tarefa fácil (os companheiros e blogueiros de SQL Server sabem disso como ninguém). Seria muito fácil traduzir um determinado material ou ainda copiar alguma idéia de alguém. Não posso negar que me inspiro em muita gente, mas o difícil mesmo é manter a originalidade, a aplicabilidade e qualidade de um artigo. Nesses últimos meses manter essa média tem sido bem difícil, e não raras às vezes, acumulei algumas semanas acabando por postar várias coisas de uma vez só. Vou tentar mantê-la para o ano de 2010.

Meta 05 – Chegar aos 45.000 pontos nos fóruns do MSDN e TechNet

Os fóruns técnicos de SQL Server do MSDN e do TechNet são um dos melhores fóruns de SQL Server que existem. A participação nesses fóruns (juntamente com as comunidades do Orkut) certamente tiveram um peso muito forte na minha nomeação para MVP. Esse ano não consegui dar exatamente a mesma atenção que dei em 2008. Foi um ano corrido com muitos projetos, aulas, consultorias e outras atividades. Diminui sim a quantidade de postagens, mas de forma nenhuma deixei de ajudar (apenas mudei o foco para o blog, artigos técnicos, etc). Atualmente estou com pouco mais de 39.000 pontos. Com mais 6.000 pontos chego à meta de 45.000. Isso significa 500 pontos mensais e certamente será um prazer ajudar a quem precisa.

Meta 06 – Gravar pelo menos três vídeos de SQL Server

Em 2008 realizei seis Webcasts fora a participação de três gravações no MSDN Experience totalizando nove vídeos. Já em 2009 fiz apenas um Webcast referente ao Performance Studio e a participação no SQL Server Day. Espero conseguir gravar pelo menos três vídeos independente da iniciativa (Webcast, SQL Server Day, etc).

Meta 07 – Renovar o título de MVP

Ao contrário de boa parte das certificações, o título de MVP não é obtido através de uma prova e seu período de validade não é eterno ou quando acabar o ciclo de suporte ao produto. Ele vale apenas por um ano após a data de nomeação. Estou somente a alguns dias de completar um ano de renovação e espero conseguir renová-lo. Se isso não for possível, nada impede de obtê-lo novamente em um ciclo futuro de nomeações. Eu sempre ajudarei à comunidade de SQL Server enquanto achar que posso e no caso da não renovação, continuarei a ajudar mesmo assim.

Meta 08 – Escrever um livro sobre SQL Server

Essa é uma meta bem antiga e muitos já me perguntaram "Se você gosta tanto de escrever, se você devaneia através de longos posts e detalhadas explanações, por que não escreve um livro ?". De fato já pensei muito sobre esse assunto e não é por falta de vontade. Se ler um livro já é trabalhoso, imagine só escrever um. Quanto tive as primeiras idéias, as primeiras inspirações, ainda não tinha a experiência necessária (mesmo achando que tivesse). Além disso o grande dificultador era que eu sempre estava um pouco atrás da última release (comecei no SQL Server 7/2000 em 2002, no SQL Server 2005 em 2006 e no SQL Server 2008 em 2009). Agora estou sentindo que estou "empatando" e talvez nada que escreva fique para trás tão rapidamente. Enfim, ainda que nem tudo esteja fechado (apenas alguns rascunhos) espero produzir alguma coisa.

Meta 09 – Ler pelo menos 8 livros de SQL Server

Desde 2002 contabilizo quantos livros de SQL Server (e demais assuntos) eu consigo ler. Até hoje já cheguei a marca de 86 livros (mais de 40.000 páginas) sendo 49 relacionados a banco de dados e 36 específicos de SQL Server. Em 2010 vou adicionar mais oito livros de SQL Server para esse patamar. Certamente que alguns Best Sellers estarão inclusos nessa lista.

Meta 10 – Ambientar-me no SQL Server 2008 R2

Embora o nome continue sendo SQL Server 2008, há vários recursos novos nessa release. Ela não chega a ser uma reformulação completa, mas certamente trará muitas novidades. Espero familiarizar e aprender bastante sobre os novos recursos.

[ ]s,

Gustavo

Prova 70-453 – Upgrade: Transition Your MCITP SQL Server 2005 DBA to MCITP SQL Server 2008 DBA

Boa Tarde Pessoal,

Dando continuidade ao meu ciclo de provas, realizei hoje a prova 70-453 – Upgrade: Transition Your MCITP SQL Server 2005 DBA to MCITP SQL Server 2008 DBA. Eu havia feito a 70-454 no final do mês retrasado para atualizar o MCITP Database Developer de 2005 para 2008. Para realmente manter a qualidade de um profissional de SQL Server, nada como trilhar o mesmo caminho só que na área de administração. Acho que desenvolver em SQL Server sempre exigirá algum conhecimento em administração (backups, permissões, etc) assim como administrar o SQL Server também exigirá algum conhecimento em desenvolvimento (elaboração de scripts, criação de tabelas de controles, etc).

Antes de propriamente falar da prova, registro aqui algumas críticas em relação a Prometric. Após ficar pouco mais de uma hora e meia fazendo a prova e gastando mais algum tempo comentando algumas questões para que a Microsoft refine seus exames, ao acabar a prova fui direcionado para o startup que me solicitou iniciar a prova. Quando vi a tela não acreditei e de forma nenhuma iria realizar a prova de novo (ainda mais depois de ter virado uma noite antes da prova). Chamei o profissional responsável que entrou em contato com a Prometric e recebeu as orientações pertinentes. Infelizmente o resultado da prova não foi impresso e caso eu não receba nenhuma notificação terei que entrar em contato com a Prometric e apresentar um CaseID para que eles avaliem o que aconteceu. Devo reconhecer que a Prometric melhorou alguns pontos como o seu site (antes era de difícil navegação) e a divulgação de vouchers de desconto nos exames Microsoft. Ainda assim, eu continuo sentindo falta do site da Vue Person Education que nunca tive ou ouvi falar de alguém que teve problemas. Após anotar meu CaseID fiquei sabendo que só nessa semana, mais três passaram por esse problema. Enfim, vou aguardar respostas e torcer para que dê tudo certo.

Seguindo a linha dos temores de provas de upgrade na Microsoft, estava me preparando para algo realmente complicado. Embora tenha achado a prova de upgrade do MCITP Database Developer muito fácil, minha expectativa em relação à prova de administração não era a mesma. Não por achar que administrar é mais difícil ou fácil que desenvolver, mas é que a prova 70-443 – Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005 do SQL Server 2005 é muito comentada como difícil e se ela é difícil o que esperar de uma prova de upgrade ? Algo ainda mais difícil com certeza.

Pois bem, a verdade é que essa prova não é tão complicada assim. Não sei se as provas de upgrade do SQL Server estão facilitando, mas novamente me surpreendi em fazer uma prova tão fácil. A primeira parte da prova correspondia 23 questões de marcação. Quando terminei e vi que teria uma segunda parte, logo pensei "Agora vão aparecer aqueles gigantescos estudos de casos das provas 70-441 e 70-443". Para a minha surpresa, a segunda parte da prova também era toda de marcação só que ao invés de 23 questões eram 25 questões. Qual seria a lógica de dividir uma prova em duas partes com exatamente os mesmos critérios de avaliação ? Até pensei que isso era para segmentar os assuntos cobrados, mas havia assuntos que caíram em ambas as partes. Nem mesmo tive questões de simulação com máquinas virtuais a exemplo da 70-431- Microsoft SQL Server 2005 – Implementation and Maintenance. Outra coisa que me chamou a atenção foi não cair nenhuma (absolutamente nenhuma) questão relacionada ao SQL Server PowerShell. Vi uma questão de PowerShell na prova de desenvolvimento, quem diria na de administração ? Pois é. Não caiu nenhuma.

Vamos então às dicas para quem desejar fazer esse exame:

  • Backup & Restore – Vi pelo menos umas oito questões relacionadas a backup e restore. Saber como funcionam os backups full, diferencial e log é prerequisito básico para responder essas questões. Normalmente é repassado um cenário com a descrição de uma política de backup, tipos, horários, etc e solicita-se o caminho mais rápido para restaurar o banco de dados. Um fato que eu não poderia deixar que de comentar é sobre a utilização da opção COPY_ONLY. Apesar da crítica que recebi no artigo "A armadilha do Backup Diferencial – O que fazer para evitá-la ?", houve uma questão com um cenário muito parecido com o que descrevi no artigo. Fico feliz, pois, apesar de ter o meu artigo classificado como "pejorativo", foi exatamente o raciocínio cobrado na questão, ou seja, o uso do COPY_ONLY pode realmente evitar que uma política de backup seja prejudicada. Uma última observação nesse tópico é em relação aos Database Snapshots. Eles realmente podem prover diversos benefícios em cenários de exclusão e atualização acidental de dados, mas cabe lembrar que se o banco ficar comprometido, o snapshot também ficará inacessível. Esse recurso é excelente, mas é necessário lembrar que ele não substitui integralmente um backup.
  • Soluções de Alta Disponibilidade – Uma parte das questões teve atenção voltada a soluções de High Availability (HA). Conhecer Cluster, Database Mirroring e Log Shipping certamente farão diferença e alguns pontinhos a mais. No quesito cluster, normalmente as provas da Microsoft eram mais leves em relação a esse assunto bastante apenas saber que quando o failover for automático e transparente, o Cluster normalmente era a melhor opção. Nessa prova as questões de cluster foram um pouco mais exigentes. Quando usar um ativo & ativo ? E um ativo & passivo ? Quantas instâncias ficaram em cada nó ? Se for instalar um certificado para criptografar as conexões a um Cluster quais são os passos ? A parte de Database Mirroring também não ficou devendo. Alguém aí saberia como atualizar um patch de segurança em um banco espelhado no modo High Availability ? Será que tem que parar o mirror ou não ? A parte de replicação também não ficou muito atrás. Quando será que é melhor usar a P2P ? E a Merge ? Onde será que ela se aplica ?
  • Políticas – Esse certamente é um recurso excelente do SQL Server 2008 que não poderia deixar de ficar de fora. Qual será o modo correto de configurá-la de modo a impedir que certos objetos sejam criados ? Será que ela pode ser utilizada em conjunto com o WMI e WQL no seu papel de padronização ? E como fazer para padronizar uma mesma política em um parque de servidores ?
  • Auditoria – Embora seja um tópico muito bem comentado do SQL Server 2008, vi uma ou duas questões de baixa complexidade. Basta lembrar os principais eventos que tem escopo de banco (Database Audit Especification) e os que tem escopo de servidor (Server Audit Specification)
  • Monitoramento – Quais são as formas de monitoramento e melhora de desempenho no SQL Server 2008 ? DMVs, Profiler, System Monitor, Trace Flags, Resource Governor, Database Tuning Advisor, etc. Sim temos todas essas mas quando usar qual ? Será que o System Monitor é capaz de gerar dados detalhados sobre um Deadlock ? E será que um Trace Flag é capaz de listar os processos que estão sendo bloqueados e deixam uma aplicação lenta ? Ainda na linha de novas features é importante conhecer o Data Collector (ou o Performance Studio se preferir) para não correr o risco de errar.
  • Particionamento – A  velha história da tabela imensa com milhões de registros onde somente um pedacinho dela é consultada e o tamanho está causando problemas de desempenho. O que fazer ? Particionar com certeza, mas e quando for necessário utilizar uma estratégia de particionamento em conjunto com uma estratégia de archieve e expurgo ? Nessas horas conhecer os operadores split, switch e merge são essenciais.
  • Filestream – Mais um recurso do 2008 que rende algumas questões de prova. Será que é possível rodar o Filestream com o Cluster ? E com o Mirror ? E que configurações tem de ser feitas para que usuários externos acessem o stream diretamente sem passar pelo SQL Server ? Vi umas duas questões sobre esse assunto.
  • Miscelância – Existem uma série de outros tópicos que o exame cobra mas que não aparecem com tanta força. Registro de Assemblies em CLR, opções de segurança (Trustworthy, Execute As, etc), reindexação, filegroups, compactação NTFS, etc.

Embora existam bibliografias fantásticas produzidas pela Microsoft Press (a exemplo do Internals do 2008 produzido pela Kalen Delaney), eu não utilizei nenhum desses livros para fazer a prova. Li apenas o livro Microsoft SQL Server 2008 – Management and Administration do MVP Ross Mistry em conjunto com Hilary Cotter. Na minha opinião, a leitura (com o devido entendimento) é suficiente para conseguir aprovação nesse exame. O livro não é direcionado para a prova, mas cobre com profundidade muitos temas relacionados à administração do SQL Server 2008. Para aqueles que implementam o SQL Server 2008 sobre Windows 2008 o livro se torna ainda mais interessante.

Vou ficar no aguardo da Prometric para saber o resultado da prova nos próximos dias (embora eu tenha um palpite de que fui aprovado). No caso da aprovação, conquisto agora as certificações MCTS: SQL Server 2008, Implementation and Maintenance e MCITP: Database Administrator 2008. O jeito é aguardar para conferir. Com uma pendência a menos espero focar no SQL Server Day e responder algumas dúvidas acumuladas.

Atualização (28/11/2009)

Já faz algum tempo que eu queria atualizar mas acabei enrolando. Depois do incidente, tenho que dar meus parabéns para a equipe da Prometric pela forma que eles conduziram o meu caso. Embora tenha ocorrido o problema, seguindo as orientações de um amigo, entrei no site da Prometric e preenchi um formulário no próprio site informando o ID que me passaram, o número da prova, o local de realização e meu MCPID. Em 48 horas, o site atualizou o status da prova para Passed e ainda me mandaram um PDF do Score que foi escaneado. Consegui fazer 918 pontos (devo ter errado umas quatro questões).

[ ]s,

Gustavo

Prova 70-454 – Upgrade: Transition Your MCITP SQL Server 2005 DBD to MCITP SQL Server 2008 DBD

Bom Dia Pessoal,

Nos últimos dias tenho estado um pouco ausente dos fóruns e comunidades de SQL Server. Estive um pouco fora, pois, iniciei meu ciclo de provas de SQL Server. No final de 2007 obtive a credencial MCITP – Database Administrator de SQL Server 2005. De lá pra cá não fiz mais nenhuma prova, e assim como o produto evolui, é necessário me atualizar. Passei as três últimas semanas dedicando-me para a prova 70-454 – Upgrade: Transition Your MCITP SQL Server 2005 DBD to MCITP SQL Server 2008 DBD.

A julgar pelo conteúdo cobrado e por ser uma prova de upgrade eu achava que realmente iria encarar uma prova bem complicada. O primeiro ponto é que nem o site da MS tem referências de material de estudo, livros e learning para essa prova. Isso é ruim, pois, é desconfortável ir para uma prova sem referências. Andei vasculhando a net e simplesmente não há qualquer tipo de simulado ou material recomendado (desconsidero aqui testking, braindumps, etc). Até encontrei alguns, mas apenas para avaliação. Imaginei também que no estilo da 70-447 de upgrade do 2000 para o 2005, essa prova iria ter conteúdo da 70-451 e acabei encontrando um post do Luti (Prova 70-451: Microsoft SQL Server 2008, Designin and Optimizing Database Solutions). Após ler o post, minha impressão era que essa prova seria algo bem difícil.

A verdade é que a prova 70-454, na minha opinião é muito fácil. Foram apenas 41 questões de marcar sem absolutamente nenhum estudo de caso ou ainda alguma questão de simulação que se vê nas provas de MCTS e MCITP do SQL Server 2005. Não vi nada tão complicado e classificaria essa prova no mesmo nível de dificuldade da 70-431 que também é bem fácil. Aos que forem fazer esse exame, seguem algumas dicas para aumentar as chances de aprovação:

  • XML – Não deixe de ler os capítulos de XML do curso 6232 (2008) ou do 2779 (2005). Se você compreender os métodos do XML, Nested XML, Namedspace e o XML Schema Collection, as questões de XML é pra tirar de letra. Não perca tempo com métodos legados do SQL Server 2000 como o OPENXML ou variações como XML Explicit. O Nodes e o Path são bem mais práticos e eficientes.
  • Common Table Expressions (CTEs) – Vi duas questões de CTEs recursivas. Não havia muito mistério, mas para revisar eu indicaria uma lida nos artigos de CTE do Thiago Zavaski.
  • Índices – Vi três questões sobre índices e plano de execução. Entender sobre seletividade, Include Columns e Views Indexadas é suficiente. Recomendo uma olhada no Blog do Fabiano Neves Amorim.
  • Junções – Esse é elementar. Além dos JOINs clássicos (INNER, OUTER, FULL e CROSS), é bom saber como utilizar o CROSS APPLY e a diferença para o OUTER APPLY
  • SETs – É importante conhecer os recursos de GROUPING SETs, ROLLUP e CUBE e quando utilizá-los. O livro "Microsoft SQL Server 2008: T-SQL Fundamentals" do Itzik é excelente. Não é preciso ler o livro todo (embora eu recomende, pois, é muito bom). Basta apenas o capítulo 7 referente a Pivot, Unpivot e Grouping Sets.
  • Bloqueios – Saber como o NOLOCK funciona é básico e sequer foi cobrado. Entender o SNAPSHOT Isolation Level, o HINT READPAST e outros níveis como SERIALIZABLE e REPETABLE READ ajudam bastante.
  • Integridade – A partir do SQL Server 2005 as quatro opções ANSI na construção de FKs, foram contempladas (NOACTION, CASCADE, SET NULL e SET DEFAULT). Uma atenção especial nesse ponto pode garantir algumas questões. Lembre-se que Triggers nunca são a melhor opção se uma constraint puder ser utilizada. 
  • Novos recursos – É bom dar uma lida superficial em recursos como compressão, sparse columns, table parameters, novos tipos de dados, etc. O livro Programming Microsoft SQL Server 2008 (PRO-Developer) cobre muito mais do que o necessário além de ser, na minha opinião, um dos livros mais completos de SQL Server 2008 voltado para desenvolvedores. Uma lida no capítulo 2 (T-SQL Enhacements) tem cerca de 100 páginas e cobre com profundidade muitos dos novos recursos.

Outra dica é prestar bastante atenção nos enunciados. Há questões em que pode-se até não se saber a resposta certa, mas um ORDER BY, um TYPE ou algum outro comandinho fora do lugar entregam a alternativa correta. A prova valia 1000 pontos e consegui marcar 933, o que significa que errei 3 das 41 questões. A duração foi de duas horas e meia, mas consegui acabar a prova em pouco mais de uma hora (1:15 para ser exato). Depois dessa tenho agora mais uma certificação pra coleção. Agora é estudar para as próximas.

[ ]s,

Gustavo

E se Chuck Norris fosse um DBA ? (Versão SQL Server)

Olá pessoal,

Enquanto o MVP Summit não começa, estava lendo alguns e-mails antigos e achei um contando como seria se o Chuck Norris fosse um DBA. Eu havia me esquecido desse e-mail. O texto é um pouco puxado para o ORACLE e é bem engraçado. Originalmente o texto era em inglês e imagino que alguém o traduziu.

  • Chuck Norris não faz DELETEs. Ele olha para os registros e eles correm de medo.
  • Chuck Norris não cria PKs. Os registros simplesmente não atrevem-se a duplicar.
  • Chuck Norris não usa LOG. Ele lembra de todos registros que alterou.
  • Chuck Norris não usa LOG. Ele não vai falhar.
  • Chuck Norris não cria índices. Ele sabe que os registros retornarão o mais rápido que puderem.
  • MSSQL SERVER é muito mais rápido que ORACLE. Basta que o DBA seja Chuck Norris.
  • Uma vez adaptaram o ROUNDHOUSE KICK para o ORACLE. Assim nasceu o TRUNCATE TABLE.
  • Chuck Norris não dá DROP TABLE. Ele dá ROUNDHOUSE KICK TABLE.
  • Uma vez Chuck Norris deu um ROUNDHOUSE KICK em um banco poderoso. Hoje ele é conhecido por ACCESS.
  • Chuck Norris sabe todos os erros do ORACLE de cor. Porque ele criou eles.
  • SELECT SUM(FORÇA) FROM CHUCK_NORRIS; Internal error. Don’t call the support.
  • SELECT CHUCK_NORRIS; Drop database sucessful.
  • SELECT ROUNDHOUSE_KICK FROM CHUC… Lost connection.
  • DELETE FROM CHUCK_NORRIS. Not Found. (Ele está atrás de você, a ponto de dar um ROUNDHOUSE KICK!!!)
  • Chuck Norris tem IGNORE CONTRAINTS automático. Ninguém restringe nada a Chuck Norris. Ninguém.
  • Chuck Norris não faz cursos de ORACLE. A ORACLE é que faz cursos com ele.
  • Chuck Norris instala o ORACLE sem ler o manual. (Quem você pensa que é para ter tentado isso?)
  • Chuck Norris instala o ORACLE em um 486. Rodando KURUMIM. Em 2 minutos.
  • Chuck Norris instala o MSSQL Server em um Pentium 100MHZ. Rodando Solaris. A partis dos fontes.
  • Chuck Norris instala o DB2 em um 486. Rodando WINDOWS VISTA. Sem HD.
  • Chuck Norris não tem Certificação. São as empresas que tentam tirar Certificação em Chuck Norris. Em vão.
  • Se disser ao DBA Chuck Norris que "o problema está no banco", é melhor que esteja se referindo a algum banco Instituição Financeira.
  • Chuck Norris sabe qual o problema de performance do banco. Ele só está dando uma chance do banco se arrepender. 5… 4… 3…
  • Chuck Norris não cria STORED PROCEDURES. Todas suas Queries já se armazenam no banco, tentanto se esconder. Mas é inútil.
  • TRIGGERS tem este nome porque Chuck Norris sempre ameaçava atirar no banco quando ele não fazia algo automático.
  • Chuck Norris não faz Modelo de Dados. Ele encara o banco até que ele faça o modelo sozinho.
  • Chuck Norris instala o ORACLE sem a interface gráfica. E sem a interface texto. (Pergunte a ele você!)
  • Megabyte, Gigabyte, Terabyte, Petabyte, Exabyte, Chuckbite.
  • ORACLE tem as versões Personal, Standard, Enterprise e ChuckNorris Edition. Mas nenhum computador é rápido o suficiente para rodá-lo.

Recentemente recebi um desses um pouco mais adaptado para o SQL Server (não sei se a origem também é inglês ou algum outro idioma) que achei bem engraçado e resolvi publicá-lo. O legal é que está atualizado para o 2008.

  • Chuck Norris consegue instalar e configurar o SQL Server em um cluster de 2048 nós
  • Chuck Norris consegue fazer qualquer atualização de versão. Recentemente ele migrou do SQL Server 4.2 para o 2008 64bits em 10 minutos
  • Toda vez que um Service Pack está pra ser lançado, Chuck Norris é consultado pra ver se está tudo certo (ele responde e verifica no mesmo dia)
  • O banco menos crítico administrado por Chuck Norris é de 50TB em uma máquina com 512GB com 64 processadores rodando SQL Server Express
  • Um banco que usa TDE pode ser atachado ou restaurado em outro servidor perfeitamente se a tentativa partir de Chuck Norris
  • Uma vez Chuck Norris se irritou e executou um Round House Kick contra uma tabela. Daí veio o particionamento de tabelas
  • Os arquivos se agrupam em FILEGROUPs com medo que Chuck Norris os ataque com um Round House Kick
  • Chuck Norris consegue acessar um banco de dados em SUSPECT. Quando o banco percebe que o DBA é Chuck Norris ele se recupera imediatamente
  • Chuck Norris consegue ler o log de transações nativamente
  • Chuck Norris não precisa de auditoria. O banco conta para ele sempre que houver algo de seu interesse
  • Chuck Norris escreve Extended Stored Procedures com o bloco de notas (e ainda compila)
  • DBCC ChuckNorris(‘Banco’) WITH RoundHouseKick foi substituído por ALTER DATABASE Banco SET OFFLINE WITH ROLLBACK IMMEDIATE
  • A stored procedure sp_ChuckNorris é capaz de remover completamente o SQL Server
  • A stored procedure sp_RoundHouseKick libera toda a memória utilizada, mas nunca mais a memória pode ser utilizada novamente
  • O HINT WITH (Chuck Norris) faz com que sua consulta tenha a prioridade máxima
  • A replicação MERGE nunca dá conflito se Chuck Norris tiver configurado
  • Chuck Norris consegue ler e escrever a qualquer momento mesmo se houver um bloqueio do tipo XLOCK
  • Chuck Norris consegue debugar triggers no SQLCMD
  • Chuck Norris faz backup de tabela. Basta ele olhar pra ela e um backup é gerado por segurança
  • O SQL Profiler não consegue registrar as ações de Chuck Norris
  • Qualquer consulta retorna imediatamente quando é feita por Chuck Norris
  • Os índices jamais fragmentam se Chuck Norris estiver por perto
  • Nenhuma trigger ousa disparar se Chuck Norris estiver fazendo alguma coisa
  • Deadlocks tem medo de ser vítimas de um Round House Kick e portanto não ocorrem se Chuck Norris estiver administrando o banco
  • Quando Chuck Norris usa um CURSOR, o desempenho é excepcional
  • O Resource Governor não é capaz de reduzir a utilização de recursos de Chuck Norris
  • BCP é sinônimo de Big Chuck Process e por isso pode importar e exportar dados tão rapidamente
  • A mensagem "Reporting is being generate" nunca apareceu para Chuck Norris
  • Recentemente Kallen Delaney, Paul Randal, Ken Henderson, Kimberly Trip e Brian Knight tiveram aulas de SQL Server com Chuck Norris
  • Nem Chuck Norris consegue administrar um banco de dados melhor que Chuck Norris

É… Eu espero um dia chegar perto do DBA Chuck Norris (rs).

[ ]s,

Gustavo

Mais uma conquista e um novo MVP em SQL Server

Olá Pessoal,

Estou escrevendo a minha primeira postagem no blog em 2009. Já estava me preparando para escrever algo relacionado a dicas de T-SQL que abordei no meu último Webcast ou algo para a série piores práticas. Felizmente tive de mudar os planos. Há algo que gostaria de compartilhar como primeira postagem do ano. Logo no primeiro dia do ano, ao voltar de viagem abri o meu e-mail e recebi uma surpresa. Era uma supresa esperada já algum tempo, mas daquelas que a gente nunca sabe quando vai chegar. Felizmente chegou. Fui agraciado com o título de MVP em SQL Server.

Não sei se todos que lêem conhecem o programa MVP. A julgar pela sigla e pelo fato de estar relacionado à Microsoft, pode parecer apenas mais uma certificação como MCT, MCTS ou MCITP, mas essa sigla é um pouco diferente. Resumidamente falando o programa MVP é um reconhecimento da Microsoft para as contribuições realizadas para os grupos de discussão, as comunidades, grupos de usuários, células acadêmicas, etc. O profissional MVP é aquele que contribuiu para disseminação, suporte e implementação de uma determinada tecnologia como .NET, Windows Mobile, Exchange, SQL Server, etc.

Ao longo dos últimos 6 anos tenho participado de alguns grupos de discussão e comunidades como o DevSQL, o SQL Server Brasil e SQL Brasil no Orkut e mais recentemente os fóruns MSDN e Technet. Para mim é sempre um prazer ajudar aqueles que tem dúvidas e problemas através do meu conhecimento, experiência e disponibilidade. Acho que somente quando compartilhamos o conhecimento é que ele realmente é trabalhado, expandido e refinado e ao longo desses anos estou certo que devo boa parte do meu conhecimento a essas comunidades com as quais compartilhei tantas experiências.

Quero agradecer imensamente a todos aqueles que contribuiram para que eu fosse nomeado MVP em SQL Server. Eu não sei nem por onde começar a lista de agradecimentos além de um enorme receio de esquecer alguém, mas certamente alguns grupos merecem ser lembrados.

Gostaria de agradecer à minha esposa pela paciência e apoio durante todas as horas que estou me dedicando aos estudos e às comunidades, ao meus pais e minha irmã por terem me apoiado nos momentos em que eu iniciava as certificações em SQL Server, aos amigos que incentivaram, aos profissionais da Microsoft pelas oportunidades de Webcasts e vídeos do MSDN, aos Learning Partners pelas oportunidades de ministrar os cursos oficiais (certamente foi uma forte experiência em lidar com dúvidas e problemas de SQL Server), ao pessoal do meu atual trabalho (sem dúvida um dos maiores aprendizados em SQL Server pelo qual já tive contato) e principalmente aos membros das comunidades que participo. Meu agradecimento a vocês que postam dúvidas, expõem problemas, compartilham soluções, assistem os Webcasts, visitam meu blog e me ajudaram a chegar até aqui.

Muito obrigado a todos,

Gustavo

Como resolver problemas relacionados a conflitos de Collation

Boa Noite,

Esse é um dos erros comuns quando trabalhamos com colunas textuais em operações de concatenação ou quando colunas textuais são utilizadas em operações de junção (JOINs) ou em combinação de resultados distintos (UNION). Nesse artigo descreverei porque isso acontece e alternativas para contornar ou resolver esse erro.

Um pouco sobre collation

Não sei se todos os que recebem essa mensagem sabem propriamente porque ela acontece e o que é o tal da collation. No fim das contas o que é importa é resolver o problema (mesmo sem saber exatamente qual era), mas penso que resolver o problema sabendo do que se trata é melhor do que resolvê-lo sem saber do que se trata. Assim sendo, farei uma breve descrição do que é collation.

Definir collation pode ser um pouco difícil, mas para tornar esse conceito simples, prefiro dizer que collation é a forma como tipos textuais serão armazenados, comparados e ordenados. A escolha da collation influenciará nesses três aspectos e a partir disso é que algumas facilidades podem ou não estar disponíveis. Uma collation que faça distinção entre maiúsculas e minúsculas considerará a letra "A" e a letra "a" como caractéres diferentes. Uma collation que faça distinção entre acentos também considerará a letra "Á" diferente da letra "A". Se a collation não faz distinção entre maiúsculas e minúsculas e nem acentos, então poderemos considerar que as letras "A", "a", "Á", "á", "À", "à", "Ã" e "ã" são equivalentes. Há um artigo muito bom publicado por Mauro Pichiliani explicando mais sobre collations. Recomendo que os interessados dêem uma lida. O artigo pode ser acessado através do link http://imasters.uol.com.br/artigo/262. Para aqueles que querem escovar bits e byte em torno desse assunto, o livro Inside SQL Server 2005 – The Storage Engine será de grande valia.

A escolha da collation tem alguns efeitos bem interessantes que merecem ser conhecidos para que você não seja surpreendido. Se você possuir uma collation que faça distinções entre acentos, ao fazer um pesquisa do tipo "Texto LIKE ‘%José%’", você poderá descobrir que os registros que tiverem o nome "Jose" não serão retornados, pois, não possuem acentos. O SQL Server armazena o nome das tabelas, views, procedures e functions em tabelas de sistema e toda vez que um objeto é chamado, uma pesquisa nessas tabelas de sistema é realizada. Se a collation do banco diferenciar maiúsculas de minúsculas, haverá uma diferença entre "SELECT * FROM Tabela" e "SELECT * FROM TABELA". Se há diferença, será necessário digitar o nome da tabela de forma idêntica ao nome criado (imagine quantas confusões não poderiam acontecer se a collation implementada diferenciar maiúsculas de minúsculas).

A collation possui uma hierarquia de três níveis. Podemos definí-la em nível de servidor, em nível de banco e em nível de coluna. A collation em nível de servidor é definida no momento da instalação e se errarmos poderá ser necessário reinstalar o SQL Server (há uma procedimento a respeito publicado por Nilton Pinheiro no site MCDBA Brasil e o mesmo está disponível através do link http://www.mcdbabrasil.com.br/modules.php?name=News&file=article&sid=244). As collations em nível de banco de dados e de coluna podem ser alteradas sem maiores impactos.

Toda vez que uma collation não é especificada, a collation do nível superior é escolhida. Dessa forma, se criarmos um banco de dados e não especificamos a collation, automaticamente a collation do servidor será escolhida para esse banco de dados, se criarmos uma tabela e nela houver colunas textuais sem especificação de collation, automaticamente a collation do banco de dados será atribuída para essas colunas.

Reproduzindo o erro

Enquanto não houver qualquer combinação entre colunas textuais não há como o problema ocorrer. Ainda que colunas textuais sejam combinadas, se todas tiverem a mesma collation também não haverá problemas. Os problemas se manisfestam quando há combinações de colunas textuais com collations diferentes. Façamos um pequeno teste para simular esse problema através do script abaixo:

— Cria a tabela de Atrizes
CREATE TABLE Atrizes (
    Nome VARCHAR(50) COLLATE Latin1_General_CI_AI,
    SobreNome VARCHAR(50) COLLATE Latin1_General_CI_AS)

— Insere registros
INSERT INTO Atrizes VALUES (‘Deborah’,‘Seco’)
INSERT INTO Atrizes VALUES (‘Juliana’,‘Paes’)
INSERT INTO Atrizes VALUES (‘Carol’,‘Castro’)
INSERT INTO Atrizes VALUES (‘Cláudia’,‘Abreu’)

— Combina o nome e o sobrenome em uma única coluna para as atrizes
SELECT Nome, SobreNome, Nome + ‘ ‘ + SobreNome FROM Atrizes

Nesse caso, a mensagem de erro abaixo é produzida:

Msg 457, Level 16, State 1, Line 1
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

Se fizermos uma combinação entre nomes e sobrenomes usando o UNION ou o JOIN também teremos problemas

— Cria a tabela de Atores
CREATE TABLE Atores (
    Nome VARCHAR(50) COLLATE Latin1_General_CI_AS,
    SobreNome VARCHAR(50) COLLATE Latin1_General_CI_AI)

— Insere registros
INSERT INTO Atores VALUES (‘Edson’,‘Celulari’)
INSERT INTO Atores VALUES (‘José’,‘Abreu’)
INSERT INTO Atores VALUES (‘Murilo’,‘Benício’)
INSERT INTO Atores VALUES (‘Tarcísio’,‘Meira’)

— Lista todos as atores e atrizes
SELECT Nome, SobreNome FROM Atores
UNION ALL
SELECT
Nome, SobreNome FROM Atrizes

— Lista atores e atrizes com o mesmo sobrenome
SELECT AO.Nome, AO.SobreNome, AA.Nome, AA.SobreNome
FROM Atores AS AO INNER JOIN Atrizes AS AA
ON AO.SobreNome = AA.SobreNome

Msg 457, Level 16, State 1, Line 2
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

Através das mensagens postadas podemos identificar um conflito relacionados a escolha da collation. Na tabela de atrizes, a collation escolhida para nome difere da collation escolhida para sobrenome produzindo o erro quando essas colunas tem de ser concatenadas.

No momento de fazer o UNION das tabelas, a collation da coluna nome da tabela Atrizes diferente da collation da coluna nome da tabela atores. O mesmo ocorre para a coluna sobrenome de ambas as tabelas.

A diferença de collations na coluna sobrenome também inviabiliza o JOIN que embora tenha uma mensagem um pouco diferente, retorna que um conflito de collation prejudica a operação.

Como podemos observar, quando há uma diferença de collations e desejamos combinar as colunas, um conflito pode se manisfestar inviabilizando a consulta de retornar os resultados desejados.

Soluções Alternativas

Quando há diferença entre as collations, para que o conflito não se manifeste é necessário igualar as collations. Se as collations forem iguais, então não há porque o conflito ocorrer. Umas das formas de fazer isso é através de pequenas modificações nas consultas. Ex:

— Combina o nome e o sobrenome em uma única coluna
SELECT Nome, SobreNome,
    CAST(Nome AS VARCHAR(50)) COLLATE Latin1_General_CI_AI + ‘ ‘ + SobreNome
FROM Atrizes

— Lista todos os atores e atrizes
SELECT Nome, SobreNome FROM Atores
UNION ALL
SELECT
    Nome COLLATE Latin1_General_CI_AI,
    SobreNome COLLATE Latin1_General_CI_AS
FROM Atrizes

— Lista atores e atrizes com o mesmo sobrenome
SELECT AO.Nome, AO.SobreNome, AA.Nome, AA.SobreNome
FROM Atores AS AO INNER JOIN Atrizes AS AA
ON CAST(AO.SobreNome AS VARCHAR(50)) COLLATE Latin1_General_CI_AS = AA.SobreNome

— Lista atores e atrizes com o mesmo sobrenome
SELECT AO.Nome, AO.SobreNome, AA.Nome, AA.SobreNome
FROM Atores AS AO INNER JOIN Atrizes AS AA
ON AO.SobreNome COLLATE Latin1_General_CI_AS = AA.SobreNome

Para igualar a collation, pode-se converter a coluna para o mesmo tipo de dados e especificar a collation através da palavra Collate ou simplesmente utlizar a palavra collate com a especificação de collation desejada. O exemplo dos JOINs demonstra que as duas condições são permitidas.

Há dois problemas com esse tipo de solução. A primeiro é que uma vez que a especificação da collation torna-se obrigatória para combinar colunas textuais, isso pode significar que boa parte das consultas tenham de contemplar essa especificação. O resultado final pode ser bem desagradável afinal alterações por todo o código serão necessárias. O outro ponto negativo é o desempenho. Toda vez que a coluna de uma collation é trocada, será necessário converter todas as linhas para a nova collation. Para piorar a situação, índices que estejam sobre colunas textuais serão desprezados uma vez que não podemos garantir que a ordem dos registros será a mesma antes e após a collation ser trocada. Na cláusula SELECT esse problema não é tão crítico, mas em JOINs pode levar a uma demora sensível no tempo e processamento da consulta. Para diminuir o overhead é recomendável converter apenas uma coluna para a nova collation (normalmente a da tabela menor). Em todo caso, o overhead continuará existindo.

Resolução definitiva

Uma vez que collations diferentes possam levar a conflitos no momento de combinar colunas textuais, a solução definitiva é trocar as collations utilizadas para uma única collation. Assim não haverá mais problemas em combinar colunas textuais. Essa parece ser uma solução adequada, mas alguns pontos merecem atenção antes de adotá-la.

Os bancos de dados possuem uma collation própria. Assim sendo, se você alterar a collation do servidor e restaurar o backup dos bancos de dados, as collations ficarão divergentes já que os bancos de dados continuaram a manter sua collation original. Isso pode parecer não representar problemas, mas lembre-se que o TempDB irá utilizar a collation do servidor. Se você criar tabelas temporárias, elas utilizarão a collation do servidor e se essa divergir da collation do banco os mesmos problemas apresentados podem ocorrer quando você combinar tabelas temporárias com as tabelas do banco (a menos que na criação das tabelas temporárias você especifique a mesma collation que a do banco ou as crie usando a instrução SELECT INTO).

As colunas possuem collation própria e caso você troque a collation do banco de dados através da instrução ALTER DATABASE, apenas as novas colunas irão obedecer a nova collation. As colunas antigas terão a collation original. Uma vez que a collation influencia a forma como os caractéres são armazenados, comparados e ordenados, trocar a collation do banco de dados não terá efeito sobre a collation das colunas existentes, pois, os dados já estão gravados.

Dados as considerações citadas, para conseguir unificar a collation é necessário garantir que a collation do servidor, do banco e das colunas sejam exatamente as mesmas. Dessa forma, novos bancos irão utilizar a collation do servidor e novas colunas irão utilizar a collation do banco e se a collation do banco for igual a collation do servidor, as novas colunas irão portanto utilizar a collation do servidor. Trocar a collation do banco é bastante simples (basta o comando ALTER DATABASE). Trocar a collation do servidor também é simples (a dificuldade pode ser em conseguir uma janela para indisponbilizar o SQL Server). O grande problema está em trocar a collation das colunas já existentes que não serão alteradas automaticamente. Para uma compreensão completa desses últimos parágrafos, vou exemplificar:

— Verificando a collation do servidor
EXEC sp_helpsort

— Criando um novo banco de dados
— A collation é divergente do servidor

CREATE DATABASE BD COLLATE Latin1_General_CI_AS

— Criando uma nova tabela
— A collation é divergente do banco e do servidor
CREATE TABLE BD..tbl (Nome VARCHAR(80) COLLATE Latin1_General_CS_AS)

— Inserindo registros
INSERT INTO BD..tbl (Nome) VALUES (‘Alguém’)

— Tentando combinar registros com uma tabela temporária
CREATE TABLE #tbl (Nome VARCHAR(80))
INSERT INTO #tbl (Nome) VALUES (‘Ninguém’)

SELECT Nome FROM BD..tbl
UNION ALL
SELECT Nome FROM #tbl

No meu servidor, a collation escolhida foi Latin1-General, case-insensitive, accent-sensitive, … Se observarmos ela difere da collation do banco de dados e ambos divergem da collation da coluna. Como pode-se ver, se for utilizado tabelas temporárias e a collation estiver divergente os problemas de conflito de collation continuam a ocorrer (isso aconteceria mesmo que a collation da coluna e do banco de dados fosse a mesma já que o problema é com o TempDB).

Supondo que a collation do servidor já estivesse correta, precisaríamos então alterar a collation do banco de dados para refletir a nova collation.

— Verificando a collation do banco de dados antes da alteração
SELECT DATABASEPROPERTYEX(‘BD’,‘Collation’)

— Alterando a collation do banco de dados
ALTER DATABASE BD COLLATE Latin1_General_CI_AI

— Verificando a collation do banco de dados após a alteração
SELECT DATABASEPROPERTYEX(‘BD’,‘Collation’)

— Criando uma nova coluna
ALTER TABLE BD..tbl ADD SobreNome VARCHAR(80)

Após essa alteração, saberemos que o banco de dados possuirá a mesma collation que o servidor. Ao criarmos uma nova coluna, essa nova collation será a collation escolhida.

— Verificando as collations da tabela
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM BD.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘tbl’

Podemos perceber que a coluna sobrenome já estará com a collation desejada (CI_AI), já que essa é a collation do banco. Toda vez que uma nova coluna for adicionada e sua collation não for definida valerá a collation do banco de dados. O problema está na coluna nome. Como a coluna já existia antes da troca da collation do banco de dados, ela permanece com a collation antiga (CS_AS). A troca da collation do banco de dados não tem efeito sobre colunas já existentes e não foi capaz de alterá-la. Será necessário uma intervenção para alterar a collation desejada. Isso pode ser feito através do SQL Server Management Studio, abrindo-se a estrutura da tabela e efetuando-se a troca. Para uma única coluna, isso é factível, mas e se o banco tivesse dezenas de colunas textuais ? Alterar uma a uma seria impraticável.

Uma das formas de se trocar isso seria a alteração da coluna através de uma instrução de ALTER TABLE. Ex:

— Alterando a collation da coluna Nome
ALTER TABLE BD..tbl ALTER COLUMN Nome VARCHAR(80) COLLATE Latin1_General_CI_AI

O segredo então será então gerar um script que gere todas as instruções de ALTER para todas as colunas textuais e aplicar essas instruções de ALTER. Devemos lembrar que é essencial preservar as características da coluna como tipo de dados, tamanho, nulabilidade, etc. Façamos então um exemplo.

— Declarando uma variável para armazenar a collation do banco
DECLARE @Collation VARCHAR(30)
SET @Collation = CAST((SELECT DATABASEPROPERTYEX(‘BD’,‘Collation’)) AS VARCHAR(30))

— Gerando as instruções de ALTER
— Gerar o ALTER <Tabela> e ALTER COLUMN <Coluna>

SELECT ‘ALTER ‘ + TABLE_NAME + ‘ ALTER COLUMN ‘ + COLUMN_NAME + ‘ ‘ +

— Definir a sintaxe do tipo de dados
CASE
WHEN DATA_TYPE IN (‘Text’,‘NText’) THEN DATA_TYPE
WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN DATA_TYPE + ‘(MAX)’
ELSE DATA_TYPE + ‘(‘ + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ‘)’

— Especificar a collation
END + ‘ COLLATE ‘ + @Collation + ‘ ‘ +

— Especificar a nulabilidade
CASE IS_NULLABLE WHEN ‘YES’ THEN ‘NULL’ ELSE ‘NOT NULL’ END
FROM BD.Information_Schema.Columns

— Filtrar apenas as colunas textuais
WHERE COLLATION_NAME IS NOT NULL

Ao rodar essa instrução, percebemos que as instruções de ALTER TABLE foram geradas para as colunas textuais do banco de dados.

ALTER TABLE tbl ALTER COLUMN Nome varchar(80) COLLATE Latin1_General_CI_AI NULL
ALTER TABLE tbl ALTER COLUMN SobreNome varchar(80) COLLATE Latin1_General_CI_AI NULL

Basta então copiar as instruções de alteração geradas e aplicá-las. Com esse script é possível criar as instruções de alteração para todas as colunas textuais. Uma vez que as instruções estejam disponíveis, as mesmas podem ser aplicadas e os problemas com collation estariam todos resolvidos. A collation do servidor, do banco e das colunas finalmente estaria a mesma.

É bem verdade que o script pode ajudar nessa tarefa, mas existem situações em que ele sozinho não será suficiente. Se uma coluna textual fizer parte de um índice ou uma constraint (PK, UK, FK, CK ou Default) o script irá falhar. A razão é simples. Se um índice é construído sobre um coluna textual, é normal que sua chave contemple a collation da coluna e ao alterar a coluna é necessário alterar também o índice. Infelizmente não poderemos fazer tudo isso em uma única instrução ALTER. O exemplo abaixo irá falhar pela existência de uma constraint sobre uma coluna textual (mesmo que não haja nenhum registro).

— Cria a tabela
CREATE TABLE BD..Estados (UF CHAR(2) COLLATE Latin1_General_CI_AS NOT NULL)

— Cria uma chave primária
ALTER TABLE BD..Estados ADD CONSTRAINT PK_Estado PRIMARY KEY (UF)

— Tenta alterar a collation
ALTER TABLE BD..Estados ALTER COLUMN UF CHAR(2) COLLATE Latin1_General_CI_AI NOT NULL

Normalmente colunas textuais não são escolhidas para chave primárias, mas fatalmente haverá alguma constraint ou índice sobre essas colunas. Então o que pode ser feito é usar o script apresentado para gerar todas as instruções de alteração e as que não funcionarem, uma intervenção manual deverá ser realizada. Se houver poucas colunas com índices e constraints, ficará fácil fazer a intervenção manual. Se houver muitas colunas com índices e constraints, então sim, trocar a collation será bem dispendioso (pode ser mais interessante criar um banco do zero e exportar os dados).

Esse artigo mostra que é possível alterar a collation, mas mostra também o quão importante é definí-la adequadamente. Muitas vezes essa opção é esquecida (principalmente para usuários do tipo NEXT, NEXT, NEXT, FINISH). Se você irá trabalhar com softwares de terceiros, lembre-se de acordar previamente com o seu fornecedor qual será a collation utilizada, para que após o produto estar em produção, você não tenha que alterar a collation de uma solução de terceiros.

Aos mais curiosos, o SQL Server mapeia a relação das constraints e índices em algumas tabelas como sys.check_constraints, sys.foreing_keys, etc. Se for o caso, pode-se evoluir o script para gerar apenas as alterações que não irão ter problemas relacionadosa constraints e índices e depois contabilizar as que irão gerar problemas dessa natureza. Fica aí o desafio de evoluir esse script.

[ ]s,

Gustavo

Transformando Linhas em Colunas com o SQL Server 2005

Boa Noite,

A necessidade de transformar linhas em colunas é bastante presente. Acho que desde os iniciantes nas primeiras aulas de SQL até os mais experientes se deparam com essa necessidade de tempos em tempos. De fato a SQL em si não prevê (até então) algum operador para fazer isso e em tese isso tem suas razões já que transformar linhas em colunas não muda a recuperação dos dados em si, mas fundamentalmente o seu formato, ou seja, os dados são os mesmos mas o que muda é como eles são exibidos.

Ainda que uma camada de banco de dados seja necessária idealmente apenas para gravar e recuperar dados, a necessidade de transformar linhas e colunas continua comum e muita embora a camada de aplicação / apresentação deve ser a responsável por fazer essa conversão, seria muito mais fácil se uma consulta no banco de dados pudesse retornar os dados dispostos dessa forma. Há alternativas como o OWC, o Reporting Services e ferramentas de BI, mas quase sempre elas não são soluções factíveis dependendo da sua ferramenta de desenvolvimento, da arquitetura de sua aplicação ou do seu orçamento.

Ainda acho que não deveria ser o banco de dados o responsável por transformar linhas em colunas. Toda vez que o banco de dados faz algum trabalho que não seja recuperar e gravar dados, ele irá gastar recursos em outras atividades e não irá recuperar e gravar dados tão rapidamente quanto deveria. Idealmente falando isso está correto, mas insistir nessa idéia é contrariar a vontade de muitos e por mais que esteja correto, sempre existirão aqueles que irão fazer o contrário (seja por desconhecimento, vontade ou necessidade).

Há algum tempo atrás fiz um webcast intitulado Dicas e Truques sobre consultas complexas no SQL Server. Nesse webcast eu demonstrei como fazer isso no SQL Server através do operador PIVOT. Demonstrei também como contornar as limitações desse operador e fazer algo mais dinâmico.

De uns dias pra cá estou vendo essa dúvida com uma freqüência muito grande nos fóruns de SQL Server que participo. Sempre que posso indico a solução que postei no Webcast (inclusive disponibilizo o código no blog), mas pelos feedbacks, me parece que o código não está tão claro quanto os usuários gostariam. Talvez seja falta de uma explicação mais clara ou então de fato o código possui mais complexidades do que deveria.

No intuito de ajudar aqueles que passam por essa dúvida (ou que certamente irão passar), resolvi uma entrada aqui no blog para tratar dessa necessidade. Na verdade não demonstrarei somente como transformar linhas em colunas, mas apresentarei uma solução mais completa. Irei criar duas tabelas com os dados necessários para exemplificar.

— Cria as tabelas
CREATE TABLE tblClientes (
    IDCliente INT IDENTITY(1,1),
    NomeCliente VARCHAR(80))

CREATE TABLE tblPedidos (
    IDPedido INT IDENTITY(1,1),
    IDCliente INT,
    DataPedido SMALLDATETIME)

— Cria as constraints
ALTER TABLE tblClientes ADD CONSTRAINT PK_Clientes PRIMARY KEY (IDCliente)
ALTER TABLE tblPedidos ADD CONSTRAINT PK_Pedidos PRIMARY KEY (IDPedido)
ALTER TABLE tblPedidos ADD CONSTRAINT FK_Clientes_Pedidos
FOREIGN KEY (IDCliente) REFERENCES tblClientes (IDCliente)

— Insere clientes
INSERT INTO tblClientes (NomeCliente) VALUES (‘Amanda’)
INSERT INTO tblClientes (NomeCliente) VALUES (‘Ivone’)
INSERT INTO tblClientes (NomeCliente) VALUES (‘Regiane’)
INSERT INTO tblClientes (NomeCliente) VALUES (‘Mariana’)

— Insere pedidos para o cliente 1
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (1,‘20080115’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (1,‘20080328’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (1,‘20080406’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (1,‘20080410’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (1,‘20080523’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (1,‘20080524’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (1,‘20080712’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (1,‘20080812’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (1,‘20080818’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (1,‘20080828’)

— Insere pedidos para o cliente 2
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (2,‘20080411’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (2,‘20080417’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (2,‘20080422’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (2,‘20080430’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (2,‘20080711’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (2,‘20080901’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (2,‘20080903’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (2,‘20080907’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (2,‘20080914’)

— Insere pedidos para o cliente 3
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (3,‘20080122’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (3,‘20080408’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (3,‘20080502’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (3,‘20080510’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (3,‘20080519’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (3,‘20080702’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (3,‘20080703’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (3,‘20080712’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (3,‘20080713’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (3,‘20080718’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (3,‘20080723’)
INSERT INTO tblPedidos (IDCliente, DataPedido) VALUES (3,‘20080729’)

Imagine então que seja desejado um relatório com o seguinte formato (clientes e total de pedidos por mês):

Cliente

01/2008

02/2008

03/2008

04/2008

05/2008

06/2008

07/2008

08/2008

09/2008

Amanda

1

0

1

2

2

0

1

3

0

Ivone

0

0

0

4

0

0

1

0

4

Regiane

1

0

0

1

3

0

7

0

0

Mariana

0

0

0

0

0

0

0

0

0

Desconsiderando a formatação dos nomes e dos períodos, tenho certeza de alguns devem estar pensando "é justamente isso que eu preciso". É possível chegar nesse resultado, mas há um considerável trabalho até então. Vamos então realizar o passo a passo para que a partir dos dados nas tabelas de clientes e pedidos possamos chegar nesse relatório. Antes de propriamente utilizar os recursos do SQL Server 2005, vejamos uma forma de resolver esse problema com base nos recursos do SQL Server 2000 (possivelmente portável para outros SGBDs).

— Relação de Clientes
SELECT NomeCliente AS Cliente,

— Vendas de Janeiro
(SELECT COUNT(*) FROM tblPedidos AS P WHERE C.IdCliente = P.IdCliente
AND YEAR(DataPedido)=2008 AND MONTH(DataPedido)=1) AS [01/2008],

— Vendas de Fevereiro
(SELECT COUNT(*) FROM tblPedidos AS P WHERE C.IdCliente = P.IdCliente
AND YEAR(DataPedido)=2008 AND MONTH(DataPedido)=2) AS [02/2008],

— Coloque aqui os dados dos outros meses alterando mês

— Vendas de Setembro
(SELECT COUNT(*) FROM tblPedidos AS P WHERE C.IdCliente = P.IdCliente
AND YEAR(DataPedido)=2008 AND MONTH(DataPedido)=9) AS [09/2008]

FROM tblClientes AS C

Essa abordagem retorna o resultado do quadro acima, mas possui algumas fortes desvantagens:

  • Há uma gradativa perda de desempenho no uso das funções YEAR e MONTH (quantos mais registros mais forte é a perda)
  • O quadro foi montado, mas foi necessário conhecer previamente todos os meses e codificá-los um a um

A questão do desempenho pode ser resolvida com uso de um índice na coluna data e o uso do Between com essa coluna ao invés das funções MONTH e YEAR, mas o grande problema é resolver a segunda limitação. Como montar o quadro sem saber previamente que os registros iniciavam em janeiro de 2008 e terminavam em setembro de 2008 ? E se aparecesse um registro de dezembro de 2007 ? A consulta iria simplesmente ignorá-lo.

Para resolver esse problema, é necessário para cada mês, construir uma nova subquery com o código abaixo:

— Vendas do mês X
(SELECT COUNT(*) FROM tblPedidos AS P WHERE C.IdCliente = P.IdCliente
AND YEAR(DataPedido)=AnoX AND MONTH(DataPedido)=MesX) AS [MesX / AnoX]

Onde "Mês X" e "Ano X" devem ser obtidos dinamicamente a partir da lista de meses de tblPedidos. Em todo caso, não vou desenvolver esse script já que a idéia é utilizar os novos recursos do SQL Server 2005.

O primeiro passo é gerar uma consulta para simplificar os resultados, afinal não é interessante fazer o JOIN entre Clientes e Pedidos todas as vezes que for necessário. Assim sendo utilizarei uma View para fazer essa junção. A view abaixo contempla todos os clientes quer tenham pedidos ou não.

CREATE VIEW vPedidos (IDPedido, NomeCliente, Mes, Ano)
AS
SELECT

    IDPedido, NomeCliente, MONTH(DataPedido), YEAR(DataPedido)
FROM tblClientes C LEFT JOIN tblPedidos P
    ON C.IdCliente = P.IdCliente

O SQL Server 2005 dispõe do operador PIVOT para transformar linhas em colunas. Através do exemplo abaixo, podemos ver uma utilização básica.

SELECT NomeCliente AS Cliente, [1], [2], [3], [4]
FROM
(SELECT NomeCliente, IDPedido, Mes
    FROM vPedidos) AS TBO
PIVOT
(COUNT(IDPedido) FOR Mes IN ([1], [2], [3], [4])) AS TPVT

Nesse exemplo, podemos ver o retorno de todos os clientes para o meses 1, 2, 3 e 4 respectivamente janeiro, fevereiro, março e abril. A primeira subquery derived table (TBO) é a tabela de origem e contem os campos necessários para montarmos o relatório e mostrarmos os resultados. A coluna NomeCliente e Mes serão exibidas, mas a coluna IDPedido é necessária para fazer a contagem. Na segunda subquery (TPVT) é a Pivot Table e para os meses 1, 2, 3 e 4 irá contar o total de Pedidos. Cada um desses meses será uma coluna.

Essa sintaxe é menor do que a proposta para o SQL Server 2000, mas em todo caso, carece de algumas limitações. A principal limitação é que assim como a proposta anterior, tivemos que saber previamente os meses desejados. Outra limitação nessa primeira construção é que o mês é considerado separadamente. Se tivéssemos pedidos em janeiro de 2007, esses também fariam parte da contagem deprezando o ano (não faria diferença se fosse 2007 ou 2008).

Antes de propriamente resolvermos a limitação de especificação prévia dos valores, é importante resolver a questão do ano. Para isso, faremos algumas adaptações na view vPedidos e na consulta de pivoteamento.

ALTER VIEW vPedidos (IDPedido, NomeCliente, Periodo)
AS
SELECT

    IDPedido, NomeCliente, YEAR(DataPedido) * 100 + MONTH(DataPedido)
FROM tblClientes C LEFT JOIN tblPedidos P
    ON C.IdCliente = P.IdCliente
GO

SELECT NomeCliente AS Cliente, [200801], [200802], [200803], [200804]
FROM
(SELECT NomeCliente, IDPedido, Periodo
    FROM vPedidos) AS TBO
PIVOT
(COUNT(IDPedido) FOR Periodo IN ([200801], [200802], [200803],[200804])) AS TPVT

Com essas alterações, já está incluído no período tanto o ano quanto o mês. Assim, não há mais problemas em termos ordens do mesmo mês (mas de diferentes anos) serem contabilizadas da mesma forma. A grande limitação é que tivemos que especificar os meses previamente.

Se notarmos a consulta, perceberemos que a única parte que precisava realmente ser dinâmica é a especificação dos meses. Se observamos, tanto o primeiro SELECT quanto o último, tem a relação dos meses que necessitamos. Se pudermos construir essa instrução dinamicamente, pode-se montar uma instrução SQL para executar o comando. Para delimitarmos o período completamente, bastaria especificar os períodos inicial e final. O código abaixo faz essa montagem dos períodos dinamicamente.

DECLARE @menorPeriodo INT, @maiorPeriodo INT, @Periodos VARCHAR(500)

— Captura os períodos e inicializa as variáveis
SELECT @menorPeriodo = MIN(Periodo), @maiorPeriodo = MAX(Periodo),
@Periodos = FROM vPedidos

— Montagem dos períodos
WHILE @menorPeriodo <= @maiorPeriodo
BEGIN
    SET @Periodos = @Periodos + ‘[‘ + CAST(@menorPeriodo AS CHAR(6)) + ‘],’
    SET @menorPeriodo = @menorPeriodo + 1
END

— Exibe os períodos
SET @Periodos = LEFT(@Periodos,LEN(@Periodos)-1)
PRINT
@Periodos

Podemos perceber que após a execução do código, obtivemos a relação de todos os períodos existentes iniciando em 200801 até 200809. Com essa parte do comando, podemos disparar uma execução dinâmica. Claro que essa é apenas uma demonstração (do jeito que o código está montado, poderíamos chegar a absurda situação de 13/2008). Além de corrigir esse BUG, ainda devemos fazer algumas adaptações no código, afinal o formato do ano está YYYYMM e o desejado era MM/YYYY. Então façamos a alteração na View e posteriormente na montagem dos meses.

ALTER VIEW vPedidos (IDPedido, NomeCliente, Periodo)
AS
SELECT

    IDPedido, NomeCliente, RIGHT(CONVERT(CHAR(10),DataPedido,103),7)
FROM tblClientes C LEFT JOIN tblPedidos P
    ON C.IdCliente = P.IdCliente
GO

DECLARE @menorPeriodo SMALLDATETIME, @maiorPeriodo SMALLDATETIME, @Periodos VARCHAR(500)

— Captura os períodos e inicializa as variáveis
SELECT @menorPeriodo = MIN(DataPedido), @maiorPeriodo = MAX(DataPedido),
@Periodos = FROM tblPedidos

— Retira os dias das respectivas datas
SET @menorPeriodo = DATEADD(D,-DAY(@menorPeriodo)+1,@menorPeriodo)
SET @maiorPeriodo = DATEADD(D,-DAY(@maiorPeriodo)+1,@maiorPeriodo)

— Montagem dos períodos
WHILE @menorPeriodo <= @maiorPeriodo
BEGIN
    — Captura o mês e o dia
    SET @Periodos = @Periodos + ‘[‘ + RIGHT(CONVERT(CHAR(10),@menorPeriodo,103),7) + ‘],’

    — Adiciona um mês ao menor período
    SET @menorPeriodo = DATEADD(M,1,@menorPeriodo)
END

— Exibe os períodos
SET @Periodos = LEFT(@Periodos,LEN(@Periodos)-1)

PRINT @Periodos

Agora o formato está correto e podemos montar o comando dinamicamente, vamos a solução final.

DECLARE @menorPeriodo SMALLDATETIME, @maiorPeriodo SMALLDATETIME,
@Periodos VARCHAR(500), @cmdSQL VARCHAR(1000)

— Captura os períodos e inicializa as variáveis
SELECT @menorPeriodo = MIN(DataPedido), @maiorPeriodo = MAX(DataPedido),
@Periodos = FROM tblPedidos

— Inicializa a variável @cmdSQL com a montagem do PIVOT
— O caractér ? será substituído pelo período obtido dinamicamente

SET @cmdSQL = ‘SELECT NomeCliente AS Cliente, ?
FROM
(SELECT NomeCliente, IDPedido, Periodo
    FROM vPedidos) AS TBO
PIVOT
(COUNT(IDPedido) FOR Periodo IN (?)) AS TPVT’

— Retira os dias das respectivas datas
SET @menorPeriodo = DATEADD(D,-DAY(@menorPeriodo)+1,@menorPeriodo)
SET @maiorPeriodo = DATEADD(D,-DAY(@maiorPeriodo)+1,@maiorPeriodo)

— Montagem dos períodos
WHILE @menorPeriodo <= @maiorPeriodo
BEGIN
    — Captura o mês e o dia
    SET @Periodos = @Periodos + ‘[‘ + RIGHT(CONVERT(CHAR(10),@menorPeriodo,103),7) + ‘],’

    — Adiciona um mês ao menor período
    SET @menorPeriodo = DATEADD(M,1,@menorPeriodo)
END

— Monta os períodos
SET @Periodos = LEFT(@Periodos,LEN(@Periodos)-1)

— Substitui o ? pelo período montado dinamicamente
SET @cmdSQL = REPLACE(@cmdSQL,‘?’,@Periodos)

— Executa o comando, opcionalmente dê um PRINT
— PRINT @cmdSQL
EXEC (@cmdSQL)

Bom, acho que agora os princípios de tornar o pivot mais dinâmico está devidamente explicado. Com certeza, outras necessidades parecidas irão aparecer. Em todo caso, lembre-se de que essa transformação está dividida em três etapas. A primeira etapa é a formatação das colunas (no caso convertemos a data para MM/YYYY) a segunda etapa é a montagem dinâmica do nome das colunas e a última é a montagem dinâmica do comando. Provavelmente as necessidades irão diferir na primeira etapa (alguns vão desejar colocar o nome do mês ao invés do ano).

Alguns irão indagar a certa do desempenho dessa consulta. Aplicar funções como YEAR e MONTH vão denegrí-la, mas lembre-se que as mesmas foram aplicadas em uma cláusula SELECT e não em uma cláusula WHERE como foi a proposta do SQL Server 2000. Em todo caso, uma coisa é certa sobre o desempenho. Quanto maior for o período analisado, maior serão as transformações e menor será o desempenho. A chave para tornar essa consulta factível do ponto de vista de desempenho é utilizar um filtro nas datas em tblPedido (prevendo que essa coluna possui um índice). A consulta abaixo filtra previamente os registros entre janeiro e agosto (setembro não está incluído).

ALTER VIEW vPedidos (IDPedido, NomeCliente, DataPedido, Periodo)
AS
SELECT

    IDPedido, NomeCliente, DataPedido, YEAR(DataPedido) * 100 + MONTH(DataPedido)
FROM tblClientes C LEFT JOIN tblPedidos P
    ON C.IdCliente = P.IdCliente
GO

SELECT NomeCliente AS Cliente, <Especifição dos meses>
FROM
(SELECT NomeCliente, IDPedido, Periodo
    FROM vPedidos WHERE DataPedido >= ‘20080101’ AND DataPedido < ‘20080901’ ) AS TBO
PIVOT
(COUNT(IDPedido) FOR Periodo IN <Especificação dos meses>) AS TPVT

Essa foi uma alternativa para tornar o código do PIVOT dinâmico através do Transact SQL. Em todo caso, você pode ter a disposição outras formas de apresentar esse resultado. Embora bastante tentador, tente evitar esse tipo de construção dentro do banco de dados. Não é atribuição do banco de dados formatar os dados. Lembre-se que enquanto linhas são formatadas e transformadas em colunas, outras consultas podem estar rodando mais lentamente já que recursos foram desviados para pivotear o resultado.

Quem estiver animado e tiver entendido perfeitamento o código do pivot dinâmico, cabe um desafio final. Experimente tentar colocar uma coluna de totalização somando os pedidos de todos os clientes por mês. Essa é para quem realmente tem o domínio desse código.

[ ]s,

Gustavo

Mapeando dependências entre tabelas

Boa Noite Pessoal,

Hoje estive fazendo uma exportação eventual entre bases de dados SQL Server. A exportação consistia em apenas 30 tabelas do banco de dados de produção para o banco de dados de homologação. Para uma tarefa tão simples, eu pude até me dar ao luxo de escolher como fazer isso. Import / Export Wizard, SSIS com um container ForEachLoop e scripts DML com Linked Servers foram algumas das minhas opções.

O problema não era escolher qual ferramenta já que todas eram boas opções. O problema é que nessa relação de 30 tabelas havia diversos relacionamentos e infelizmente não era possível exportar as tabelas em qualquer ordem já que exportar uma tabela em uma ordem incorreta leva fatalmente a uma violação de foreign keys.

A primeira coisa que pensei foi em excluir as chaves estrangeiras, exportar os dados e recriá-las, mas como o banco tinha 400 tabelas e não apenas 30 não era uma solução muito interessante já que se uma chave fosse excluída e não fosse recriada, brechas de integridade seriam abertas. Mapear todas as chaves envolvidas nas 30 tabelas era trabalhoso também.

Bom, provavelmente alguém já passou por isso, então para me ajudar e ajudar os que como eu esbarraram nesse problema, resolvi dar uma "fuçada" no catálogo do SQL Server 2005 e montar um script que me diga qual é a ordem correta para exportar as tabelas. Não foi preciso ir tão longe. Eu conhecia a sysconstraints do SQL Server 2000 e a Sys.Foreign_Keys do SQL Server 2005 e ao contrário do que eu pensava, apenas a Sys.Foreign_Keys já é suficiente. Estou honrando também uma promessa de um artigo anterior.

Para exemplificar como montar a ordem de exportação das tabelas, vou me utilizar do banco AdventureWorks que vem junto com o SQL Server 2005. Eu poderia utilizar o Northwind também ou quem sabe até o Pubs, mas prefiro utilizar o AdventureWorks que está disponível junto com o produto. Em todo caso, a solução funciona para qualquer banco de dados.

A view sys.foreign_keys tem a descrição de todas as chaves estrangeiras presentes no banco de dados. Algumas colunas que merecem atenção são:

  • Name: Nome da chave estrangeira
  • Object_ID: ID da chave estrangeira (é um apontamento para o ID em sys.objects e sys.all_objects)
  • Parent_Object_ID: ID da tabela que contém a chave estrangeira (é um apontamento para o ID em sys.objects e sys.all_objects)
  • Create_Date: Data de criação da chave estrangeira
  • Modify_Date: Data de alteração da chave estrangeira
  • Referenced_Object_ID: ID da tabela que tem a chave primária que é referenciada pela chave estrangeira (aqui está o pulo do gato)

Uma vez que os IDs são fornecidos, basta utilizarmos a função OBJECT_NAME para recuperar o nome do objeto. A consulta abaixo retorna a relação de todas as FKs e as tabelas das quais elas fazem parte.

SELECT
    Name AS
ChaveEstrangeira,
    OBJECT_NAME(Parent_Object_ID) AS Tabela
FROM SYS.FOREIGN_KEYS

Se evoluírmos um pouco mais, podemos ter o nome da chave estrangeira, a tabela que ela faz parte e a tabela que ela referencia, ou seja, a tabela que contém a chave primária. A consulta abaixo retorna esses dados:

SELECT
    Name AS
ChaveEstrangeira,
    OBJECT_NAME(Parent_Object_ID) AS TabelaFilho,
    OBJECT_NAME(Referenced_Object_ID) AS TabelaPai
FROM SYS.FOREIGN_KEYS

Se desejarmos saber todas as tabelas que possuem uma FK para a tabela Products, podemos adaptar a consulta acima. Se desejarmos saber todas as tabelas que Product referencia, também podemos fazer uma adaptação:

— Selecionar todas as tabelas que referenciam Product através de FKs
SELECT
    Name AS
ChaveEstrangeira,
    OBJECT_NAME(Parent_Object_ID) AS TabelaFilho,
    OBJECT_NAME(Referenced_Object_ID) AS TabelaPai
FROM SYS.FOREIGN_KEYS

WHERE Referenced_Object_ID = OBJECT_ID(‘Production.Product’)

— Selecionar todas as tabelas que Product referencia através de FKs
SELECT
   Name AS
ChaveEstrangeira,
   OBJECT_NAME(Parent_Object_ID) AS TabelaFilho,
   OBJECT_NAME(Referenced_Object_ID) AS TabelaPai
FROM SYS.FOREIGN_KEYS

WHERE Parent_Object_ID = OBJECT_ID(‘Production.Product’)

Podemos ver que na primeira consulta, tabelas como ProductCostHistory, ProductInventory e ProductListPriceHistory dependem de Product já que representam respectivamente o histórico do custo do produto, o inventário do produto e o histórico do preço do produto. Da mesma forma, a tabela Product depende das tabelas UnitMeasure, ProductModel, ProductSubcategory que representam a unidade de medida do produto, o modelo do produto e a subcategoria do produto.

Como podemos perceber, a tabela Product repassa sua PK para as tabelas ProductCostHistory, ProductInventory e ProductListPriceHistory e depende da PK das tabelas UnitMeasure, ProductModel, ProductSubcategory. Assim sendo, se desejarmos exportar essas tabelas, teríamos que exportar primeiro UnitMeasure, ProductModel, ProductSubcategory, posteriormente exportaríamos a tabela Product e por último as tabelas ProductCostHistory, ProductInventory e ProductListPriceHistory. O grande desafio é montar a lista.

Utilizando essas consultas, conseguimos mapear relacionamentos diretos entre as tabelas que dependem de Product e as tabelas de que Product depende, mas isso não nos dá a ordem de exportação. Precisamos lembrar também que existem outras tabelas envolvidas então não podemos simplesmente selecionar uma tabela e mapear suas dependências.

O detalhe para resolver esse problema é lembrar que existem relações de dependências indiretas. Se Product depende de UnitMeasure e ProductCostHistory depende de Product, então indiretamente ProductCostHistory depende de UnitMeasure e ProductCost e nesse caso UnitMeasure precisa ser migrada antes de ProductCost. Na verdade ela precisa estar entre as primeiras tabelas migradas, já que se não for migrada primeiro irá provocar problemas de violação de chave. Esse é mais um caso de relacionamentos hierárquicos e com uma CTE Recursiva, podemos resolver o problema.

Só que não basta utilizar uma CTE Recursiva, pois, existem situações particulares que podem inviabilizá-la. Um autorelacionamento por exemplo, iria tornar a CTE recursiva infinita e ele deve ser tratado antes. Outro ponto a ser trabalhado é que o SQL Server 2005 dá suporte a Schemas e é necessário exibir também os Schemas das tabelas para identificá-las de forma duplamente qualificada (Schema.Objeto). Primeiramente, tratemos o problema do nome com a Function abaixo:

— Cria uma função para mostrar o nome do objeto
CREATE FUNCTION dbo.RetornaNomeObjeto (@ID INT)
RETURNS SYSNAME
AS
BEGIN
    DECLARE
@Nome SYSNAME
    SELECT @Nome = SCHEMA_NAME(SCHEMA_ID) + ‘.’ + NAME
    FROM SYS.OBJECTS WHERE OBJECT_ID = @ID
    RETURN @Nome
END

Com essa função, basta passar o ID de um objeto que será retornado o nome do objeto acompanhado de seu Schema. Para evitar autorelacionamentos, basta colocar a condição WHERE Parent_Object_ID != Referenced_Object_ID. Essa condição impede que a tabela filho seja igual a tabela pai (condição para a existência de um auto relacionamento).

WITH Relacoes (Filho_ID, Pai_ID)
AS (
SELECT Parent_Object_ID, Referenced_Object_ID
FROM SYS.FOREIGN_KEYS
WHERE Parent_Object_ID != Referenced_Object_ID),

Dependencias (Filho_ID, TabelaFilho, Pai_ID, TabelaPai, Nivel)
AS (

SELECT Filho_ID, dbo.RetornaNomeObjeto(Filho_ID),
Pai_ID, dbo.RetornaNomeObjeto(Pai_ID), 1 AS Nivel
FROM Relacoes

UNION ALL

SELECT REL.Filho_ID, TabelaFilho, DEP.Pai_ID, TabelaPai, Nivel + 1
FROM Relacoes AS REL
INNER JOIN Dependencias AS DEP ON REL.Pai_ID = DEP.Filho_ID)

SELECT TabelaPai, COUNT(Nivel) AS Niveis FROM Dependencias
GROUP BY TabelaPai
ORDER BY Niveis DESC

A lógica dessa consulta é simples, as tabelas pai que tiverem a maior quantidade de níveis são as que tem a maior quantidade de tabelas dependentes. Nesse caso, essas tabelas devem ser as primeiras a serem migradas, para permitir que outras tabelas sejam migradas sem violações de chave. Se observarmos as três primeiras tabelas da lista (Production.UnitMeasure, Sales.SalesTerritory, Person.Contact) poderemos ver que elas não dependem de nenhuma outra tabela.

SELECT * FROM SYS.FOREIGN_KEYS
WHERE Parent_Object_ID IN (
    OBJECT_ID(‘Production.UnitMeasure’),
    OBJECT_ID(‘Sales.SalesTerritory’),
    OBJECT_ID(‘Person.Contact’))

A consulta resolve parte de nosso problema. Ela tem a relação de todas as tabelas que repassam suas PKs para outras tabelas como FK. Se migrarmos essas tabelas na ordem estipulada, não teremos problemas, mas embora a consulta tenha a ordem completa, ela não possui a relação de todas as tabelas. É preciso ainda colocar as tabelas restantes que não estão na lista. Finalizamos então nossa consulta com o operador UNION. As tabelas que não estejam na lista, recebem a quantidade de níveis igual a zero. Ex:

WITH Relacoes (Filho_ID, Pai_ID)
AS (
SELECT Parent_Object_ID, Referenced_Object_ID
FROM SYS.FOREIGN_KEYS
WHERE Parent_Object_ID != Referenced_Object_ID),

Dependencias (Filho_ID, TabelaFilho, Pai_ID, TabelaPai, Nivel)
AS (

SELECT Filho_ID, dbo.RetornaNomeObjeto(Filho_ID),
Pai_ID, dbo.RetornaNomeObjeto(Pai_ID), 1 AS Nivel
FROM Relacoes

UNION ALL

SELECT REL.Filho_ID, TabelaFilho, DEP.Pai_ID, TabelaPai, Nivel + 1
FROM Relacoes AS REL
INNER JOIN Dependencias AS DEP ON REL.Pai_ID = DEP.Filho_ID),

Lista (Tabela, Niveis)
AS (

SELECT TabelaPai, COUNT(Nivel) FROM Dependencias
GROUP BY TabelaPai

UNION ALL

SELECT
dbo.RetornaNomeObjeto(OBJECT_ID), 0 FROM sys.tables AS T
WHERE NOT EXISTS (
    SELECT Pai_ID FROM Dependencias
    WHERE Dependencias.Pai_ID = T.OBJECT_ID))

SELECT Tabela FROM Lista
ORDER BY Niveis DESC

Acho que com esse último script, o mapeamento da relação das tabelas fica mais fácil. Espero que os erros de FK diminuam a partir desse script. É possível também adaptá-lo para montar a relação de tabelas abaixo de uma tabela específica. Pode-se por exemplo, montar a lista de tabelas a serem migradas a partir da tabela de produtos. Nesse caso, o NOT EXISTS precisa de algumas adaptações. Deixo essa para os mais fuçadores.

[ ]s,

Gustavo

Mapeando dependências entre objetos

Bom Dia Pessoal,

Uma das necessidades comuns na geração de scripts e alteração da estrutura de alguns objetos é o mapeamento de dependências entre objetos de banco de dados. Se desejarmos por exemplo replicar uma determinada procedure para outro banco de dados, será necessário mapear todas as tabelas que essa procedure utiliza. Ao alterar o tipo de dados de um determinada coluna é necessário saber todas as views que referenciam a tabela que possui aquela coluna.

O SQL Server 2005 possui uma View de metadados (sys.sql_dependencies) que mostra a relação entre objetos e podemos utilizá-la para mapear essas dependências e saber "quem" dependende de "quem". Vejamos um pequeno exemplo para entender como essa View pode nos ajudar nesse mapeamento. Utilizarei o TEMPDB para testes, pois, se esquecermos de excluir algum objeto, quando o SQL Server for reiniciado eles serão automaticamente excluídos.

— Muda o contexto para o TEMPDB
USE TEMPDB
GO

— Cria uma tabela T
CREATE TABLE T (COL INT)
GO

— Cria uma View V1 que acessa a tabela T
CREATE VIEW V1 AS SELECT COL * 2 AS DOBRO FROM T
GO

— Cria uma View V2 que acessa a tabela T
CREATE VIEW V2 AS SELECT COL * 3 AS TRIPLO FROM T, V1
GO

— Cria uma Procedure P1 que acessa a VIEW V1
CREATE PROCEDURE P1 AS SELECT DOBRO FROM V1
GO

— Cria uma Procedure P2 que executa a Procedure P1
CREATE PROCEDURE P2 AS EXEC P1
GO

— Mostra as dependências existentes entre os objetos
SELECT * FROM SYS.SQL_DEPENDENCIES
GO

— Mostra as colunas que "interessam"
SELECT OBJECT_NAME(OBJECT_ID) AS OBJETO,
OBJECT_NAME(REFERENCED_MAJOR_ID) AS DEPENDE_DE
FROM SYS.SQL_DEPENDENCIES

Através do último SELECT podemos ver o mapeamento das seguintes dependências:

– A tabela T não depende de ninguém
– A View V depende da tabela T
– A procedure P1 depende da View V
– A procedure P2 depende da procedure P1

Isso significa que caso seja necessário excluir a procedure P2, por exemplo, saberemos que a procedure P2 será impactada e precisamos nos prevenir para essa mudança. A View V2 pode ser excluída sem maiores problemas do ponto de vista de banco de dados, pois, embora ela dependa da tabela T e da VIEW V1, nenhum objeto depende dela. De forma resumida, se precisarmos saber que objetos dependem de alguém, basta filtrar a coluna REFERENCED_MAJOR_ID. Se precisarmos saber de que objetos alguém depende, basta filtrar a coluna Object_ID. Ex:

— Localizar todos os objetos dependentes da tabela T
SELECT OBJECT_NAME(OBJECT_ID) AS OBJETO,
OBJECT_NAME(REFERENCED_MAJOR_ID) AS DEPENDE_DE
FROM SYS.SQL_DEPENDENCIES
WHERE REFERENCED_MAJOR_ID = OBJECT_ID(‘T’)

— Localizar todos os objetos de que P1 depende
SELECT OBJECT_NAME(OBJECT_ID) AS OBJETO,
OBJECT_NAME(REFERENCED_MAJOR_ID) AS DEPENDE_DE
FROM SYS.SQL_DEPENDENCIES
WHERE OBJECT_ID = OBJECT_ID(‘P1’)

Essas consultas são interessantes para relações diretas, mas na maioria das vezes os mapeamentos não são tão simples assim. Se excluirmos por exemplo a tabela T, a consulta que utilizamos mostrará que apenas os objetos V1 e V2 serão afetados, mas sabemos que na prática se a tabela T for eliminada, todos os objetos utilizados
 serão eliminados. O que ocorre é que diretamente apenas V1 e V2 usam a tabela T, mas indiretamente, todos os objetos se baseiam nela. É necessário portanto, buscar uma solução que mostre tantos as referências diretas quanto as indiretas.

Se a coluna OBJECT_ID mostra o ID do objeto e a coluna REFERENCED_MAJOR_ID mostra o ID do objeto na qual OBJECT_ID depende, temos uma típica situação de hierarquias (bem na linha do Empregado e do Superior). Bastaria então utilizar o recurso de Recursive CTEs para recuperar as dependências indiretas. Ex:

WITH Relacoes (DEPENDENTEID, DEPENDENTENOME, OBJETOID)
AS (

— Recupera todas as relações de dependência
SELECT OBJECT_ID, OBJECT_NAME(OBJECT_ID),
REFERENCED_MAJOR_ID
FROM SYS.SQL_DEPENDENCIES),

Dependencias (DEPENDENTEID, DEPENDENTENOME, OBJETOID, OBJETONOME, Nivel)
AS (

SELECT DEPENDENTEID, DEPENDENTENOME, OBJETOID, OBJECT_NAME(OBJETOID), 1
FROM Relacoes

UNION ALL

SELECT REL.DEPENDENTEID, REL.DEPENDENTENOME, DEP.OBJETOID, OBJETONOME, Nivel + 1
FROM Relacoes AS REL
INNER JOIN Dependencias AS DEP ON REL.OBJETOID = DEP.DEPENDENTEID
)

— Selecionar todas as referências diretas e indiretas
SELECT DEPENDENTEID, DEPENDENTENOME, OBJETOID, OBJETONOME, Nivel
FROM Dependencias
ORDER BY DEPENDENTENOME

Através dessa consulta, podemos perceber que a procedure P1 depende diretamente da View V1 e indiretamente da tabela T já que se V1 dependente de T e P1 depende de T1, então P1 dependente indiretamente de T. Agora ficou fácil, verificar as dependências. Se você deseja saber todos os objetos que dependem de alguém, basta filtrar a CTE pela coluna OBJETONOME. Se desejar saber de que objetos alguém dependente, basta filtrar pela coluna DEPENDENTENOME. Ex:

— Listar todos os objetos que dependem de T
SELECT DEPENDENTEID, DEPENDENTENOME, OBJETOID, OBJETONOME, Nivel
FROM Dependencias
WHERE OBJETONOME = ‘T’
ORDER BY Nivel

— Listar todos os objetos de que P2 necessita
SELECT DEPENDENTEID, DEPENDENTENOME, OBJETOID, OBJETONOME, Nivel
FROM Dependencias
WHERE DEPENDENTENOME = ‘P2’
ORDER BY Nivel

O script é interessante, mas há algumas situações que não são cobertas pelo script. Se houver uma recursividade como por exemplo uma procedure que chama a si própria, o script irá gerar um erro. É uma situação pouco comum, mas se acontecer, é necessário fazer alguns ajustes (deixo essa por conta do mais curiosos).  Outra questão é que FKs não são mapeadas na tabela de dependências e nesse caso, o script não irá gerar as dependências necessárias entre as tabelas por conta das FKs (isso é um bom assunto para um outro artigo).

[ ]s,

Gustavo