T-SQL: CROSS APPLY

Prosseguindo na série com as instruções novas no SQL Server 2005, vou mostrar neste post o CROSS APPLY. Em uma operação de JOIN (INNER, OUTER ou CROSS) o T-SQL não aceita subquery correlacionada em tabela derivada, veja os exemplos abaixo:

select A.col, b.col
from A
cross join (select B.col from B where B.val=A.val) b
-- Erro, A.val na subquery está fora de escopo!

A instrução acima deverá ser reescrita em uma das forma abaixo, utilizando subquery como tabela derivada:

select A.col, b.col
from A
cross join (select B.col,B.val from B) b
where B.val=A.val
OU
select A.col, b.col
from A join (select B.col,B.val from B) b
on B.val=A.val

O problema persiste quando tentamos utilizar uma Função Definida pelo Usuário (UDF), porque A.val como parâmetro da função está fora de escopo!

select A.*, B.col from A
cross join dbo.UDF(A.val) B

O novo CROSS APPLY do SQL Server 2005 resolve este problema, aceitando a instrução abaixo:

select A.*, B.col from A
CROSS APPLY dbo.UDF(A.val) B

Vamos criar duas tabelas e uma função para trabalharmos em um exemplo real:

use TempDB
go
create table Cliente(ClientePK int NULL,Nome varchar(30) NULL)
create table Vendas(VendasPK int NULL,ClienteFK int NULL,Valor decimal(9,2) NULL)
go
insert Cliente values (1,'Jose')
insert Cliente values (2,'Maria')
insert Cliente values (3,'Ana')

insert Vendas values (1,1,20.00)
insert Vendas values (2,1,40.00)
insert Vendas values (3,2,15.00)
insert Vendas values (3,2,36.00)
go

create function fnu_MaiorVenda(@ClienteID int)
returns table as return(
select ClienteFK,Max(Valor) MaiorValor from Vendas
where ClienteFK = @ClienteID group by ClienteFK)
go

A função fnu_MaiorVenda faz um GROUP BY na tabela Vendas, retornando o valor da maior venda de um cliente identificado pelo parâmetro de entrada @ClienteID. Ao tentar executar um JOIN entre a tabela Cliente e a função fnu_MaiorVenda, recebemos o erro 4104:

select c.Nome, v.MaiorValor
from Cliente c JOIN fnu_MaiorVenda(c.ClientePK) v
on c.ClientePK = v.ClienteFK
-- Msg 4104, Level 16, State 1, Line 1
-- The multi-part identifier "c.ClientePK" could not be bound.

O CROSS APPLY resolve o problema...

select c.Nome, v.MaiorValor
from Cliente c CROSS APPLY fnu_MaiorVenda(c.ClientePK) v

Até o próximo post.
Landry.

Trigger de Login

No último curso de Design de Segurança do SQL Server 2005 (curso 2790), um aluno com experiência em Oracle, perguntou se o SQL Server tinha Trigger de Login. Respondi que não tinha, porém poderíamos simular esta funcionalidade com Event Notification... o problema é que ele bloqueava o login em algumas situações, dependendo do usuário e da aplicação utilizada no login, o que seria impossível já que Event Notification é assíncrono! Uma rápida consulta na Internet mostrou uma nova funcionalidade incluída no Service Pack 2 do SQL Server 2005: Trigger de Login!

A Trigger de Login foi incluída para atender a uma certificação de segurança chamada Common Criteria (CC), resultante da união de três outras certificações: ITSEC (padrão Europeu), CTCPEC (padrão Canadense) e TCSEC (Departamento de Defesa Norte Americano). A certificação CC é reconhecida por mais de 24 países e possui 7 níveis para produtos de informática, de EAL1 a EAL7.

O SQL Server até SP1 foi classificado em EAL1, já com SP2 recebeu a classificação EAL4+ (o + indica atendimento parcial, já que a Microsoft irá melhorar o suporte em atualizações futuras). A Trigger de Login foi criada para atender os seguintes requisitos que constam no EAL4+:
Restringir a quantidade máxima de conexões concorrentes de um mesmo usuário.
Definir uma quantidade máxima default de conexões por usuário.
Negar a conexão com base no usuário, grupo, dia da semana, etc.

Dentro de uma Trigger de Login você pode utilizar a função EVENTDATA() para obter informações da conexão que originou o disparo da trigger, veja o documento XML gerado abaixo:



