Qual o impacto do meu script?

Às vezes pode não parecer, mas tudo dentro de um banco de dados está interligado. Quando pensamos em interligações, é comum lembrar de duas tabelas associadas por uma chave estrangeira ou Foreign Key. No entanto, esta é apenas uma das possíveis relações de dependência dentro do SQL Server. Assim como no caso de uma FK, os objetos envolvidos numa relação de dependência podem ser afetados caso ocorra alguma alteração, seja nos objetos que dependem deles, seja nos objetos dos quais eles dependem. Vejamos um exemplo:

CREATE DATABASE DB1;
GO

USE DB1;
-- Cria uma tabela Tab1
CREATE TABLE dbo.Tab1 (id INT IDENTITY,col1 INT DEFAULT 99);
INSERT INTO dbo.Tab1 DEFAULT VALUES;
GO
-- Cria uma função func1 que depende de Tab1
CREATE FUNCTION dbo.func1 (@id INT)
RETURNS INT AS
BEGIN
DECLARE @col INT;
SELECT @col = col1 FROM dbo.Tab1 WHERE id = @id;
RETURN(@col);
END
GO
-- Cria a procedure sp1 que depende de func1
CREATE PROCEDURE dbo.sp1 (@id INT)
AS
BEGIN
SELECT dbo.func1(@id) + 1;
END
GO

Em nosso DB1, temos uma relação de dependência dos objetos. Então, caso ocorra uma alteração na tabela “dbo.Tab1” onde o nome da coluna “col1” seja alterado para “col2”, a função escalar “dbo.func1” deixará de funcionar e, por sua vez, a procedure “dbo.sp1” também deixará de funcionar. No entanto, a alteração poderia ser na função. Supondo que a função mude sua quantidade de parâmetros, nada será afetado na tabela “Tab1”, mas a procedure não funcionará mais.

Para uma base simples como esta, fica fácil observar esta relação entre os objetos. Já numa base com milhares de objetos, onde alguns deles se relacionam com objetos de outras bases (dependência cross database), esta não é uma tarefa trivial. Vejamos algumas formas de tornar esta verificação mais prática.

Método 1 – Object Explorer

A forma mais prática para verificar a relação de dependência entre os objetos é através do Object Explorer do SSMS. Basta abrir as pastas até o objeto que você quer avaliar, clicar com o botão direito e depois ir em “View Dependencies”.

img01

Assim, será aberta uma janela com duas opções. A primeira, selecionada por padrão, mostra os objetos que dependem de “Tab1”. Expandi o nó em “func1” para mostrar que “sp1” também depende de “Tab1” através da função. Na outra opção, “Objects on which [Tab1] depends”, são listados os objetos dos quais a “Tab1” depende e, neste caso, sabemos que esta tabela não depende de nenhum outro objeto.

img02

O principal ponto positivo deste método é a identificação de toda a relação de dependência. No entanto, além da permissão de SELECT na DMV “sys.sql_expression_dependencies [1]”, é necessária a permissão VIEW DEFINITION [2] em todos os bancos de dados que são referenciados por estes objetos e isso nem sempre é possível. Isso nos leva ao método 2.

Método 2 – T-SQL

Este método é documentado no Books Online [1] e traz dois scripts que servem como as duas opções do método anterior. Então, para retornar os objetos que dependem de “func1”, basta executar:

USE DB1;
GO
SELECT
OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
OBJECT_NAME(referencing_id) AS referencing_entity_name,
o.type_desc AS referencing_desciption,
COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,
referencing_class_desc, referenced_class_desc,
referenced_server_name, referenced_database_name, referenced_schema_name,
referenced_entity_name,
COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referenced_id = OBJECT_ID(N'dbo.func1');
GO

Como resultado, temos o seguinte:

img03

O layout do resultado foi ajustado para caber na página e mostrar as informações interessantes. Na imagem acima, vemos que a procedure “sp1” faz referência à função. Para verificar quais objetos são referenciados por “func1”, ou seja, os objetos dos quais a função depende, executamos o seguinte script:

USE DB1;
GO
SELECT
OBJECT_NAME(referencing_id) AS referencing_entity_name,
o.type_desc AS referencing_desciption,
COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,
referencing_class_desc,
referenced_server_name, referenced_database_name, referenced_schema_name,
referenced_entity_name,
COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID(N'dbo.func1');
GO

Como esperado, o resultado é o seguinte:

img04

Assim vemos que “func1” depende de (ou faz referência à) “Tab1”. Vale lembrar que este método não irá listar toda a relação de dependência, mas apenas a relação direta de referências. Desta forma podemos verificar e saber qual será o impacto da mudança que teremos de fazer.

Próximos Passos

Agora que já sabemos quais objetos serão afetados com as alterações que faremos, precisamos tomar algumas providências como:

  • Entrar em contato com os responsáveis pelos objetos afetados conversando sobre as mudanças necessárias e os possíveis impactos;
  • Documentar as alterações necessárias juntamente com o objeto registrando quais foram as alterações, onde ocorreram, quando ocorreram e quem foi o autor da alteração;
  • Após a alteração do objeto no ambiente de desenvolvimento, realizar testes rigorosos tanto do objeto alterado como dos objetos afetados.

Desta forma, realizamos manutenções de código e alterações de objetos de uma maneira mais consciente e controlada. Lembre-se, o próximo código a quebrar pode ser o seu.

Links:

[1] sys.sql_expression_dependencies

[2] VIEW DEFINITION

 

Anúncios

[On-line Training] T-SQL Level Up

Boa tarde, pessoal!

Venho rapidamente compartilhar um treinamento gratuito para melhorar as habilidades de escrever T-SQL.

O treinamento “T-SQL Level Up”[1] foi criado pelo Doug Lane [2] da família Brent Ozar Unlimited [3] e visa dar uma incrementada nas suas habilidades de escrita de código SQL através dos seguintes tópicos:

  • Thinking in Sets;
  • Number and Data tables;
  • Case Expressions;
  • Computed Columns;
  • Windowing Functions (Design & Performance);
  • Replacing Cursors;

