Verificando o status do seu Cluster SQL Server
Olá a todos,
O post de hoje é uma dica rápida para os profissionais que precisam trabalhar com instâncias clusterizadas do SQL Server e gostariam de saber algumas informações sobre as mesmas!
Temos o uso do SERVERPROPERTY e das DMV’s: sys.dm_io_cluster_shared_drives / sys.dm_os_cluster_nodes.
Para extrair algumas informações:
SELECT SERVERPROPERTY('IsClustered') as _1_Eh_Clusterizada, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as NoAtual, SERVERPROPERTY('Edition') as Edicao, SERVERPROPERTY('MachineName') as VirtualName, SERVERPROPERTY('InstanceName') as NomeInstancia, SERVERPROPERTY('ServerName') as Virtual_e_InstanceNames, SERVERPROPERTY('ProductVersion') as Versao, SERVERPROPERTY('ProductLevel') as NomeVersaoSemHotfixes
E para extrair outras informações é bastante interessante utilizar as seguintes queries:
SELECT * FROM sys.dm_io_cluster_shared_drives SELECT * FROM sys.dm_os_cluster_nodes
Espero que as queries sejam úteis a vocês!
Abraços,
Thiago Zavaschi
Prevendo o crescimento da suas databases com o SQL Server Data Mining Add-ins for Microsoft Office 2007 – Parte I
Olá pessoal,
O artigo de hoje é a base de um dos itens/indicadores utilizados nas suas análises de health check dos servidores SQL Server: Verificar o crescimento dos seus arquivos de base de dados e log.
Antes de mais nada vamos revisar um ponto importante quanto ao crescimento das databases:
É muito comum encontrar em servidores configurações como: crescimento proporcional 10% para a base e para o arquivo de log e com irrestrito por exemplo. A imagem a seguir mostra onde visualizar estas informações.
Em que isso impacta? Valores pequenos de taxas de crescimento para bases que crescem muito é extremamente prejudicial, pois teremos altas taxas (overhead) de I/O para o crescimento dos arquivos. Por outro lado, valores muito grandes podem gerar um uso descontrolado dos seus discos.
Em um mundo/cenário ideal os crescimentos das databases são monitorados e o aumento físico dos mesmos são planejados e executados de maneira manual. Claro que pela comodidade, é difícil DBAs optarem por esta abordagem.
Mas e como monitorar, ou melhor, prever o crescimento, já que muitos DBAs deixam essas configurações automáticas? Uma das maneiras que eu acho muito elegante é através da utilização do Add-in para o Office 2007, mais especificamente a opção Forecast.
O conteúdo deste post pode ser adaptado para fazer outras previsões através do Office!
O que preciso para começar?
“Então Thiago, o que eu preciso para iniciar?”
Será necessário basicamente:
Microsoft Office Excel 2007; (a princípio não funciona no Office 2010 beta, mas estou verificando se há algum meio).
E um dos pares abaixo:
SQL Server Analysis Service 2008;
Data Mining Add-Ins for Office 2007 (SQL 2008);
http://www.microsoft.com/downloads/details.aspx?familyid=AF070F2C-46F4-47B6-B7BF-48979B999AEB&displaylang=en
OU
SQL Server Analysis Service 2005;
Data Mining Add-Ins for Office 2007 (SQL 2005);
http://www.microsoft.com/downloads/details.aspx?familyid=7C76E8DF-8674-4C3B-A99B-55B17F3C4C51&displaylang=en
Mãos a obra!
Coletando os dados de tamanho das databases
O primeiro passo é definir um mecanismo para a coleta dos dados.
Para isso vamos criar uma tabela que armazenará os dados relativos ao tamanho dos arquivos. Essa tabela poderá ter variações para outros fins, mas manterei ela com um certo grau de simplicidade.
CREATE TABLE TamanhoBaseHistorico ( id INT PRIMARY KEY IDENTITY, nome varchar(128) NOT NULL, -- nome da base nome_fisico VARCHAR(2000) NOT NULL, -- nome físico do arquivo, com diretório tamanho INT NOT NULL, -- tamanho do arquivo em Kb dblog BIT NOT NULL, -- se o arquivo é de log data DATETIME NOT NULL -- data da medição ) GO
De tempos em tempos algum mecanismo deverá coletar os dados necessários e inserí-los nesta tabela.
Entre outras formas (como por exemplo o powershell), podemos fazer uso da system stored procedure chamada sp_msforeachdb e associar a execução da mesma a um job do banco de dados.
Essa stored procedure executa um comando para cada database, substituindo a “tag” [?] pelo nome da base. As informações virão da system view chamada sys.database_files.
O comando que será agendado como um job no SQL Server Agent será, para o nosso caso, o seguinte:
EXECUTE sp_msforeachdb 'INSERT INTO TamanhoBaseHistorico SELECT name, physical_name, size, type, GETDATE() FROM [?].sys.database_files'
Não vou entrar nos méritos da criação de jobs no Agent, mas vamos assumir que estes dados sejam carregados todos os meses, uma vez por mês.
Na próxima parte veremos o processo de instalação do add-in e a utilização do mesmo!
Abraços,
Thiago Zavaschi
SQL Server Saturday Night
Pessoal segue um convite para mais um grande evento de SQL Server promovido pela comunidade técnica!
Eu vou palestrar no evento também! Espero que estejam todos presentes!
--
O SQL Server Day foi um sucesso, sendo assim, alguns participantes resolveram dar um presente de Natal para todos: O SQL Server Saturday Night. Um super-webcast de 5 horas de duração que será realizado no dia 19/12/2009 à partir das 18:00 horas.
A grade está montada da seguinte forma:
- Powershell & Transact-SQL (Laerte Jr) - 18:00 as 18:50 horas
- Profiler e Perfmon - (Vladimir Magalhães) - 18:50 as 19:40 horas
- Alta Disponibilidade: Mirroriing (Vitor Fava) - 19:40 às 20:30 horas
- Performance & Tuning - (Rodrigo Crespi) - 20:30 horas as 21:20 horas
- Database Snapshots (Alexandre Lopes) - 21:20 às 22:10 horas
- SQL Server 2008 R2 (Thiago Zavaschi) - 22:10 às 23:00 horas
Ou seja, voce não pode perder, né? Prepara o telão, chama os amigos, pede a pizza + guaraná .. e assista ao SQL Server Saturday Night !
Aonde eu meu inscrevo? http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032437130&Culture=pt-BR
Abraços,
Thiago Zavaschi
Repostagem – Ranking e Windowing
Essa repostagem eu não colocarei com data anterior, há muitos leitores que não conhecem esse recurso direito, mesmo estando presente desde o SQL Server 2005. Enjoy!
Utilizando Funções de Rank e Windowing
Hoje não vou falar de um recurso novo do SQL Server 2008, porém é bastante interessante e acredito que valha a pena um pequeno artigo sobre (principalmente em português já que não achei muita coisa em português).
As quatro funções T-SQL que vou me ater são: ROW_NUMBER(), RANK(), DENSE_RANK() e NTILE().
A base que utilizarei nos exemplos é a amplamente conhecida Northwind, que pode ser baixada gratuitamente do site da microsoft, mas para poupar o trabalho dos leitores e/ou possíveis mudanças no link, coloquei no meu SkyDrive: aqui.
Vamos começar dando uma pequena olhada nos dados.
Usamos a seguinte query SQL (limitada a 15 resultados e ordenados pelo preço unitário, do maior para o menor):
SELECT TOP 15 ProductID, ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC
Lembro que é uma boa prática utilizar os comandos SQL em caixa alta (todas as letras em maiúsculas).
Teremos o seguinte resultado:
ProductID ProductName UnitPrice
----------- ---------------------------------------- ----------
38 Côte de Blaye 263.50
29 Thüringer Rostbratwurst 123.79
9 Mishi Kobe Niku 97.00
20 Sir Rodney's Marmalade 81.00
18 Carnarvon Tigers 62.50
59 Raclette Courdavault 55.00
51 Manjimup Dried Apples 53.00
62 Tarte au sucre 49.30
43 Ipoh Coffee 46.00
28 Rössle Sauerkraut 45.60
27 Schoggi Schokolade 43.90
63 Vegie-spread 43.90
8 Northwoods Cranberry Sauce 40.00
17 Alice Mutton 39.00
12 Queso Manchego La Pastora 38.00
ROW_NUMBER():
O ROW_NUMBER é a função de ranking mais simples existente. Consiste de atribuir o valor de ranking igual a 1 para o primeiro registro (de acordo com o OVER, ver exemplo a seguir para facilitar), e para cada registro subsequente. Vejam o exemplo a seguir utilizando o ROW_NUMBER(). Percebam também que devido ao ORDER BY do OVER não é mais necessário usá-lo após o WHERE como na query anterior.
SELECT TOP 15 ProductID, ProductName, UnitPrice, ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS ROW_COUNT FROM Products
Teremos o seguinte resultado:
ProductID ProductName UnitPrice ROW_NUMBER
----------- ------------------------ -------- -----------
38 Côte de Blaye 263.50 1
29 Thüringer Rostbratwurst 123.79 2
9 Mishi Kobe Niku 97.00 3
20 Sir Rodney's Marmalade 81.00 4
18 Carnarvon Tigers 62.50 5
59 Raclette Courdavault 55.00 6
51 Manjimup Dried Apples 53.00 7
62 Tarte au sucre 49.30 8
43 Ipoh Coffee 46.00 9
28 Rössle Sauerkraut 45.60 10
27 Schoggi Schokolade 43.90 11
63 Vegie-spread 43.90 12
8 Northwoods Cranberry Sauce40.00 13
17 Alice Mutton 39.00 14
12 Queso Manchego La Pastora 38.00 15
Bastante simples, como eu disse. Porém é bastante útil, eu particularmente a utilizo para fazer paginação nos casos em que ela é feita no banco de dados mesmo. Para tal basta adicionar ao final (por exemplo): WHERE ROW_NUMBER >= 10 AND ROW_NUMBER <= 21.
RANK():
Agora vamos utilizar a função RANK para ranquearmos os produtos do produto mais caro para o mais barato.
Com a função RANK podemos fazer isso, o comportamento do valor de ranking é o seguinte:
Caso haja empate, ambos recebem o mesmo valor de ranking. Só que o registro seguinte ao empate não é o número imediatamente subsequente, mas sim o numero de rank do empate acrescido do numero de registros empatados.
SELECT TOP 15 ProductID, ProductName, UnitPrice, RANK() OVER (ORDER BY UnitPrice ASC) AS RANK FROM Products
Dado o fato de que não há repetição de preços no exemplo inicial, então estou trocando da ordenação do decrescente pata a crescente (ao invés de DESC estou utilizando ASC).
ProductID ProductName UnitPrice RANK
----------- ----------------------- -------- ---------
33 Geitost 2.50 1
24 Guaraná Fantástica 4.50 2
13 Konbu 6.00 3
52 Filo Mix 7.00 4
54 Tourtière 7.45 5
75 Rhönbräu Klosterbier 7.75 6
23 Tunnbröd 9.00 7
19 Teatime Chocolate Biscuits 9.20 8
47 Zaanse koeken 9.50 9
45 Rogede sild 9.50 9
41 Jack's New England Clam Chowder 9.65 11
21 Sir Rodney's Scones 10.00 12
3 Aniseed Syrup 10.00 12
74 Longlife Tofu 10.00 12
46 Spegesild 12.00 15
O RANK trabalhou de modo semelhante ao ROW_NUMBER, mas no caso em que houveram registros com igual preço unitário (UnitPrice, utilizado no OVER) ele tratou com a mesma 'pontuação'. Para os correligionários do futebol, é algo semelhante às posições dos times nas tabelas de campeonatos, e etc.
Mas e se eu desejar que (segundo o exemplo acima) após o "rank 9" que foi repetido fosse o número 10 e não o 11? Para tal temos o:
DENSE_RANK():
O comportamento do é o mesmo do RANK(), o que muda é que não há "pulos", ou seja, se uma tupla retornada contém o rank de número 37, podemos garantir que todos os número do intervalo [1, 36] já apareceram.
A query para o nosso exemplo a seguir é a seguinte:
SELECT TOP 15 ProductID, ProductName, UnitPrice, DENSE_RANK() OVER (ORDER BY UnitPrice ASC) AS DENSE_RANK FROM Products
E a sua execução sobre a base Northwind resulta em:
ProductID ProductName UnitPrice DENSE_RANK
----------- -------------------------------- ---------- ---------
33 Geitost 2.50 1
24 Guaraná Fantástica 4.50 2
13 Konbu 6.00 3
52 Filo Mix 7.00 4
54 Tourtière 7.45 5
75 Rhönbräu Klosterbier 7.75 6
23 Tunnbröd 9.00 7
19 Teatime Chocolate Biscuits 9.20 8
47 Zaanse koeken 9.50 9
45 Rogede sild 9.50 9
41 Jack's New England Clam Chowder 9.65 10
21 Sir Rodney's Scones 10.00 11
3 Aniseed Syrup 10.00 11
74 Longlife Tofu 10.00 11
46 Spegesild 12.00 12
Conforme havia dito, após uma repetição (registros com ProductID 47 e 45, por exemplo), no caso do número 9, temos na sequência o número 10, e não o 11 como no RANK(). O último valor do DENSE_RANK é 12, então também sabemos que todos os números de 1 a 11 já apareceram (com ou sem repetição).
NTILE():
Ao contrário das outras funções que apresentei o valor para este rankeamento não é baseado no valor, mas sim na quantidade de registros. Se temos 12 registros e utilizamos um NTILE(N) (onde o N é um número natural), o SQL tenta agrupar as tuplas de retorno naquele número passado como paramêtro, atribuindo valores iguais para os elementos do mesmo grupo. Um exemplo prático pode ser obtido com a seguinte query SQL:
SELECT ProductID, ProductName, UnitPrice, NTILE(3) OVER (ORDER BY ProductID ASC) AS NTILE FROM Products WHERE ProductID <= 15
A query acima pode fazer você indagar: "Ué Zavaschi, porque você não utilizou o TOP 15 como já havia feito para os demais exemplos?".
Uma rápida explicação:
A questão é que o TOP é algo como um "truncador" de retornos. Uma query que originalmente retornaria 100 linhas de registros, ao executar um TOP 15, dizemos que queremos apenas as 15 primeiras, no entanto o SQL internamente trabalhou com as 100 linhas.
Ao eu executar um NTILE() e aplicar um TOP 15, o NTILE foi aplicado sobre toda a coleção e após isto eu recuperaria os 15 primeiros registros. Devido a tal comportamento, não seria possível mostrar o agrupamento que o NTILE faz, já que muito possivelmente todos os primeiros 15 registros estivessem no mesmo grupo (recebendo 1 como valor).
Como retorno a query apresentada temos a seguinte saída:
ProductID ProductName UnitPrice NTILE (3)
----------- --------------------------------- ---------- ----------
1 Chai 18.00 1
2 Chang 19.00 1
3 Aniseed Syrup 10.00 1
4 Chef Anton's Cajun Seasoning 22.00 1
5 Chef Anton's Gumbo Mix 21.35 1
6 Grandma's Boysenberry Spread 25.00 2
7 Uncle Bob's Organic Dried Pears 30.00 2
8 Northwoods Cranberry Sauce 40.00 2
9 Mishi Kobe Niku 97.00 2
10 Ikura 31.00 2
11 Queso Cabrales 21.00 3
12 Queso Manchego La Pastora 38.00 3
13 Konbu 6.00 3
14 Tofu 23.25 3
15 Genen Shouyu 15.50 3
Gostaria que reparassem também que apesar do OVER, o resultado do NTILE() é baseado principalmente no número de resultados, caso não seja possível uma divisão exata, o SQL Server tentará agrupar da melhor maneira possível, fazendo que alguns conjuntos possuam um número de elementos maior que outros.
Alterando um poquinho a query para recuperarmos um registro a mais, mas mantendo o mesmo número de divisões:
SELECT ProductID, ProductName, UnitPrice, NTILE(3) OVER (ORDER BY ProductID ASC) AS NTILE FROM Products WHERE ProductID <= 16
Nos leva ao seguinte resultado, onde o grupo dos "1" possui um elemento a mais (6 elementos) do que o grupo dos "2" e "3" (5 elementos cada):
ProductID ProductName UnitPrice NTILE (3)
----------- --------------------------------- --------- ----------
1 Chai 18.00 1
2 Chang 19.00 1
3 Aniseed Syrup 10.00 1
4 Chef Anton's Cajun Seasoning 22.00 1
5 Chef Anton's Gumbo Mix 21.35 1
6 Grandma's Boysenberry Spread 25.00 1
7 Uncle Bob's Organic Dried Pears 30.00 2
8 Northwoods Cranberry Sauce 40.00 2
9 Mishi Kobe Niku 97.00 2
10 Ikura 31.00 2
11 Queso Cabrales 21.00 2
12 Queso Manchego La Pastora 38.00 3
13 Konbu 6.00 3
14 Tofu 23.25 3
15 Genen Shouyu 15.50 3
16 Pavlova 17.45 3
"Zavaschi, gostei destas funções de ranking, mas esta última de NTILE me deu uma idéia... E se eu quiser aplicar (na mesma query) um ranking mas para grupos específicos dentre os resultados dessa query, é possível?"
Para tal utilizaremos o PARTITION BY.
PARTITION BY:
As funções de ranking pontem ser combinadas com funções de windowing (vou manter o termo em inglês, por questão de preferência). A função de windowing (janelamento ...) dividirá os registros retornados baseado no PARTITYON BY aliado à cláusula OVER utilizada na função de ranking. Com isso você obterá um ranking separado para cada partição. Vale lembrar que o PARTITION BY também pode ser usado com outras funções agragadoras, como MIN, MAX, etc.
Um exemplo disto pode ser visto a seguir (adaptando os exemplos anteriores para o seguinte exemplo):
SELECT ProductName, SupplierID, ROW_NUMBER() OVER (PARTITION BY SupplierID Order By SupplierID) AS PARTITIONBY FROM Products WHERE ProductID <= 15
Na query exemplo temos a aplicação da função de ranking ROW_NUMBER() sobre as partições geradas pelos registros onde cada Supplier é igual. Para facilitar a visualização estou ordenando também pelo SupplierID, mas não é necessário que o campo seja o mesmo.
A query exemplo nos retorna:
ProductName SupplierID PARTITIONBY
---------------------------------------- ----------- ----------
Chai 1 1
Chang 1 2
Aniseed Syrup 1 3
Chef Anton's Cajun Seasoning 2 1
Chef Anton's Gumbo Mix 2 2
Grandma's Boysenberry Spread 3 1
Uncle Bob's Organic Dried Pears 3 2
Northwoods Cranberry Sauce 3 3
Mishi Kobe Niku 4 1
Ikura 4 2
Queso Cabrales 5 1
Queso Manchego La Pastora 5 2
Konbu 6 1
Tofu 6 2
Genen Shouyu 6 3
No exemplo podemos ver que para janela (correspondida para os grupos onde os suppliers são iguais) temos a aplicação individual da nossa função de ranking.
---
Bom se você, leitor, chegou até aqui, parabéns! Além de mostrar que tem paciência em aguentar o que eu escrevi (hehe), ainda adquiriu um conhecimento bastante interessante sobre ranking no SQL Server.
Gostaria de reiterar que os exemplos são para fins didáticos e que quaisquer dúvidas e sugestões podem entrar em contato. :)
Abraços,
Thiago Zavaschi
Links para a inscrição no SQL Server Day
Pessoal,
Saíram os links para a inscrição no maior evento online de SQL Server online do Brasil!
São ao todo três inscrições e não haverá sobreposição, ou seja, você poderá assistir todos!
SQL Server Day uma tempestade cerebral de SQL Server!
Data: 07/11/2009
09:30 ~ 14:00
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032430235&Culture=pt-BR
14:00 ~ 19:00
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032430237&Culture=pt-BR
19:00 ~ 22:00
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032430239&Culture=pt-BR
Este é um evento realizado pela COMUNIDADE TÉCNICA com o apoio da MICROSOFT.
Nos vemos lá!
Abraços,
Thiago Zavaschi
SQL Server Day – 12 horas (13!) de SQL Server!
Ae pessoal beleza?
O Post hoje é para divulgar um evento online muito bacana que ocorrerá dia 07/11! Será uma verdadeira maratona SQL Server! Será nos moldes do grande evendo oline do PASS que ocorreu fazem alguns meses.
Contaremos com grandes especialistas em SQL Server do Brasil inteiro (infelizmente alguns que gostaríamos que participassem não irão poder), entre eles (copiei do blog do Laerte hahaha):
Gustavo Maia Aguiar,
Junior Galvão,
Felipe Ferreira,
Diego Nogare,
Laerte Júnior,
Fabiano Amorim,
Alexandre Lopes,
Higor Fernandes,
Roberto Fonseca,
Entre outros!
O SQL Server Day começará as 09:00 e terminá as 22:00. A ideia originalmente era fazer 12 horas, mas ampliamos mais ainda!
Eu ministrarei dois webcasts. Um falando sobre Common Table Expressions e o outro ministrarei junto ao Laerte Junior falando de PowerShell e de análise de dados! Mais informações no blog dele: http://laertejuniordba.spaces.live.com/blog/cns!C16042A4306A1328!563.entry
Quando tiver definido a lista de webcasts/palestrantes/horários eu divulgo aqui!
Estão TODOS convidados!!
Abraços,
Thiago Zavaschi
Entendendo as Common Table Expressions – CTE – Parte 2 (Final)
Exemplos de queries recursivas
Bom, mostrei um pouco de teoria, mostrei alguns exemplos didáticos (o de números é bastante utilizado na prática), mas vamos ver mais alguns exemplos:
Vou começar com um exemplo já mostrado aqui no blog (sim, finalmente este é o post sobre o qual falei no referido post :) ).
Este é o método baseado em CTE para realizar Split no SQL Server (atentar para o limite da recursividade, se necessário usar o maxrecursion).
DECLARE @s VARCHAR(8000), @d VARCHAR(10) SET @s = 'separar por espaço em branco' SET @d = ' ' ;WITH split(i,j) AS ( SELECT i = 1, j = CHARINDEX(@d, @s + @d) UNION ALL SELECT i = j + 1, j = CHARINDEX(@d, @s + @d, j + 1) FROM split WHERE CHARINDEX(@d, @s + @d, j + 1) <> 0 ) SELECT SUBSTRING(@s,i,j-i) FROM split
O método é facilmente adaptável para o seu outro grande uso: parser de textos CSV. Basta apenas trocar o delimitador. Simples não?
DECLARE @s VARCHAR(8000), @d VARCHAR(10) SET @s = '123;4;thiago@zavaschi.com;2232323' SET @d = ';' -- Continuação mostrada anteriormente.
Um exemplo interessante que sempre cobro em aula (acho que vou ter que parar de cobrar :P) é para realizar o exemplo clássico do fatorial. O resultado final eu diria ser muito elegante.
WITH fat(f, n) AS ( SELECT CAST (1 as bigint) as f, 0 as n -- fat de 0 é 1 UNION ALL SELECT CAST (1 as bigint) as f, 1 as n -- fat de 1 é 1 UNION ALL SELECT f * (n + 1), n +1 FROM fat WHERE n < 20 AND n <> 0 -- 20 é o limite neste caso, pois o fatorial de 21 -- não cabe em um tipo bigint. O <> 0 é para cortar a recursão -- do primeiro âncora, senão repetiria tudo, faça o teste. ) SELECT f FROM fat WHERE n = 6 -- troque n pelo fatorial que quer calcular
Para melhorar a legibilidade das queries. podemos encapsular isso em uma função:
CREATE FUNCTION fatorial ( @n INT ) RETURNS INT AS BEGIN DECLARE @val INT; WITH fat(f, n) AS ( SELECT CAST (1 as bigint) as f, 0 as n UNION ALL SELECT CAST (1 as bigint) as f, 1 as n UNION ALL SELECT f * (n + 1), n +1 FROM fat WHERE n < 20 AND n <> 0 ) SELECT @val = f FROM fat WHERE n = @n RETURN @val END GO -- Testando a função SELECT dbo.fatorial(3); SELECT dbo.fatorial(4); SELECT dbo.fatorial(7);
(Abrindo um parênteses importante:
A única resalva que faço é quanto a estes calculos matemáticos intensos em queries. O SQL Server trabalha muito bem com as ditas set based operations. ou seja, operações baseadas em conjuntos. Operações envolvendo manipulação de strings e cálculos matemáticos complexos não são o forte do SQL Server. Como resolver então, de outra maneira?
Desde o SQL Server 2005 (apareceu junto com as CTEs) o SQL Server tem a CLR integrada ao seu core. Isso quer dizer que é possível criar objetos para o SQL Server (User Defined Types – UDT, Stored Procedures, Functions, etc) programando em uma linguagem compatível com a Common Language Runtime e adicionar o assembly gerado ao seu banco SQL Server. Não fiz testes para esse exemplo do fatorial (farei em um futuro breve), mas é provável que o tempo de execução seja menor se o mesmo estivesse sendo executado pela CLR.
Farei um post detalhando melhor e também na Mundo.NET da edição de Dezembro/2009 sairá um artigo meu sobre como programar com SQLCLR usando C#!
Fechando o parênteses muito importante)
Para o próximo exemplo eu preciso fazer algumas considerações.
Com o SQL Server 2008 surgiu o tipo de dados HIERACHYID e que é, na minha visão, o melhor método de se trabalhar com registros que possuem comportamento hierárquico. Por uma série de motivos: o banco é SQL Server 2005 (ou esta com Compability Level 90), ou é uma estrutura legada, e se você deseja tratar hierarquias?
Commom Table Expressions podem fazer, e fazem, esse papel muito bem. Digamos que temos uma estrutura hirárquica de empregados e cada registro faz uma referência a outro registro que seria o seu supervisor dentro de uma empresa. O diretor da empresa não possui ligação com ninguém, uma vez que ele não teria um supervisor.
Para esse exemplo poderia usar a tabela de empregados do AdventureWorks, mas como nem sempre todos a possuem instalada (deveriam, pois há centenas de exemplos que a utilizam), eu mesmo montarei uma pequena “base”.
Segue o modelo de dados e script para criação da estrutura.
-- Cria tabela base CREATE TABLE Empregado ( id INT PRIMARY KEY, nome VARCHAR(50) NOT NULL, cargo VARCHAR(50) NOT NULL, id_supervisor INT NULL CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Empregado(id) ); -- Popula a tabela (reparem na sintaxe do insert, -- só é permitida no SQL Server 2008 ou superior). INSERT INTO Empregado VALUES (1, 'Thiago Zavaschi', 'Diretor', null), (2, 'Angelina Jolie', 'Gerente de Vendas', 1), (3, 'Megan Fox', 'Gerente de Marketing', 1), (4, 'Uma Thurman', 'Vendedor', 2), (5, 'Jessica Alba', 'Vendedor', 2), (6, 'Julia Roberts', 'Estagiário', 3);
Ao executar um simples select nesta tabela temos:
id nome cargo id_supervisor
-------- -------------------------- -------------------------------- -------------
1 Thiago Zavaschi Diretor NULL
2 Angelina Jolie Gerente de Vendas 1
3 Megan Fox Gerente de Marketing 1
4 Uma Thurman Vendedor 2
5 Jessica Alba Vendedor 2
6 Julia Roberts Estagiário 3
Creio que todos deverão concordar comigo que esta visulização torna praticamente impossível a noção de hierarquia (e olhe que temos poucos registros, imagine termos centenas dos mesmos.
É fácil retornar qual é o nome do supervisor, mas isso de maneira recursiva não seria nada tranquilo. Vou montar uma query que me retorne o nível da pessoa na hierarquia. Ou seja, para o Diretor o valor será 1, para os gerentes será 2, e para o restante será 3. Com a minah CTE retornando isso, a query subsequente, que consome a CTE podera formatar os resultados, filtrar, enfim, tudo baseado no resultado da consulta recursiva já processada, bacana né? Vamos lá!
;WITH hierarquia(id, nome, cargo, id_supervisor, nivel_hierarquia) AS ( SELECT id, nome, cargo, id_supervisor, 1 as nivel_hierarquia FROM Empregado WHERE id_supervisor IS NULL -- Perceba que aqui no âncora temos como retorno somente o diretor. UNION ALL -- Ligação para a recursão SELECT e.id, e.nome, e.cargo, e.id_supervisor, nivel_hierarquia + 1 FROM Empregado e INNER JOIN hierarquia h ON h.id= e.id_supervisor ) SELECT * FROM hierarquia
A saída deste script é (reparem na coluna nivel_herarquia):
id nome cargo id_supervisor nivel_hierarquia
-------- -------------------- --------------------------- ------------- ----------------
1 Thiago Zavaschi Diretor NULL 1
2 Angelina Jolie Gerente de Vendas 1 2
3 Megan Fox Gerente de Marketing 1 2
6 Julia Roberts Estagiário 3 3
4 Uma Thurman Vendedor 2 3
5 Jessica Alba Vendedor 2 3
“Ok thiago, mas ainda assim não estou conseguindo visualizar, por exemplo, quem é o supervisor, tem como melhorar?”
Tem sim. uma das utilidades das CTEs é: a mesma retornar um result set “complexo de ser calculado” (como o caso da hierarquia) e deixar para o select mais abaixo fazer a formatação/filtragem/ordenação. Isso eixar a estrutura da sua CTE mais genérica e menos “engessada”, fazendo até mesmo com que você possa reaproveitar esse código em outro lugar.
Por exemplo: quero uma listagem com todo o time operacional (maior valor em nivel_hierarquia) da empresa, com seus respectivos supervisores. A query poderia ficar assim:
;WITH hierarquia(id, nome, cargo, id_supervisor, nivel_hierarquia) AS ( SELECT id, nome, cargo, id_supervisor, 1 as nivel_hierarquia FROM Empregado WHERE id_supervisor IS NULL -- Perceba que aqui no âncora temos como retorno somente o diretor. UNION ALL -- Ligação para a recursão SELECT e.id, e.nome, e.cargo, e.id_supervisor, nivel_hierarquia + 1 FROM Empregado e INNER JOIN hierarquia h ON h.id= e.id_supervisor ) SELECT h.nome + '/' + h.cargo as [Operacional], -- perceba a formatação ocorrendo aqui s.nome + '/' + s.cargo as [Tático] FROM hierarquia h JOIN Empregado s ON h.id_supervisor = s.id WHERE h.nivel_hierarquia = (SELECT MAX(nivel_hierarquia) FROM hierarquia) -- perceba que o filtro ocorreu aqui
Com o seguinte output:
Operacional Tático
------------------------------- ------------------------------------
Julia Roberts/Estagiário Megan Fox/Gerente de Marketing
Uma Thurman/Vendedor Angelina Jolie/Gerente de Vendas
Jessica Alba/Vendedor Angelina Jolie/Gerente de Vendas
Tranquilo né? E assim poderíamos evoluir de “N” formas, cada uma atendendo a uma necessidade específica.
Common table Expressions também podem ser utilizadas de outras formas, para fazer tratamento de datas, carga, e muitas outras funções (ver links no final do post para maiores informações).
O que mais saber sobre Common Table Expressions (CTE) ?
Bom, já vimos bastante coisa sobre o que é uma CTE, como funciona, onde se aplica (com alguns exemplos), mas o que mais devemos saber?
Devemos saber que há restrições sobre oq eu podemos usar ou não em CTEs. Vou listar alguams das que eu considero principais (no Books Online – BOL, há a lista completa, ver link ao final do post).
Bom acredito que a primeira coisa que é importante saber é que NÃO é permitido fazer uma referência recursiva dentro de uma subquery. Vou usar o exemplo anterior para demonstrar:
... UNION ALL SELECT e.id, e.nome, e.cargo, e.id_supervisor, (SELECT h.nivel_hierarquia_2 FROM hierarquia) -- ISSO NÃO É PERMITIDO. FROM Empregado e ... ) ...
O que nos gera um erro similar a:
Msg 465, Level 16, State 1, Line 32
Recursive references are not allowed in subqueries.
O que mais não é permitido na definição do membro recursivo? Segue a lista:
- SELECT DISTINCT
- GROUP BY
- HAVING
- Scalar aggregation
- TOP
- LEFT, RIGHT, OUTER JOIN (obs: o INNER JOIN é permitido)
- A hint applied to a recursive reference to a CTE inside a CTE_query_definition.
Você, leitor, deve ter percebido que eu normalmente iniciei minhas CTE’s com um ponto e vírgula (‘;’), não, não fiquei maluco e nem coloquei por engano.
A questão é que a sintaxe para execução da CTE obriga que o statement anterior termine com ponto e vírgula, então acabo sempre colocando, já que o statement anterior normalmente não possui “;” ao seu término!
Há algumas guidelines no books online e vou apenas trancrever aqui, em inglês mesmo (consultar o link no final para o texto completo – em inglês), omiti sobre o maxrecursion que eu já comentei.
Com alguns cuidados básicos é possível extrair o máximo da capacidade das CTEs!
Para saber mais
Seguem alguns links aonde é possível encontrar mais informações sobre as Common Table Expressions. A diferença entre CTE no SQL Server 2008 para o 2008 são mínimas, mas como a própria Microsoft mantém em links separados, seguem ambos os links!
Using Common Table Expressions (SQL Server 2008):
http://msdn.microsoft.com/en-us/library/ms190766.aspx.
Using Common Table Expressions (SQL Server 2005):
http://msdn.microsoft.com/en-us/library/ms190766%28SQL.90%29.aspx
WITH common_table_expression (Transact-SQL) (SQL Server 2008, contém diversos exemplos, muito bom!):
http://msdn.microsoft.com/en-us/library/ms175972.aspx
WITH common_table_expression (Transact-SQL) (SQL Server 2005):
http://msdn.microsoft.com/en-us/library/ms175972%28SQL.90%29.aspx
While x CTE (com exemplos de como fazer carga de dados usando CTE):
http://sqlfromhell.wordpress.com/2009/09/12/while-vs-cte-popular-tabela-de-testes/
Recursive Queries Using Common Table Expressions (SQL Server 2008):
http://msdn.microsoft.com/en-us/library/ms186243.aspx
Recursive Queries Using Common Table Expressions (SQL Server 2005):
http://msdn.microsoft.com/en-us/library/ms186243%28SQL.90%29.aspx
Mundo .NET Ed. № 16 – Recursividade, Hierarquias, CTEs e Consultas SQL aplicadas ao Marketing de Rede
(Tive a oportunidade de revisar este artigo do Gustavo Maia Aguiar – MVP em SQL Server, vale a pena conferir! O artigo se encontra apenas na revista)
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!695.entry
Using CLR Integration in SQL Server 2005 (Se alguém quiser ir se adiantando um pouco no assunto!)
http://msdn.microsoft.com/en-us/library/ms345136%28SQL.90%29.aspx
Finalizando
UFA! Bom pessoal, espero que todos tenham chegado até aqui, e espero também que gostem tanto do artigo quanto eu gostei de fazê-lo!
Utilizem como referência para consultas futuras e qualquer sugestão estou aberto para ouvir!
Comentei que há o tipo de dados HIERARCHYID, presente no SQL Server 2008 e superirores) para manipular registros essencialmente hierárquicos, mas acho que isso não compete a este post, abordo isso mais profundamente numa próxima oportunidade!
Grande abraço!
Thiago Zavaschi
PS: Tive que dividir o artigo em duas partes, pois o Spaces não deixa publicar posts grandes (para a primeira parte veja o post anterior).
LTRIMZERO e Dicas Rápidas
Olá pessoal,
Surgiu a necessidade por parte de um desenvolvedor no projeto que eu estava trabalhando sobre:
“Como remover zeros a esquerda de um elemento do tipo VARCHAR?”.
Inicialmente pensei em usar alguma abordagem relacionada ao CONVERT para algum tipo numérico.
Contudo, não era possível pois poderia existir valores não-numéricos no valor (‘001’, ‘0A’, ‘AB12’, etc).
O jeito que resolvi foi o seguinte (se alguém já fez algo parecido, estou aberto a sugestões!),
Encapsulando em uma função escalar:
-- Criando a função
CREATE FUNCTION dbo.LTrimZero
( @valor VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
BEGIN
RETURN REPLACE(LTRIM(REPLACE(@valor, '0', ' ')), ' ', '0');
END
GO
-- Testes (perceba que estes “DECLARES” só funcionarão no SQL Server 2008+, para SQL Server 2005- utilize DECLARE + SET)
DECLARE @var VARCHAR(30) = 'AAA'
DECLARE @var1 VARCHAR(30) = '000AAA'
DECLARE @var2 VARCHAR(30) = '000AA0A'
DECLARE @var3 VARCHAR(30) = '0000001'
SELECT
dbo.LTrimZero(@var) AS [Var],
dbo.LTrimZero(@var1) AS [Var1],
dbo.LTrimZero(@var2) AS [Var2],
dbo.LTrimZero(@var3) AS [Var3]
Perceba que o comportamento da função é bem simples:
1) Troca tudo por espaço (' '), inclusive os "0" que eu não posso remover.
2) Executa um LTrim, que remove todos os espaços em branco a esquerda do parâmetro. (Isso já responde outro questionamento que já me fizeram: “Porque não há o TRIM para o T-SQL, pois o RTRIM e o LTRIM sozinhos, supostamente, não teriam utilidade”. Eu gostaria que tivesse um TRIM sim, mas o LTRIM e o RTRIM sozinhos não são
3) E por fim troco todos os espaços (' ') por zero novamente.
Vejam que na saída do script não sumiu os “0” que deveriam permanecer:
Var Var1 Var2 Var3
------------------------- -------------------------- -------------------------- --------------------------
AAA AAA AA0A 1
----
Agora duas dicas rápidas:
Muitos me perguntam (na verdade reclamam) sobre o “Edit Top 200 Rows” (presente no SSMS 2008 e no SSMS 2008 R2) que substituiu o “Open Table” (SSMS 2005), dizendo que 200 linhas é pouco.
O motivo da troca é pelo fato de que: tabelas densamente povoadas (muitos registros) ao executar o “Open Table” poderia ocorrer no travamento do SQL Server Management Studio. Eu recomendo que esse valor seja mantido. Mas sim, é possível alterar este valor e até mesmo deixar com o mesmo comportamento do “Open Table” do SSMS 2005.
Para isso vá em: Tools –> Options –> SQL Server Object Explorer –> Commands e troquem o valor do “Value for Edit Top <n> Rows command” para o valor desejado. Ao colocar “0” será o mesmo que o “Open Table”, neste caso a opção no menu do SSMS será “Edit All Rows”.
-
Acredito que muitos já utilizem o SQL Server 2008 e que alguns (como eu) já brinquem com o CTP do R2, mas mesmo assim sempre acaba aparecendo alguma coisa que não haviamos visto, ou a necessidade de se aprofundar em algum tema.
Para tal existem os Training Kits que fornecem demonstrações, apresentações e hands-on para ensinar a nova tecnologia.
Em maio deste ano saiu o Training Kit do SQL Server 2008 (http://www.microsoft.com/downloadS/details.aspx?familyid=E9C68E1B-1E0E-4299-B498-6AB3CA72A6D7&displaylang=en), e é obrigatório para quem ainda não está por dentro da plataforma de dados da Microsoft.
A dica é que a versão atualmente disponível não é compatível com o Windows 7. Entrei em contato com o time que desenvolve o traing kit e me foi passado que no final de setembro já teremos uma versao compatível, e me passou um work-aroud temporário para funcionar no Win 7.
No training kit (após descompactá-lo) há uma série de arquivos chamados "Dependencies.xml", altere-os para que fiquem similares a:
<os type="Vista;Server" buildNumber="6000;6001;6002;7000;7001;7056;7057;7100;7600;">
O 7600 é a versão RTM do Windows 7. :)
E é isso pessoal, até a próxima!
Thiago Zavaschi
Repostagem – Database Mail – Configuração Através de Scripts
DatabaseMail – Configuração através de scripts
Postei um tempo atrás um artigo sobre o DatabaseMail (aqui), mas era através dos menus de configuração. Hoje trago para vocês a realização da mesma tarefa, só que através de scripts.
Configurando o DatabaseMail
-- Cria uma account do Database Mail EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SQL', @description = 'Conta de email para o envio do email na rotina de log de erro.', -- Uma descrição para a sua account @email_address = 'sql@zavaschi.com', -- Email que será -- mostrado como o emissor @replyto_address = 'sql@zavaschi.com', -- Email de reply @display_name = 'SQL Server', -- Nome mostrado no email enviado @mailserver_name = 'smtp.zavaschi.com', -- Servidor smtp @port = 25, -- Porta @username = 'sql', -- Login do servidor de smtp @password = 'sql'; -- senha do servidor de smtp -- Cria um profile do Database Mail EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'SQL Profile', @description = 'Profile usado para o envio do email na rotina de log de erro.' ; -- Cria o profile -- Adiciona a account ao profile EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'SQL Profile', @account_name = 'SQL', @sequence_number =1 ; -- Associa a account ao profile, recém criados. -- Garante acesso ao profile a todos os usuarios da base msdb EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'SQL Profile', @principal_name = 'public', @is_default = 1; -- Configura o profile criado como público e padrão.
Pronto, só isso. E agora para enviar email é da mesma maneira:
EXEC msdb.dbo.sp_send_dbmail @profile_name='SQL Profile', @recipients='sql@zavaschi.com', @subject='Titulo', @body='Corpo da mensagem.'
Tranquilo né? Mas se ao executar a procedure de envio de email você obter o seguinte erro:
Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.
É pelo fato do DatabaseMail estar desabilitado no seu SQL Server, então rode o seguinte script:
sp_configure 'Database Mail XPs', 1 GO RECONFIGURE
Há alguns casos que não é possível executar o RECONFIGURE diretamente (sem entrar nos méritos)
Executar então: RECONFIGURE WITH OVERRIDE
Espero que seja útil para você, acredito que sim, pois muitos me pedem!
Grande abraço!
Thiago Zavaschi
Repostagem – Como Proteger as Minhas Stored Procedures ?
Como proteger as minhas stored procedures?
Bom pessoal, o assunto do post de hoje não é uma novidade, mas hoje a tarde estava conversando com um amigo (Eduardo Ordine) e chegamos ao assunto de proteção às stored procedures no banco de dados SQL Server, vamos lá!
“Thiago, estou colocando minhas stored procedures em um banco de dados compartilhado, ou que a administração não cabe a mim, e nas mesmas eu tenho regra de negócio importante e confidencial da minha empresa, tem como protegê-las?”
A resposta é: Sim!
As aplicações costumam ser divididas em camadas para agilizar seu entendimento e, principalmente, facilitar as manutenções. Acontece que eu, como consultor/estudante/desenvolvedor, já MUITAS empresas que colocam a regra de negócio em stored procedures do banco de dados, utilizando as linguagens de programação (.NET (C# e VB.NET), Delphi, VB, etc.) apenas como casca gráfica. Méritos e desvantagens à parte, temos o que: a necessidade de proteger estas procedures no banco de dados. Entendemos que “proteger” é igual a evitar que descubram o conteúdo das procedures.
Proteger 100% e garantir que NUNCA NINGUÉM conseguirá ver o conteúdo da procedure é utopia, mas podemos dificultar BASTANTE o acesso à mesma através da encriptação da procedure.
A stored procedure continua podendo ser executada normalmente (segundo as permições dos usuários da base de dados), mas ninguém ve o seu conteudo.
Como fazer?
Se a sua procedure ia ser criada com um comando similar a este:
CREATE PROCEDURE dbo.spImportante BEGIN SELECT 'Thiago Zavaschi' END
Então utilize da seguinte forma:
CREATE PROCEDURE dbo.spImportante WITH ENCRYPTION AS BEGIN SELECT 'Thiago Zavaschi' END
“Pronto Thiago, encriptei a minha procedure, estou seguro e não preciso me preocupar com mais nada, certo?”
Errado! E vejamos o porquê.
Vou criar aqui a procedure do exemplo acima e tentarei fazer alguns testes.
Primeiro farei um exec.
---------------
Thiago Zavaschi
(1 row(s) affected)
Beleza, executado com sucesso! Agora vou executar a sp_helptext:
The text for object 'spImportante' is encrypted.
Não consigo ver, até mesmo o “Modify” do Management Studio fica desabilitado, impedindo a geração do script (e o mesmo vale para todas as formas de geração de scripts do SSMS).
“Bom Thiago, para mim parece bem seguro.”
Será mesmo?
Vamos fazer mais um teste: Vou ligar o SQL Server Profiler monitorando o banco e vou executar novamente a procedure (vimos que a execução é permitida sem problemas), e temos:
Ok, texto encriptado, mas e se rodarmos o profiler DURANTE a criação da procedure:
Opa, opa, opa, temos a visualização da procedure. Tem como proteger-se disso? Tem sim, basta adicionar um comentário (-- comment: sp_password) ao seu script.
Infelizmente há como burlar essa segurança (não vou mostrar como se faz, pois não acho ético), eu fiz aqui alguns métodos mas não consegui para algumas procedures, pois eram SPs CLR.
Consegui abrir essas stored procedures por outros métodos (que também não vou comentar quais), mas fiz isso só para mostrar que com segurança não devemos brincar!
Então fica aí a dica!
E não se esqueçam de manter o código original da stored prcedure em um lugar a salvo (de preferência sob o controle de um controlador de código como o TFS, etc.).
Grande abraço e até a próxima!
Thiago Zavaschi