Outras fontes de informações que podem ser utilizadas dentro da trigger:
- sys.dm_exec_sessions – View dinâmica com informações das sessões abertas.
- sys.dm_exec_connections – View dinâmica com informações das conexões abertas.
- app_name() – nome da aplicação utilizada para realizar a conexão corrente
- CURRENT_USER – usuário de banco de dados da conexão corrente

Exemplo:
-- Retorna Informações da conexão corrente
select s.*,c.*
from sys.dm_exec_sessions s
join sys.dm_exec_connections c
on s.session_id = c.session_id
where s.session_id = @@spid

O exemplo abaixo cria uma Trigger de Login que restringe o acesso ao servidor a partir do Management Studio apenas para o Administrador, além de registrar em uma tabela de auditoria os logins com sucesso.

-- Tabela de Auditoria na MSDB
create table msdb.dbo.AutitLogin (
idPK int not null identity,
Data datetime null,
ProcID int null,
LoginID varchar(128) null,
NomeHost varchar(128) null,
App varchar(128) null,
SchemaAutenticacao varchar(128) null,
Protocolo varchar(128) null,
IPcliente varchar(30) null,
IPservidor varchar(30) null,
xmlConectInfo xml)
go

-- Trigger de Login
create trigger AuditLogin on all server
for logon
as
IF CURRENT_USER <> 'dbo' and
app_name() like 'Microsoft SQL Server Management Studio%'
rollback
else
-- Login sucesso
insert msdb.dbo.AutitLogin
select getdate(),@@spid,s.login_name,s.[host_name],
s.program_name,c.auth_scheme,c.net_transport,
c.client_net_address,c.local_net_address,eventdata()
from sys.dm_exec_sessions s join sys.dm_exec_connections c
on s.session_id = c.session_id
where s.session_id = @@spid
go

Se um usuário comum tentar abrir uma conexão no SQL Server a partir do Managemente Studio irá receber a mensagem de erro abaixo:



A mensagem de erro poderia ser melhor, sem expor o motivo da falha da conexão: “trigger execution”! Você pode votar no site Microsoft Connect para alterar a mensagem de erro no link abaixo:
https://connect.microsoft.com/SQLServer/feedback/Vote.aspx?FeedbackID=237008

Gostaria de agradecer a Diego Cerqueira (o aluno especializado em Oracle) por ter enriquecido a aula com sues questionamentos, dando origem a este post.

Até o próximo post,
Landry.

T-SQL: OUTPUT

Olá... neste post veremos outra novidade no Transact-SQL do SQL Server 2005, a cláusula OUTPUT. Esta cláusula pode ser acrescentada a uma instrução de atualização para retornar os dados que acabaram de ser atualizados. Vamos criar uma tabela no Banco de Dados TEMPDB para utilizar nos exemplos:

use tempdb
go
create table TesteOutput (ColPK int IDENTITY NOT NULL, Nome varchar(50), Tel varchar(20))

Repare que a tabela TesteOutput possui a propriedade IDENTITY (auto numeração) na primeira coluna (ColPK)! Uma necessidade comum é retornar o valor atribuído pelo SQL Server a coluna com propriedade IDENTITY durante um INSERT, sendo necessário executar um SELECT após o INSERT.

insert TesteOutput values ('Ana Lucia','1111-1111')
select SCOPE_IDENTITY()

Valor_ColPK
-------------------
1

O SQL Server 2005 pode simplificar a operação acima em um comando apenas, veja:

insert TesteOutput OUTPUT inserted.ColPK
values ('Maria Clara','2222-2222')

ColPK
-----------
2

Se você quiser pode até retornar todas as colunas da tabela:

insert TesteOutput OUTPUT inserted.*
values ('Ana Paula','3333-3333')

Veja no DELETE:

delete TesteOutput OUTPUT deleted.ColPK,deleted.Nome
where ColPK = 1

Agora um exemplo mais interessante.... você precisa manter uma tabela de auditoria registrando o usuário, operação e a data que ocorreu a atualização! No SQL Server 2000 existem duas opções: TRIGGER ou executar duas operações, veja no SQL 2005:

create table TesteHist (
Nome varchar(50),
Operacao varchar(10),
Data datetime,
Usuario varchar(256))
go