O treinamento é dividido em vídeos curtos com conteúdo abordado de forma bem didática. O instrutor usa uma temática nerd para o curso com bastante eficácia e exemplos pertinentes encontrados no dia-a-dia.

Vale lembrar que o treinamento é gratuito apenas até segunda-feira, dia 21/09, portanto se inscreva logo. Após a inscrição, você terá acesso aos vídeos por 18 meses.

Fiz o treinamento e achei muito divertido. O conteúdo é excelente para que escreve SQL diariamente e sabe que é possível melhorar suas técnicas e o desempenho de suas consultas. Eu recomendo!

Já se inscreveu? Você fez o curso? O que achou?

LINKS

[1] T-SQL Level Up (https://learnfrom.brentozar.com/product/t-sql-level-up-18-months-access/)
[2] Doug Lane (http://www.brentozar.com/team/doug-lane/)
[3] Bren Ozar Unlimited (http://www.brentozar.com/)

Trabalhando com dados hierárquicos – Parte III

Saudações! No capítulo anterior desta série, falei de solicitações comuns relacionadas a dados hierárquicos utilizando o método iterativo. Hoje tratarei das mesmas solicitações através de CTEs recursivas.

CTEs recursivas têm uma estrutura um pouco diferente das outras CTEs. De acordo com o BOL [1], uma CTE recursiva é constituída por três elementos:

1. Chamada da rotina
A primeira chamada da rotina é quando o membro âncora é executado. Por membro âncora, entenda qualquer consulta que não faça referência à própria CTE.

2. Chamada recursiva da rotina
A chamada recursiva inclui uma ou mais consultas unidas pela cláusula UNION ALL que fazem referências à própria CTE. Estas consultas são conhecidas como membros recursivos.

3. Verificação de encerramento
Um processo implícito que ocorre quando a chamada recursiva não retorna mais registros.

Inicialmente esta ideia de recursividade pode parecer complexa, então partirei direto para as solicitações e, através dos exemplos, este conceito ficará mais claro.

SUBORDINADOS – MÉTODO RECURSIVO

Uma grande vantagem do método recursivo sobre o método iterativo é não existir a necessidade de materializar os dados previamente. Desta forma, seguiremos os seguintes passos para elaborar o script:

  1. Criar o membro âncora da CTE para que traga o nó raiz da sub-árvore;
  2. Criar o membro recursivo da CTE para que traga os subordinados da recursão anterior;
  3. Retornar a hierarquia;

Utilizando o exemplo inicial do método iterativo, para trazer as patentes subordinadas à patente de Comandante, adaptado para o método recursivo seria algo como:

USE tempdb;
GO
-- Iniciando CTE
;WITH Subordinados AS (
-- Membro Âncora
SELECT patente_id, 0 AS nivel
FROM dbo.Patentes
WHERE patente_id = 2 -- Comandante

UNION ALL
-- Membro Recursivo
SELECT Filho.patente_id, Pai.nivel + 1 AS nivel
FROM Subordinados AS Pai
JOIN dbo.Patentes AS Filho
ON Pai.patente_id = Filho.patente_superior_id
)
-- Fazendo o JOIN para trazer as patentes
SELECT P.patente_id, P.patente_descricao, S.nivel
FROM Subordinados AS S
JOIN dbo.Patentes AS P
ON S.patente_id = P.patente_id;

A primeira consulta no corpo da CTE – que define a raíz – é o membro âncora, após o UNION ALL vem a consulta recursiva. No membro recursivo, é feito um JOIN da própria CTE com a tabela base em busca dos subordinados. Cada recursão irá para o próximo nível de subordinados até que não existam mais subordinados.

fascinating_spock

De fato, Spock. A recursividade é uma ferramenta muito poderosa e, para mim, é uma das funcionalidades mais intrigantes (ou deveria dizer “fascinantes”?) das CTEs. O resultado do script anterior é exatamente o mesmo que o encontrado através do método iterativo.

recursivo_subordinado_resultado

Note que poderíamos utilizar diretamente a descrição da patente nos membros da CTE para que não fosse necessário realizar o último JOIN. No entanto, estou optando por esta forma para facilitar o entendimento no momento de encapsular o código. Então, aproveitando este código para encapsular a lógica de subordinados numa função com valor de tabela, terei algo como o script a seguir para retornar os tripulantes subordinados ao Sulu.

USE tempdb;
GO
IF OBJECT_ID('dbo.Subordinados') IS NOT NULL
DROP FUNCTION dbo.Subordinados;
GO
CREATE FUNCTION dbo.Subordinados
(@raiz AS INT) RETURNS TABLE
AS
RETURN
-- Iniciando CTE
WITH Subordinados AS (
-- Membro Âncora
SELECT tripulante_id, 0 AS nivel
FROM dbo.Tripulantes 
WHERE tripulante_id = @raiz

UNION ALL
-- Membro Recursivo
SELECT Filho.tripulante_id, Pai.nivel + 1 AS nivel
FROM Subordinados AS Pai
JOIN dbo.Tripulantes AS Filho
ON Pai.tripulante_id = Filho.superior_id
)
SELECT tripulante_id,nivel
FROM Subordinados;

Agora é simples obter os subordinados de um tripulante. Basta fazer o JOIN com a tabela base e voilà. A figura abaixo mostra o resultado para o Tenente-Comandante Sulu.

consulta_recursiva_resultado

SUPERIORES – MÉTODO RECURSIVO

Para encontrar os nós pais de um determinado nó até a raiz da árvore, irei apenas seguir o caminho contrário do método para encontrar os filhos (subordinados). Logo, ao invés de descer nos níveis hierárquicos, vou subir até a raiz. É importante notar que, diferentemente do método iterativo utilizado no capítulo anterior, neste método é possível ter mais de um nó pai. Isso não ocorrerá para esta árvore, mas vale o aviso.

Adaptando o código anterior para trazer os superiores em comando de um determinado tripulante, temos:

USE tempdb;
GO
IF OBJECT_ID('dbo.Superiores') IS NOT NULL
DROP FUNCTION dbo.Superiores;
GO
CREATE FUNCTION dbo.Superiores
(@tripulante_id AS INT) RETURNS TABLE
AS
RETURN
-- Iniciando CTE
WITH Superiores AS (
-- Membro Âncora
SELECT	tripulante_id, superior_id, 0 AS nivel
FROM dbo.Tripulantes
WHERE tripulante_id = @tripulante_id

UNION ALL
-- Membro Recursivo
SELECT Pai.tripulante_id, Pai.superior_id, Filho.nivel + 1
FROM Superiores AS Filho
JOIN dbo.Tripulantes AS Pai
ON Filho.superior_id = Pai.tripulante_id
)
SELECT tripulante_id, superior_id, nivel
FROM Superiores;

Neste código é necessário manter o código do tripulante superior (superior_id) para que cada recursão da CTE consiga subir um nível, a parte este detalhe, o código é similar ao código para subordinados.

Verificando a cadeia de comando para o Navegador Aprendiz Chekov, temos:

recursivo_funcao_superior_resultado

CAMINHO HIERÁRQUICO – MÉTODO RECURSIVO

Como mostrei no capítulo anterior, o caminho hierárquico registra os nós necessários para encontrar um determinado nó partindo da raiz. Geralmente é utilizado um separador da sua escolha para delimitar cada nó, então para o nó raiz o caminho seria “\” + raiz + “\” e para os próximos nós será “caminho do nó pai” + nó + “\”. Reutilizando a função de subordinados para trazer também o caminho hierárquico, temos:

USE tempdb;
GO
IF OBJECT_ID('dbo.SubordinadosCH') IS NOT NULL
DROP FUNCTION dbo.SubordinadosCH;
GO
CREATE FUNCTION dbo.SubordinadosCH
(@raiz AS INT) RETURNS TABLE
AS
RETURN
-- Iniciando CTE
WITH SubordinadosCH AS (
-- Membro Âncora
SELECT	tripulante_id, 0 AS nivel,
      CAST('\' + CAST(tripulante_id AS VARCHAR) + '\' AS VARCHAR(MAX)) AS caminho
FROM dbo.Tripulantes 
WHERE tripulante_id = @raiz

UNION ALL
-- Membro Recursivo
SELECT	Filho.tripulante_id, Pai.nivel + 1 AS nivel,
   CAST(Pai.caminho + CAST(Filho.tripulante_id AS VARCHAR) + '\' AS VARCHAR(MAX))
FROM SubordinadosCH AS Pai
JOIN dbo.Tripulantes AS Filho
ON Pai.tripulante_id = Filho.superior_id
)
SELECT tripulante_id,nivel,caminho
FROM SubordinadosCH;

Algo que vale lembrar nesta função é a utilização do CAST para garantir que o atributo “caminho” de todas as recursões tenha o mesmo tamanho e tipo de dados. Caso isso não aconteça, não é possível realizar o UNION ALL e o código quebra.

Olhando o caminho hierárquico para todos os subordinados do Capitão Kirk, incluindo ele, temos:

recursivo_funcao_caminho_resultado

Agora que já existe o caminho hierárquico e os níveis, utilizarei esta função para a última solicitação.

APRESENTAÇÃO GRÁFICA – MÉTODO RECURSIVO

Para a apresentação gráfica, utilizarei a mesma técnica apresentada no capítulo anterior. Usarei o REPLICATE para identificar os níveis na hierarquia e a ordenação pelo atributo “caminho”, criado no passo anterior. Desta forma, temos:

recursivo_funcao_caminho_apresentacao_resultado

Por questões gráficas, retornei os resultados como texto. O essencial desta técnica é replicar uma string pelo número de níveis de cada nó. Desta forma, a estrutura fica montada e ordenada como desejado.

Aqui encerro o post de hoje! Na próxima parte, irei materializar o atributo “caminho” para mostrar formas diretas e eficientes de consultar uma hierarquia. Espero que tenha gostado! Até mais!

REFERÊNCIAS

[1] Recursive Queries Using Common Table Expressions

Trabalhando com dados hierárquicos – Parte II

No capítulo anterior desta série, Trabalhando com dados hierárquicos – Parte I, descrevi o que são dados hierárquicos e defini algumas terminologias que utilizarei nesta e nas próximas partes. Apresentei o script para a criação das tabelas e ilustrei com maestria a estrutura hierárquica que servirá como base. Caso não tenha visto o post anterior, volte para a primeira parte antes de continuar.

Neste capítulo, falarei sobre o método mais intuitivo – para mim – de lidar com dados hierárquicos, o método iterativo.

MÉDOTO ITERATIVO

No método iterativo, utilizarei loops para varrer a estrutura hierárquica. Existem soluções que varrerão a árvore de nó em nó, mas essas geralmente são as escolhas mais custosas e lentas. Neste artigo utilizarei soluções iterativas que varrerão a árvore de nível em nível. Desta forma, serão feitas menos iterações nos laços de repetição.

jean_luc_cursor

Isso mesmo, Jean Luc. Esse comportamento iterativo é típico de cursores, mas veremos que esta solução pode ser uma saída flexível para nosso problema, e para situações onde nossa massa de dados é muito grande pode ser nossa única solução – veremos como resolver isso depois com a materialização da hierarquia.

Uma das maiores vantagens dos métodos iterativo e recursivo é que não precisamos alterar ou acrescentar nenhuma estrutura. Dependemos apenas dos dados para criar nossas soluções. Outra vantagem importante é não ter limite de níveis em nossa hierarquia. Podemos varrer quantos níveis quisermos.

SUBORDINADOS – MÉTODO ITERATIVO

Traduzindo a solicitação dos subordinados de um determinado nó através do método iterativo temos o seguinte algoritmo para montarmos nosso script:

  1. Criar uma tabela temporária para armazenar nossa hierarquia;
  2. Inserir nossa raiz no nível inicial (i.e. 0);
  3. Enquanto tivermos resultados, vamos inserir no próximo nível (i.e. 1) de nossa tabela temporária todos os nós cujos pais estejam entre os nós do nível anterior (i.e. 0);

Elaborando nosso script para trazer as patentes subordinadas à patente de Comandante, teríamos algo como o script abaixo.

USE tempdb;
GO
IF OBJECT_ID('tempdb..#Subordinados') IS NOT NULL
DROP TABLE #Subordinados;
GO
-- Criar a tabela temporária
CREATE TABLE #Subordinados (
	patente_id INT PRIMARY KEY,
	nivel INT NOT NULL,
	UNIQUE CLUSTERED(nivel,patente_id)
);
-- Declarar e iniciar a variável
DECLARE @nivel INT;
SET @nivel = 0;
-- Inserir a raiz
INSERT INTO #Subordinados(patente_id,nivel)
SELECT patente_id, @nivel
FROM dbo.Patentes
WHERE patente_id = 2 -- Comandante
-- Iniciar loop
WHILE(@@ROWCOUNT > 0)
BEGIN
	-- Avançar um nivel
	SET @nivel = @nivel + 1;
	-- Inserir subordinados (filhos)
	INSERT INTO #Subordinados(patente_id,nivel)
	SELECT Filho.patente_id, @nivel
	FROM #Subordinados AS Pai
	JOIN dbo.Patentes AS Filho
	ON Pai.patente_id = Filho.patente_superior_id
	WHERE Pai.nivel = @nivel - 1; -- Apenas nivel anterior
END
-- Retornar a hierarquia
SELECT patente_id, nivel
FROM #Subordinados;

Na criação da tabela temporária, acrescentei o índice único que, além de ajudar nos filtros da consulta, favorece a busca por nível na hierarquia. Esse tipo de índice também é chamado de breadth-first index. Depois, inicio a variável que controlará o nível da árvore e insiro o nó raiz que neste caso é a patente de Comandante. Então entro no loop, incremento o nível e insiro os filhos cujos pais pertençam ao nível anterior através de um JOIN da tabela temporária com a tabela base. Finalmente, retorno a hierarquia de subordinados à patente de Comandante. Para retornar não só a hierarquia, mas também a descrição de cada patente, basta realizar um JOIN da tabela resultante com a tabela base.

consulta_iterativa

Provavelmente, você irá reutilizar esse código ou terá que disponibilizar essa solução para outros. Para isso pode-se encapsular a lógica acima numa stored procedure ou numa função com valor de tabela (table-valued function). Como a estrutura que estou utilizando é pequena, optarei pela função, mas sugiro que valide o desempenho antes de descartar uma sp. Então, adaptando a solução anterior para retornar os tripulantes subordinados ao Tenente-Comandante Kelso resultaria numa função como a mostrada abaixo.

USE tempdb;
GO
IF OBJECT_ID('dbo.Subordinados') IS NOT NULL
DROP FUNCTION dbo.Subordinados;
GO
CREATE FUNCTION dbo.Subordinados
(@raiz AS INT) RETURNS @Subordinados TABLE
(
	tripulante_id INT PRIMARY KEY,
	nivel INT NOT NULL
	UNIQUE CLUSTERED(nivel, tripulante_id)
)
AS
BEGIN
-- Declarar e iniciar a variável
DECLARE @nivel INT;
SET @nivel = 0;
-- Inserir a raiz
INSERT INTO @Subordinados(tripulante_id, nivel)
SELECT tripulante_id, @nivel
FROM dbo.Tripulantes 
WHERE tripulante_id = @raiz;
-- Iniciar loop
WHILE(@@ROWCOUNT > 0)
BEGIN
	-- Avançar um nivel
	SET @nivel = @nivel + 1;
	-- Inserir subordinados (filhos)
	INSERT INTO @Subordinados(tripulante_id, nivel)
	SELECT Filho.tripulante_id, @nivel
	FROM @Subordinados AS Pai
	JOIN dbo.Tripulantes AS Filho
	ON Pai.tripulante_id = Filho.superior_id
	WHERE Pai.nivel = @nivel - 1; -- Apenas nivel anterior
END
-- Retornar a hierarquia
RETURN;
END
GO

Verificando a função com um JOIN com a tabela base da vez – dbo.Tripulantes – temos o seguinte resultado.

consulta_iterativa_funcao

SUPERIORES – MÉTODO ITERATIVO

Para obter os superiores, utilizarei a mesma lógica do processo de definir os subordinados, mas ao invés de descer níveis na hierarquia, vou subir. Como existe apenas um caminho do nó folha até o nó raiz, poderei utilizar mais este recurso como verificação. Logo, a função para obter a cadeia de comando do Oficial de Comunicações Farrell pareceria com o código abaixo.

USE tempdb;
GO
IF OBJECT_ID('dbo.Superiores') IS NOT NULL
DROP FUNCTION dbo.Superiores;
GO
CREATE FUNCTION dbo.Superiores
(@tripulante_id AS INT) RETURNS @Superiores TABLE
(
	tripulante_id INT PRIMARY KEY,
	nivel INT NOT NULL
)
AS
BEGIN
-- Declarar e iniciar a variável
DECLARE @nivel INT;
SET @nivel = 0;
-- Iniciar loop
WHILE(@tripulante_id IS NOT NULL)
BEGIN
	-- Inserir nó atual
	INSERT INTO @Superiores(tripulante_id, nivel) 
VALUES (@tripulante_id,@nivel);
	-- Avançar um nivel
	SET @nivel = @nivel + 1;
	-- Recuperar próximo gerente
	SET @tripulante_id = (SELECT superior_id
				  FROM dbo.Tripulantes
				  WHERE tripulante_id = @tripulante_id);

END
-- Retornar a hierarquia
RETURN;
END
GO

Para esta função, estou utilizando o fato de que existe apenas um superior direto para cada tripulante. Desta forma a codificação se torna bem mais simples. Ressalto que não estou validando o parâmetro de entrada, o que poderia gerar resultados inesperados ou erros. No entanto, como o objetivo aqui é mostrar uma ideia, deixo o tratamento de erros com você.

Verificando os resultados para esta função, temos o seguinte resultado:

iteracao_superior_consulta

CAMINHO HIERÁRQUICO – MÉTODO ITERATIVO

Para definir o caminho hierárquico é necessário varrer a hierarquia também. O início do caminho será o nó raiz da sub-árvore escolhida delimitado pelo separador. Então o caminho para a raiz será “\” + raiz+ “\” e para os próximos níveis será “caminho do pai” + nó + “\”. Desta forma, é possível reutilizar a função Subordinados e adaptá-la para trazer também o caminho hierárquico.

USE tempdb;
GO
IF OBJECT_ID('dbo.SubordinadosCH') IS NOT NULL
DROP FUNCTION dbo.SubordinadosCH;
GO
CREATE FUNCTION dbo.SubordinadosCH
(@raiz AS INT) RETURNS @Subordinados TABLE
(
	tripulante_id INT PRIMARY KEY,
	nivel INT NOT NULL,
	caminho VARCHAR(20)
	UNIQUE CLUSTERED(nivel, tripulante_id)
)
AS
BEGIN
-- Declarar e iniciar a variável
DECLARE @nivel INT;
SET @nivel = 0;
-- Inserir a raiz
INSERT INTO @Subordinados(tripulante_id, nivel, caminho)
SELECT	tripulante_id, @nivel, 
		'.' + CAST(tripulante_id AS VARCHAR) + '.'
FROM dbo.Tripulantes WHERE tripulante_id = @raiz;
-- Iniciar loop
WHILE(@@ROWCOUNT > 0)
BEGIN
	-- Avançar um nivel
	SET @nivel = @nivel + 1;
	-- Inserir subordinados (filhos)
	INSERT INTO @Subordinados(tripulante_id, nivel, caminho)
	SELECT	Filho.tripulante_id, @nivel, 
			Pai.caminho + CAST(Filho.tripulante_id AS VARCHAR) + '.'
	FROM @Subordinados AS Pai
	JOIN dbo.Tripulantes AS Filho
	ON Pai.tripulante_id = Filho.superior_id
	WHERE Pai.nivel = @nivel - 1; -- Apenas nivel anterior
END
-- Retornar a hierarquia
RETURN;
END
GO

Para definir o caminho, utilizei o atributo “tripulante_id”, mas você pode substituí-lo facilmente pelo nome do tripulante para seguir o caminho mais claramente. No entanto, fique alerta para o tamanho do atributo “caminho”, pois este pode crescer muito. Retornando o resultado da função para o Capitão Kirk, temos:

iterativo_caminho_consulta

Através da ordenação dos resultados pelo atributo “caminho”, fica visualmente fácil de enxergarmos a hierarquia. Utilizando este atributo novo, partiremos para a última solicitação.

APRESENTAÇÃO GRÁFICA – MÉTODO ITERATIVO

A apresentação gráfica de uma hierarquia seguirá a ordenação do novo atributo “caminho”, para organizar os resultados utiliza-se uma string – escolhida pelo usuário – repetida pelo número do nível do nó. Desta forma, temos o seguinte resultado:

iterativo_apresentacao_consulta

Como o caminho de cada nó filho é naturalmente maior que o do nó pai – por conter o nó pai –, a ordenação pelo “caminho” retorna perfeitamente a estrutura hierárquica. Utilizei o retorno dos resultados no formato texto para melhor ilustrar nossa apresentação.

Assim, encerro o método iterativo. Espero que tenha gostado! Na próxima parte irei demonstrar estas soluções utilizando CTEs recursivas. Até lá!

Tabelas Temporárias X Variáveis de Tabela

Venho compartilhar com vocês uma situação que geralmente é motivo de dúvidas. O que devo usar para materializar temporariamente meus dados: uma variável de tabela (i.e. @tabela) ou uma tabela temporária (i.e. #tabela)? Hoje mostrarei um caso onde a melhor escolha foi uma tabela temporária, explicitando as razões que me fizeram optar por ela ao invés de uma variável de tabela. Antes de começarmos, vamos falar um pouco sobre nossas duas opções.

TABELAS TEMPORÁRIAS

Tabelas temporárias tem o símbolo “#” como prefixo. As mais comumente utilizadas são as tabelas temporárias locais que possuem apenas um “#” (i.e. #tabela). No entanto, também é possível cria tabelas temporárias globais, utilizando dois “#” como prefixo (i.e. ##tabela). A diferença entre as duas está no escopo e na visibilidade. Enquanto a tabela local só pode ser acessada pelo criador e apenas dentro da sessão onde foi criada, a tabela temporária global pode ser acessada por qualquer sessão, ou seja, todos podem acessar e alterar seu conteúdo e até remover a tabela.

Tabelas temporárias são criadas e possuem uma representação física no tempdb, apesar de que quando são pequenas – e o SQL Server tem memória sobrando –, suas páginas permanecem na memória. Quando a memória livre é pouca, o SQL Server persiste a tabela em disco.

Em minha opinião, o fato mais importante para escolher entre tabelas temporárias e variáveis de tabela é o SQL Server criar e manter estatísticas de distribuição de dados para as tabelas temporárias da mesma forma como faz para tabelas permanentes. Isso é especialmente importante quando se indexa uma tabela temporária, pois a informação de distribuição permite que se obtenham planos de execução mais eficientes. Também como consequência deste fato, consultas nas tabelas temporárias serão recompiladas caso as estatísticas sejam atualizadas, atinjam o limiar de recompilação (recompilation treshold), etc. Isto não ocorre para variáveis de tabela – a não ser com a opção RECOMPILE –, pois nenhuma estatística é criada ou mantida.

VARIÁVEIS DE TABELA

Variáveis de tabelas geralmente causam confusões por serem pouco compreendidas. Um erro comum é acreditar que essas tabelas residirão apenas em memória, sem representação física. Outro erro comum é achar que se deve sempre optar por variáveis de tabela ao invés de tabelas temporárias.

Assim como tabelas temporárias, variáveis de tabela possuem representação física no tempdb e, quando pequenas – e o SQL Server tendo memória de sobra –, essas tabelas residirão em memória. Já o escopo e a visibilidade dessas tabelas são bem mais limitados. As variáveis de tabelas são apenas acessíveis dentro de um único lote (batch), como as demais variáveis.

Como mencionei antes, o SQL Server não cria ou mantém estatísticas para variáveis de tabela como faz para as tabelas temporárias. Por um lado, este fato pode ser especialmente problemático para tabelas grandes, onde o plano de execução pode se tornar impreciso. Por outro lado, isso também acarreta em menos – ou nenhuma – recompilações.

ENTÃO, QUAL OPÇÃO DEVO ESCOLHER?

Depois de entendermos melhor nossas opções, devemos levantar algumas questões:

• O SQL Server precisará de estatísticas de distribuição ou estimativas precisas de cardinalidade para gerar planos de execução eficientes? Se precisar, qual é o custo de utilizar planos ineficientes quando não tivermos estatísticas disponíveis?

• Qual é o custo das recompilações se utilizarmos tabelas temporárias?

No caso de estarmos trabalhando com tabelas grandes, o custo de utilizarmos planos ineficientes pode ser muito elevado. Já para tabelas pequenas, este custo geralmente é aceitável ou até mesmo desprezível. Como foi citado [1] no Books Online da Microsoft:

“Do not use table variables to store large amounts of data (more than 100 rows). Plan choices may not be optimal or stable when a table variable contains a large amount of data”.

Em outras palavras, não utilize variáveis de tabela para armazenar grandes quantidades de dados (mais de 100 linhas), pois os planos de execução podem não ser eficientes ou estáveis.

Recompilações são caras, então se tivermos uma tabela criada dentro de uma stored procedure, quanto mais vezes esta SP for executada, maior o seu custo. É claro que em algumas situações precisaremos que nossos dados sejam persistidos num determinado escopo em que apenas uma opção atenderá.

Vemos, então, que é necessário avaliar bem nossos requisitos e a massa de dados com a qual iremos trabalhar. Com isso trago o tuning realizado numa SP que utilizava uma variável de tabela e, quando o número de linhas aumentava muito, seu tempo de execução subia drasticamente.

ESTUDO DE CASO

Após a alteração de uma dada SP, observou-se que esta variava muito em tempo de execução. Este variava de poucos segundos até pouco mais de 2 horas e 15 minutos. Começamos a observá-la capturando os tempos de execução e o número de linhas ao longo de alguns dias. Verificamos que mais de 95% das execuções envolviam menos de 1.000 linhas e que dos 5% restantes o número de linhas variava de 1.000 até 100 mil linhas. O comportamento desta SP seguia o esperado até um determinado número de linhas – por volta de 40 mil – e após este ponto, seu tempo de execução aumentava seguindo uma tendência exponencial. Logo abaixo segue um gráfico de dispersão com as medições realizadas relacionando o tempo de execução da SP (duração) com o número de linhas.

Hmm... nice curve!

Hmm… nice curve!

Ao analisarmos o código, identificamos um JOIN utilizando uma variável de tabela que estava causando o tempo de execução inesperado quando o número de linhas era grande. Isso se dá por não existir estatísticas na variável de tabela, desta forma o SQL Server sempre estimava que esta tabela teria apenas um linha. Logo, utilizando nosso primeiro questionamento, ficou evidente que o custo de utilizar planos de execução ineficientes era astronômico. Isto já seria suficiente para optar por uma tabela temporária, mas também analisamos se o custo das recompilações impactaria nesta escolha. Esta SP era executada menos de 90 vezes por dia, um número pequeno que pouco seria influenciado pelas recompilações. Desta forma, alteramos a SP e substituímos a variável de tabela por uma tabela temporária.

No interesse de garantirmos o mesmo desempenho para os 95% dos casos (com poucas linhas), examinamos o antes e depois da alteração. Abaixo estão dois gráficos mostrando as medições realizadas para cada uma das soluções.

imagem02
imagem03
Com estas medições, comprovamos que para a maioria dos casos (95%), o desempenho permaneceu praticamente o mesmo, ou seja, menos de 40 segundos. Já para os casos atípicos (5%) com muitas linhas, o desempenho fui muito superior, como mostra o gráfico com as medições após as alterações.

imagem04
Aqui fica clara a diferença no comportamento da SP em relação ao número de linhas. Enquanto na versão anterior (utilizando variável de tabela) a SP tinha uma duração de aproximadamente 2 horas e 15 minutos para 75 mil linhas, após a alteração (utilizando tabela temporária) a SP possui um tempo de execução de aproximadamente 5 minutos para mais de 100 mil linhas!

CONCLUSÃO

Neste artigo aprendemos um pouco mais sobre variáveis de tabela e tabelas temporárias e também a escolher mais precisamente qual é a melhor opção para cada caso. Vimos, através de um caso real, o impacto que esta decisão pode ter caso seja mal tomada. Seu usuário agradece!

REFERÊNCIAS

[1] Books Online – table (Transact-SQL)
[2] Itzik Ben-gan – Inside Microsoft SQL Server 2008: Programming. Chapter 7: Temporary Tablesand Table Variables.

SQL Injection, como o DBA pode se proteger?

Depois da empolgante (e até um pouco aterrorizante) palestra “Invadindo o SQL Server? DBA Vs. Hacker” apresentada pelo Luan Moreno (blog|twitter) e pelo Lenon Leite (twitter) no XIX encontro do grupo SQLServerDF, me senti quase obrigado a falar sobre segurança. Logo, neste post irei falar sobre algumas dicas e formas para o DBA se prevenir de um possível ataque através de uma SQL Injection.

No entanto, antes de qualquer coisa, o que é SQL Injection? SQL Injection, ou Injeção de SQL, é uma técnica de inserção de código malicioso que explora vulnerabilidades na segurança da aplicação ou da base de dados. Geralmente esses ataques ocorrem quando as entradas de dados dos usuários não são devidamente tratadas pela aplicação ou quando a consulta é construída dinamicamente, mas é claro que os ataques não são limitados apenas a essas formas.

SQL dinâmico é um código ou comando que é executado dinamicamente (Duh?!). Em outras palavras, é quando se concatena linhas de código com entradas do usuário para montar um comando SQL “na hora”, ou, dinamicamente. Codificar utilizando o SQL dinâmico traz grande flexibilidade e, quando bem aplicado, pode gerar planos de execução mais eficientes. Mas, como nem tudo na vida são flores, a manutenção de códigos com SQL dinâmico é bem mais complicada e esse código, quando utilizado indevidamente, pode gerar brechas enormes na segurança do banco de dados e uma baixíssima taxa de reutilização de planos de execução. Sem prolongar mais esta introdução, vamos demonstrar como pode acontecer um ataque por SQL Injection.

Vamos supor que uma locadora de filmes empresa que oferece serviços de TV pela internet tem um sistema de busca de filmes no seu catálogo. Nesse sistema o usuário pode procurar o filme por título, diretor ou gênero. Agora, vamos supor que esta busca é feita por uma stored procedure como a abaixo.

CREATE PROCEDURE [dbo].[RetornaFilme](
	@Titulo  VARCHAR(MAX) = NULL,
	@Diretor VARCHAR(MAX) = NULL,
	@Genero  VARCHAR(MAX) = NULL)
AS

DECLARE @sql VARCHAR(MAX);

SET @sql =      'SELECT [Titulo],[Diretor],[Genero] '+
                'FROM [dbo].[Filmes] '+
                'WHERE (1=1) ';

IF(@Titulo IS NOT NULL)
SET @sql = @sql + 'AND [Titulo] LIKE '''+@Titulo+'''';

IF(@Diretor IS NOT NULL)
SET @sql = @sql + 'AND [Diretor] LIKE '''+@Diretor+'''';

IF(@Genero IS NOT NULL)
SET @sql = @sql + 'AND [Genero] LIKE '''+@Genero+'''';

EXEC(@sql)
GO

Esse tipo de consulta acaba sendo bem comum quando utilizamos filtros dinâmicos. Vale ressaltar que esta SP não segue boas práticas de programação. Em breve ficarão evidentes os motivos.

Então, supondo que estejamos atrás de filmes de aventura, basta executarmos nossa busca.

RetornaFilme_01

Tudo dentro do esperado! Agora que vemos que ela funciona, que tal vermos se aquele filme Rock ‘n’ Rolla está no catálogo?

RetornaFilme_02

Com esta mensagem de erro, evidenciamos uma brecha na segurança em nossa SP. “Mas… o que deu errado?” Um dos problemas aqui é o tratamento (ou falta de) da entrada do usuário. Se imprimirmos o comando que está sendo executado, temos o seguinte.

SELECT [Titulo],[Diretor],[Genero]
FROM [dbo].[Filmes]
WHERE (1=1) AND [Titulo] LIKE 'Rock 'n' Rolla';

Aqui fica claro que a letra “n” ficou fora da string. Isso já foi o suficiente para gerar um erro que demonstra que esta SP não trata nomes que contenham aspas simples. Através deste tipo brechas que hackers vão inserir trechos de código malicioso. Eis um exemplo de código malicioso para obter mais informação que o esperado.

RetornaFilme_03

“O que é esse result… Essas são as tabelas no banco e suas colunas?!” Exatamente. Uma relação de todas as tabelas criadas e suas colunas. Depois de algumas tentativas e um pouco de conhecimento em banco de dados – e pode ter certeza que hackers tem tanto tempo quanto o google à disposição deles – um usuário malicioso poderia entrar com esse parâmetro e voilà. Agora ele já sabe o que tem dentro da sua base de dados. Agora basta ajustar novamente o parâmetro e…

RetornaFilme_04

Neste momento o sorriso está estampado na cara do hacker, afinal de contas, agora ele tem todos os dados de todos os usuários cadastrados nesta empresa. Quanto será que isso deve valer para a empresa concorrente? Bastante, imagino. Mas vamos ver o que ocorreu com essa SP para que houvesse esta brecha. Imprimindo o parâmetro “@sql” para este último caso temos:

SELECT [Titulo],[Diretor],[Genero]
FROM [dbo].[Filmes] WHERE (1=1) AND [Titulo] LIKE 'a'
UNION
SELECT nome,email,conta_bancaria
FROM Usuario --'

Pelo fato da execução do código ser baseada na concatenação de strings, observa-se que o trecho de código malicioso fecha a busca por título – com a’ – e então utiliza o operador UNION para trazer informações adicionais que neste caso são bem sensíveis. Depois ele utiliza o comentário de linha “- -“ para anular qualquer código posterior.

Outro exemplo mais agressivo de ataque é quando o código malicioso tenta alterar ou apagar algo dentro da sua base ou até em seu servidor. O objetivo pode ser apagar o catálogo de filmes, os registros dos usuários ou só dificultar a sua vida como DBA.

EXEC [dbo].[RetornaFilme] @Titulo = 'a''; DROP TABLE Filmes; --';

Mas é claro que sempre é possível piorar. Então, aviso antes mesmo de colocar o comando abaixo: Não execute o código abaixo em lugar nenhum (Não, nem mesmo assim). A SP xp_cmdshell é capaz de executar comandos em shell como se estivesse no command prompt do Windows. Dependendo do nível de permissão do usuário que executa esta SP, ele pode fazer o que quiser com a máquina. Então, depois de avisar que você não deve brincar com essa SP, eis a possível injeção de SQL.

EXEC [dbo].[RetornaFilme]
@Titulo = 'a''; EXEC master.dbo.xp_cmdshell ''format d:''--';

Calma. A SP xp_cmdshell é desativada por padrão a partir do SQL Server 2005, mas se o invasor conseguir privilégios de sysadmin ele pode mudar isso (Aí sim, já era). No entanto, apesar do potencial destrutivo desta SP, é comum encontrá-la ativada para usos administrativos – o que também não é considerado uma boa prática de segurança.

Como vimos, a utilização de SQL dinâmico pode gerar uma brecha enorme na segurança e nosso objetivo neste artigo é saber como reduzir essa superfície de ataque ao máximo para evitarmos uma injeção de SQL. Então vejamos algumas medidas que podemos tomar!

1.Dimensione e determine as variáveis apenas de acordo com o necessário. Variáveis fortemente tipadas diminuem a liberdade do invasor ao inserir código malicioso. No nosso caso, o tipo VARCHAR(MAX) dá liberdade total para o ataque.

2.Valide toda e qualquer entrada do usuário. Aproveitando até os tipos bem definidos, utilize funções de verificação. Se o tipo deve ser numérico, use ISNUMERIC(). Se for data, ISDATE(), e assim por diante. Em caso de strings, utilize REPLACE() para substituir aspas simples por aspas duplas.

3.Utilize prints para debugar o código. Ao acrescentar uma forma de debugar o resultado da concatenação de strings, vemos com muito mais clareza possíveis brechas no código. Além disso, fica muito mais fácil realizar uma manutenção!

4.Dê o mínimo de permissão ao usuário que executar a SP. Aqui é mais um controle de danos. Pois, por mais que o invasor consiga entrar com algum código malicioso, ele não conseguirá fazer muito estrago.

Então se ajustarmos nossa consulta de acordo com essas premissas, teríamos algo como o código abaixo.

CREATE PROCEDURE [dbo].[RetornaFilme](
	@Titulo  VARCHAR(100) = NULL,
	@Diretor VARCHAR(50)  = NULL,
	@Genero  VARCHAR(20)  = NULL,
	@Debug   BIT          = 1)
AS

DECLARE @sql VARCHAR(MAX);

SET @sql =      'SELECT [Titulo],[Diretor],[Genero] '+
                'FROM [dbo].[Filmes] '+
                'WHERE (1=1) ';

IF(@Titulo IS NOT NULL)
SET @sql = @sql + 'AND [Titulo] LIKE '''+REPLACE(@Titulo,'''','''''')+'''';

IF(@Diretor IS NOT NULL)
SET @sql = @sql + 'AND [Diretor] LIKE '''+REPLACE(@Diretor,'''','''''')+'''';

IF(@Genero IS NOT NULL)
SET @sql = @sql + 'AND [Genero] LIKE '''+REPLACE(@Genero,'''','''''')+'''';

IF(@Debug = 1) PRINT @sql;
EXEC(@sql) AS USER = 'usuario_acesso_minimo';
GO

Desta forma diminuímos consideravelmente a superfície de ataque do invasor. No entanto, ainda estamos gerando uma baixíssima taxa de reutilização dos planos de execução. Afinal de contas, somente se os parâmetros usados forem idênticos o plano de execução poderá ser reutilizado. Então vamos para a última – e talvez mais importante – dica:

1.Sempre que possível, utilize sp_executesql ao invés de EXEC(). A stored procedure sp_executesql, por ser parametrizada, facilita a reutilização dos planos de execução e, como iremos ver, acaba blindando as entradas do usuário dentro das variáveis.

Reformulando nossa consulta para utilizar a sp_executesql, temos o código a seguir.

CREATE PROCEDURE [dbo].[RetornaFilme](
	@Titulo  VARCHAR(100) = NULL,
	@Diretor VARCHAR(50)  = NULL,
	@Genero  VARCHAR(20)  = NULL,
	@Debug   BIT          = 1)
AS

DECLARE @sql NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);

SET @sql =  'SELECT [Titulo],[Diretor],[Genero] '+
            'FROM [dbo].[Filmes] '+
            'WHERE (1=1) ';

SET @params = '@Titulo  VARCHAR(100) = NULL,'+
              '@Diretor VARCHAR(50)  = NULL,'+
              '@Genero  VARCHAR(20)  = NULL';

IF(@Titulo IS NOT NULL)
SET @sql = @sql + 'AND [Titulo] LIKE @Titulo ';

IF(@Diretor IS NOT NULL)
SET @sql = @sql + 'AND [Diretor] LIKE @Diretor ';

IF(@Genero IS NOT NULL)
SET @sql = @sql + 'AND [Genero] LIKE @Genero ';

IF(@Debug = 1) PRINT @sql;
EXEC sp_executesql @sql, @params, @Titulo, @Diretor, @Genero
GO

Pronto! Além de termos um código mais limpo e simples, nossos planos de execução serão reutilizados com muito mais frequência graças à parametrização que agora é possível!

Neste artigo, vimos como o DBA pode se proteger melhor de uma SQL Injection e digo “se proteger melhor” pois, é muito difícil – se não impossível – se proteger 100% contra esse tipo de ataque. No entanto, diminuímos a superfície de ataque de possíveis invasores e ainda parametrizamos nossa consulta para uma maior reutilização dos planos de execução. Mas, como esse tópico me deixou com mais vontade de ler e estudar sobre o assunto, deixo aqui alguns lugares para que você continue sua leitura.

1.Consultas parametrizadas, ISNULL e SQL dinâmica, um post do Gustavo Maia Aguiar que explora um pouco mais sobre implementações de consultas parametrizadas e seus efeitos nos planos de execução.

2.The Curse and Blessings of Dynamic SQL, um artigo excelente do Erland Sommarskog que menciona SQL Injection, boas práticas quando utilizar SQL dinâmico e casos comuns de quando não se utilizar SQL dinâmico.

3.Inside Microsoft SQL Server 2008: T-SQL Programming, livro de Itzik Ben-Gan e companhia que considero leitura obrigatória para qualquer DBA/AD/Desenvolvedor que trabalhe com T-SQL.