delete TesteOutput OUTPUT
deleted.Nome,'DETELE',getdate(),suser_sname()
into TesteHist
where ColPK = 2
go

select * from TesteHist



Até o próximo post.
Landry.

T-SQL: INTERSECT e EXCEPT

Quarta... terminei a consultoria mais cedo (as 16h), tenho duas horas até começar a próxima turma do curso Microsoft 2793 (Reporting Service) as 18h... Tempo suficiente para escrever mais um post e fazer um pequeno lanche (a turma da noite detona o coffee break)!

Uma situação muito comum para quem trabalha com banco de dados é comparar o conteúdo de duas tabelas, retornando as linhas em comum ou as linhas que existem em uma tabela e não existem na outra (minus da álgebra relacional). Até o SQL Server 2000 a solução para estes dois problemas era utilizar JOIN ou SUBQUERY, veja no exemplo abaixo:

-- Criando duas tabelas
create table Teste1 (Coluna1 varchar(20))
create table Teste2 (Coluna1 varchar(20))
go

insert Teste1 values('Rio de Janeiro')
insert Teste1 values('Sao Paulo')
insert Teste1 values('Salvador')
insert Teste1 values('Sao Luiz')

insert Teste2 values('Rio de Janeiro')
insert Teste2 values('Sao Paulo')
insert Teste2 values('Salvador')
insert Teste2 values('Brasilia')
go

-- Obtendo linhas em comum
select Coluna1 from Teste1 where exists
(select * from Teste2 where Teste2.Coluna1 = Teste1.Coluna1)

-- Obtendo as linhas que existem na tabela Teste1 e
-- não existem na tabela Teste2
select Coluna1 from Teste1 where not exists
(select * from Teste2 where Teste2.Coluna1 = Teste1.Coluna1)

No SQL Server 2005 ficou muito mais fácil utilizando os operadores INTERSECT e EXCEPT, veja abaixo:

-- Obtendo linhas em comum
select Coluna1 from Teste1
INTERSECT
select Coluna1 from Teste2

-- Obtendo as linhas que existem na tabela Teste1 e
-- não existem na tabela Teste2
select Coluna1 from Teste1
EXCEPT
select Coluna1 from Teste2

Até o próximo post.
Landry.

Novos tipos de dados do SQL 2008: HIERARCHYID (Parte 2)

Depois de um logo período sem publicar nada no Blog (fiquei enrolado no trabalho...), finalmente vou finalizar o post sobre o tipo de dados hierárquico. A primeira parte está no endereço: http://sqlserver-brasil.blogspot.com/2008/05/novos-tipos-de-dados-do-sql-2008_14.html.

No último post mostrei como implementar hierarquia no SQL Server 2005, veremos agora as novidades do SQL Server 2008. Vou utilizar a mesma hierarquia do post anterior, segue abaixo a figura:


O novo tipo de dados hierarchyid representa uma hierarquia, estando disponível uma série de métodos para sua manipulação: GetAncestor, GetDescendant, GetLevel, GetRoot, IsDescendant, Parse, Read, Reparent, ToString, Write. Vou utilizar alguns destes no exemplo abaixo.

Primeiro vamos criar a tabela abaixo contendo o novo tipo de dados hierarchyid:

CREATE TABLE FuncionarioHierarchies(
FuncionarioID int not null primary key,
Nome varchar(10) not null,
Cargo varchar(12) not null,
Hierarquia hierarchyid null)

Para incluir o ROOT da hierarquia (Presidente Jose) vamos utilizar o método GetRoot, que retorno o ROOT da hierarquia:

INSERT FuncionarioHierarchies VALUES
(1,'Jose','Presidente',hierarchyid::GetRoot())

SELECT * FROM FuncionarioHierarchies
where Hierarquia = hierarchyid::GetRoot()

Reparem que para consultar o ROOT, basta utilizar o mesmo método da inclusão!

Vamos incluir agora os Diretores Maria e Pedro, utilizando o método GetDescendant:

-- Inclui Maria
DECLARE @Pai hierarchyid
SELECT @Pai = hierarchyid::GetRoot() FROM FuncionarioHierarchies
INSERT FuncionarioHierarchies VALUES
(2,'Maria','Diretor',@Pai.GetDescendant(NULL,NULL))

-- Inclui Pedro
DECLARE @Pai hierarchyid
DECLARE @PrimeiroFilho hierarchyid
SELECT @Pai = hierarchyid::GetRoot() FROM FuncionarioHierarchies
SELECT @PrimeiroFilho =@Pai.GetDescendant(NULL,NULL)
INSERT FuncionarioHierarchies VALUES
(3,'Pedro','Diretor',@Pai.GetDescendant(@PrimeiroFilho,NULL))

A tabela abaixo mostra como utilizar os dois parâmetros do método pai.GetDescendant (filho1,filho2):



Para completar nosso organograma utilize o script abaixo:

DECLARE @Pai hierarchyid
DECLARE @PrimeiroFilho hierarchyid
SELECT @Pai = Hierarquia FROM FuncionarioHierarchies
where FuncionarioID = 2
INSERT FuncionarioHierarchies VALUES
(4,'Ana','Gerente',@Pai.GetDescendant(NULL,NULL))
SELECT @PrimeiroFilho =@Pai.GetDescendant(NULL,NULL)
INSERT FuncionarioHierarchies VALUES
(5,'Lucia','Gerente',@Pai.GetDescendant(@PrimeiroFilho,NULL))

SELECT @Pai = Hierarquia FROM FuncionarioHierarchies where FuncionarioID = 3
INSERT FuncionarioHierarchies VALUES
(6,'Ronaldo','Gerente',@Pai.GetDescendant(NULL,NULL))

SELECT @Pai = Hierarquia FROM FuncionarioHierarchies where FuncionarioID = 5
INSERT FuncionarioHierarchies VALUES
(7,'Marcio','Coordenador',@Pai.GetDescendant(NULL,NULL))
go

Agora vamos retornar a hierarquia utilizando o SELECT abaixo com o método GetAncestor:

SELECT FuncionarioID,Nome,Cargo,Hierarquia.GetLevel() as Nivel,
(Select FuncionarioID FROM FuncionarioHierarchies
Where Hierarquia = e.Hierarquia.GetAncestor(1)) As Chefe
FROM FuncionarioHierarchies e

Até o próximo post,
Landry.

Melhorando o Desempenho das Consultas com INCLUDE

Estava preparado para finalizar o artigo sobre o novo tipo de dado hierárquico do SQL Server 2008, quando ao tentar entrar no SQL Profiler recebi a mensagem indicando que o SQL 2008 CTP havia expirado (ainda não instalei o CTP de Fevereiro 2008 no notebook)! Como estou ministrando o curso 2784 (tuning de consultas), resolvi escrever sobre um recurso novo no SQL Server 2005, a cláusula INCLUDE do CREATE INDEX.

Vamos criar uma tabela a partir da VIEW vIndividualCustomer do banco de dados AdventureWorks para utilizar nos exemplos deste artigo.

use TEMPDB
go
select CustomerID,FirstName,MiddleName,Lastname,
Phone,EmailAddress,AddressLine1 as Address,'RJ' as Region,
dateadd(d,-CustomerID,getdate()) DataCadastro
into customer
from AdventureWorks.Sales.vIndividualCustomer

Quando criamos um índice nonclustered o SQL Server constrói uma Árvore B preenchendo todos os níveis (raiz, intermediário e folha) com as colunas que compõe e chave. Para atender a consulta abaixo, vamos criar um índice nonclustered com chave composta FirstName, LastName e Phone, evitando a necessidade de acessar a tabela:

create nonclustered index IXCustomer_FirstName on
Customer(FirstName,LastName,Phone)

Agora vamos executar a consulta abaixo, habilitando o plano de execução gráfico (menu query opção “Include actual execution plan”) e as estatísticas de I/O:

set statistics io on
go
select FirstName,LastName,Phone from Customer
where FirstName = 'David'
-- Table 'customer'. Scan count 1, logical reads 4

Veja no comentário do script que foram lidas quatro páginas para navegar no índice e retornar o resultado! A busca binária foi feita utilizando o filtro WHERE na coluna FirstName, já as colunas LastName e Phone serviram apenas para compor o resultado final. Para esta consulta, as colunas LastName e Phone não precisam estar no nível raiz e intermediário do índice, já que não foi feita pesquisa binária no índice com elas.

A partir do SQL Server 2005 você poderá reduzir I/O de consultas utilizando a cláusula INCLUDE, retirando as colunas que não serão utilizadas na busca binária do nível raiz e intermediário do índice, gerando um índice menor, com conseqüente melhora no desempenho. As colunas definidas na cláusula INCLUDE são acrescentadas ao nível folha do índice.

Vamos agora alterar o índice movendo as colunas LastName e Phone da chave para a cláusula INCLUDE:

create index IXCustomer_FirstName_LastName_Phone
on Customer(FirstName) INCLUDE (LastName,Phone)
with drop_existing

select FirstName,LastName,Phone from Customer
where FirstName = 'David'
-- Table 'customer'. Scan count 1, logical reads 2

Repare que o I/O da consulta foi reduzido pela metade!!!

Outra vantagem da cláusula INCLUDE é o limite de 1023 colunas, diferente da chave que contém um limite muito menor de 16 colunas ou 900 bytes! Alguns tipos de dados que não podem fazer parte da chave, como VARCHAR(MAX), mas podem compor o INCLUDE.

Ate o próximo post,
Landry.

Classificando as Transformações no Data Flow do Integration Service (SSIS)

Como não estou com o notebook que contém a VM de SQL Server 2008, não tenho como completar a série de artigos que fala sobre o tipo de dados hierárquico. Já que estou ministrando o curso 2795 sobre Design de ETL, vou escrever sobre o Integration Service (SSIS).

No DTS do SQL Server 2000 transformar dados significa escrever um VB Script executado linha a linha durante a importação, o que obviamente gerava sérios problemas de desempenho. Devido a esta limitação no DTS, para médios e grandes volumes de dados, se utilizava uma área intermediária chamada “Staging” dividindo o processo de importação em duas fases: Origem – Staging e Staging – Destino. Na primeira fase (Origem – Staging) carregavam-se os dados sem transformar, centralizando em uma única base intermediária no SQL Server destino. Na segunda fase (Staging – Destino) instruções T-SQL eram utilizadas para transformar os dados, obtendo melhor desempenho. Devido a esta prática muito comum no DTS, este não era classificado como uma ferramenta de ETL (Extract Transform Loan) e sim como ELT (Extract Loan Transform).

A área Data Flow representa a principal novidade no SSIS, onde construímos um fluxo de dados com objetivo de importar realizando transformações com desempenho, classificando o SSIS como uma real ferramenta de ETL. Mas para se obter o máximo de desempenho durante o Data Flow é fundamentar compreender alguns conceitos como buffers e a classificação das transformações.

No Data Flow Buffer é a coleção de colunas que flui de uma transformação para a próxima, sendo classificado como de entrada ou de saída:
- Source: contém apenas buffer de saída.
- Transformations: contém buffer de entrada e saída.
- Destination: contém apenas buffer de entrada.

As transformações podem ser classificadas de dois modos:

1) Síncrona X Assíncrona
Uma transformação é considerada Síncrona quando ela utiliza o mesmo buffer que a transformação anterior. Isto é possível porque o buffer de entrada e saída possui sempre a mesma quantidade de linhas. Já uma transformação Assíncrona possui buffer de entrada com quantidade de linhas diferente do buffer de saída, criando um novo buffer na sua saída.

Fica claro que transformações Síncronas proporcionam melhor desempenho e redução no consumo de recursos.

2) Sem Bloqueio, Semi Bloqueio e Com Bloqueio
Uma transformação Sem Bloqueio é aquela que não retém as linhas para executar o algoritmo, liberando a linha imediatamente. Como Exemplo a transformação “Derived Column” aplica o algoritmo linha a linha sem retenção.
Transformações de Semi Bloqueio retém parcialmente o fluxo devido a funcionalidade implementada pelo algoritmo. A transformação “Merge Join” é um exemplo de retenção parcial, pois implementa o algoritmo balance line para realizar o join de dois fluxos de dados.
Já as transformações Com Bloqueio retêm o fluxo todo, liberando as linhas no final da execução do algoritmo.

Segue abaixo tabela resumindo as principais características das transformações:



Um bom desempenho no Data Flow está diretamente relacionado ao uso de transformações Sem Bloqueio, além de evitar ao máximo as transformações de Semi Bloqueio e com Bloqueio. Segue abaixo a relação das tarefas:



Até o próximo post onde irei retomar a série dos novos tipos de dados.
Landry.