SQL Server 2008 SSIS – Change Data Capture (CDC) Parte 2

CDC e Integration Service (SSIS)
No ultimo post escrevi sobre uma nova funcionalidade do SQL Server 2008 chamada Change Data Capture (CDC), onde é criada uma infra-instrutora para registrar as alterações que ocorrem em uma tabela, ficando simples identificar as alterações nos dados durante a carga com o Integration Service (SSIS).

Para maiores detalhes veja a primeira parte em http://sqlserver-brasil.blogspot.com/2008/11/sql-server-2008-ssis-change-data.html.

Neste post vamos ver como utilizar o CDC dentro do SSIS, identificando facilmente as alterações nos dados durante a carga.

No post anterior mostrei o conteúdo da tabela cdc.dbo_Cliente_CT que contém o registro das operações que ocorreram na tabela Cliente, veja na imagem abaixo:

Select * from cdc.dbo_Cliente_CT

As linhas identificadas no resultado acima representam:
Azul INSERT (_$operation = 2)
Vermelho UPDATE antes da alteração (_$operation = 3)
Vermelho UPDATE após a alteração (_$operation = 4)
Verde DELETE (_$operation = 1)

O primeiro problema que temos é identificar as alterações que ocorreram em um determinado período (entre duas cargas de dados), porque na tabela acima não existe uma coluna com data e hora! A chave para resolver este problema está na coluna _$start_lsn, que contém uma numeração em Hexa seqüencial (LSN – Log Sequence Number). A função fn_cdc_map_time_to_lsn converte uma data e hora no LSN mais próximo! O script abaixo identifica o intervalo de LSN a ser utilizado na consulta na tabela acima:

DECLARE @Data_INI datetime, @Data_FIM datetime,
@lsn_INI binary(10), @lsn_FIM binary(10);

SET @Data_INI = GETDATE() -1
SET @Data_FIM = GETDATE();

SET @lsn_INI = sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal', @Data_INI);

SET @lsn_FIM = sys.fn_cdc_map_time_to_lsn
('largest less than or equal', @Data_FIM);

O próximo problema é identificar as operações que devem ser realizadas durante a carga, utilizando as entradas na tabela cdc.dbo_Cliente_CT, dentro do período especificado acima. No exemplo da figura acima temos seis linhas, onde as três primeiras são inclusões, a quarta e quinta uma atualização e a última exclusão.

1) A cliente Maria foi incluída e depois não sofreu alteração, por isso esta deverá ser inserida no destino.

2) O cliente Jose foi incluído e depois alterado, sendo assim este cliente deverá ser inserido no destino com o telefone 2211-1111, que foi modificado na alteração registrada nas linhas quatro e cinco.

3) O cliente Antonio foi incluído e depois excluído (linhas 3 e 6 respectivamente), por isso este não deve fazer parte da carga.

Para facilitar o SQL Server 2008 disponibiliza a função cdc.fn_cdc_get_net_changes_dbo_cliente (em azul a porção variável do nome) que realiza a análise acima em um intervalo de LSN e retorna apenas as operações que devem ser feitas no destino. No exemplo acima o retorno da função seria: inclusão da Maria e Jose com o telefone novo! Na Stored Procedure abaixo temos o procedimento completo, ficando fácil utilizar este recurso no Integration Service:

CREATE PROC dbo.cdc_Cliente
@Data_INI datetime, @Data_FIM datetime
as
SET @Data_INI = GETDATE() -1
SET @Data_FIM = GETDATE();
DECLARE @lsn_INI binary(10), @lsn_FIM binary(10);

SET @lsn_INI = sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal', @Data_INI);

SET @lsn_FIM = sys.fn_cdc_map_time_to_lsn
('largest less than or equal', @Data_FIM);

SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_cliente
(@lsn_INI, @lsn_FIM, 'all');
go

No próximo post veremos um exemplo de pacote SSIS utilizando os recursos deste post, até lá.
Landry.

Palestras SQL Server Landry

Olá,

Segue abaixo as minhas palestras para download (PPTs e Demonstrações):

- SQL Server 2005

Atualizando para Microsoft SQL Server 2005
Local: Instituto Infnet (Rio de Janeiro)
Data: 04/06/2007
Link: http://www.4shared.com/file/70459690/a009ce7b/SQL2005_Upgrade.html

Microsoft SQL Express 2005
Local: Intituto Infnet (Rio de Janeiro)
Data: 06/06/2007
Link: http://www.4shared.com/file/70460708/447aa26d/SQL2005_Express.html


- SQL Server 2008

Novidades no Integration Service (SSIS) do SQL Server 2008
Local: Allen
Data: 08/11/2008
Link: http://www.4shared.com/file/70458232/b5d8864/SQL2008_NovidadesSSIS.html


Espero que gostem!
Landry

SQL Server 2008 SSIS – Change Data Capture (CDC) Parte 1

SQL Server 2008 SSIS – Change Data Capture (CDC) Parte 1

Estou no final da elaboração da minha palestra no SQL Server 2008 Community Lauch aqui no Rio de Janeiro, faltam apenas dois assuntos CDC e Script... Neste post vou escrever sobre o Change Data Capture (CDC).

O CDC tem como objetivo registrar todas as alterações que ocorrem em uma tabela facilitando o processo de exportação. Primeiro vamos aprender a habilitar e configurar esta funcionalidade, depois como fazer uso deste log no SSIS.

Habilitando e Configurando o CDC
Vamos criar um banco de dados para utilizar neste post como exemplo, execute o script abaixo:
USE master
go
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'TesteCDC')
DROP DATABASE TesteCDC
go
CREATE DATABASE TesteCDC
go

Para habilitar o CDC no banco criado acima utilizamos a Stored Procedure sp_cdc_enable_db:
USE TesteDCD
go
EXEC sys.sp_cdc_enable_db

Podemos verificar se um banco de dados está habilitado para o CDC com a query abaixo:
SELECT [name] as Banco, is_cdc_enabled FROM sys.databases

O SQL Server 2008 cria alguns objetos quando um banco é habilitado para o CDC:
- Usuário: cdc
- Schema: cdc
- Tabelas de Sistema: capture_columns, change_tables, ddl_history, índex_columns, lsn_time_mapping e dbo.systranschemas.

Agora vamos criar uma tabela para ver o CDC em ação, execute o script abaixo:
USE TesteCDC
go
CREATE TABLE Cliente (
ClienteID int not null primary key,
Nome varchar(30) not null,
Telefone varchar(30) null)
go

Para habilitar o CDC na tabela criada acima basta utilizar a Stored Procedure sp_cdc_enable_table, veja o exemplo abaixo:
EXEC sp_cdc_enable_table 'dbo', 'Cliente',
@role_name = NULL, @supports_net_changes =1

A Stored Procedure pc_cdc_enable_table cria alguns objetos no SQL Server 2008: uma tabela de sistema (cdc.dbo_Cliente_CT) e dois Jobs do SQL Server Agent (cdc.TesteCDC_capture, cdc.TesteCDC_cleanup).

Para obter a lista de tabelas habilitadas para CDC execute a consulta abaixo:
Select [name], is_tracked_by_cdc from sys.tables

Agora vamos testar incluindo, excluindo e alterando linhas na tabela Cliente:
-- Insert
insert dbo.Cliente values (1,'Jose','2236-1111')
insert dbo.Cliente values (2,'Maria','2236-2222')
insert dbo.Cliente values (3,'Antonio','2236-3333')
go

-- Update
update dbo.Cliente set Telefone = '2211-1111'
where ClienteID = 1
go

-- Delete
delete dbo.Cliente where ClienteID = 3
go

Na figura abaixo temos o resultado da consulta ao log de operações gerado na tabela cdc.dbo_Cliente_CT:
Select * from cdc.dbo_Cliente_CT



As linhas identificadas no resultado acima representam:
Azul - INSERT (_$operation = 2)
Vermelho - UPDATE antes da alteração (_$operation = 3)
Vermelho - UPDATE após a alteração (_$operation = 4)
Verde - DELETE (_$operation = 1)

No próximo post vamos ver como utilizar este recurso no SSIS, até lá!
Landry.

SQL Server 2008 SSIS – Data Profile

O SQL Server 2008 trás uma nova tarefa no Control Flow para analisar os dados durante a carga. O Data Profile pode fazer até 8 análises nos dados gerando um relatório em XML, que pode ser visualizado em um novo aplicativo chamado Data Profile Viewer.

As 8 análises disponibilizadas pelo Data Profile podem ser divididas em dois grupos: analise do relacionamento entre colunas e analise individua de colunas.

Análise do relacionamento entre colunas:
Candidate Key – analisa se uma ou mais colunas podem ser utilizadas como chave, ou analisa se existem valores duplicados onde se espera unicidade.

Functional Dependency – analisa a dependência funcional entre colunas, por exemplo ocorrência de cidades em estados.

Value Inclusion – verifica relacionamento tipo Foreign Key entra colunas de tabelas diferentes, exemplo de ocorrência de código de cliente nas tabelas de vendas e cliente.

Análise individual das colunas:
Column Length Distribution – relação dos tamanhos distintos de string em uma coluna e o percentual de linhas de cada tamanho. Útil para identificar possíveis erros nos dados.

Column Null Ratio – retorna o percentual de ocorrências de NULL em uma coluna.

Column Pattern – retorna um conjunto de expressões regulares identificadas e o percentual de linhas de cada ocorrência.

Column Statistics – só aplicável a colunas numéricas, mostra: Min, Max, Média e Desvio Padrão.

Column Value Distribution – retorna todos os valores distintos de uma coluna e o percentual de linhas de cada ocorrência.

Tipos de dados indicados:


Você poderá combinar várias análises em uma única tarefa Data Profile, veja na imagem abaixo:

O arquivo XML gerado após a análise poderá ser visualizado com o utilitário Data Profile Viewer localizado no grupo de programas do SQL Server 2008 no submenu Integration Services. O exemplo abaixo mostra uma análise de distribuição de valores (Column Value Distribution Profile):

Até o próximo Post,
Landry.

SQL Server 2008 Community Launch

Lançamento do SQL Server 2008 no Brasil

O SQL Server Community Launch irá ocorrer simultaneamente em vários locais no Brasil durante o mês de Novembro de 2008, veja no link abaixo:

http://www.bufaloinfo.com.br/sqllaunch/mapa.htm

Estaremos confirmando o evento na Allen no sábado dia 8 de Novembro ou no seguinte, aguardem próximo post.

Landry

SQL Server 2008 SSIS - Pipeline Scalability

No post Escolha das Transformacoes SSIS (http://sqlserver-brasil.blogspot.com/2008/06/classificando-as-transformaes-no-data.html) escrevi sobre a classificação das transformações em 3 grandes grupos: com bloqueio, sem bloqueio e semi bloqueio.

As transformações sem bloqueio consecutivas em um Data Flow formam uma única árvore de execução, utilizando o mesmo worker thread. Toda vez que em um Data Flow temos uma transformação com bloqueio ou semi bloqueio, é iniciada uma nova árvore de execução, utilizando um novo worker thread. Veja no Data Flow abaixo a divisão em árvores de execução:


Esta divisão em mais de um worker thread proporciona a execução em paralelo em máquinas com mais de um processador, melhorando o desempenho da carga.

Uma técnica utilizada no SQL Server 2005 era acrescentar ao Data Flow uma transformação semi bloqueio (exemplo UNION All) para forçar o uso de um novo worker thread, e melhorar o paralelismo.

No SQL Server 2008 o paralelismo é automático, não sendo mais necessário utilizar do artifício acima para melhorar o desempenho. Para controlar o uso de worker threads basta ajustar a propriedade do Data Flow EngineThreads,

Por outro lado isto nos força a rever os pacotes desenvolvidos no SQL Server 2005 utilizando o artifício descrito acima!

Até o próximo post,
Landry.

Novidades do SQL Server 2008 para Business Intelligence

Estou iniciando uma série de posts com as novidades do SQL Server 2008 para Business Intelligence, pois estarei participando do evento SQL Launch no Rio de Janeiro com duas palestras sobre SSIS e SSAS. Segue abaixo a relação das novas funcionalidades e melhorias que farão parte dos próximos posts:

SSIS
SSIS pipeline.
SSIS persistent lookups.
SSIS data profiling.
CDC (Change Data Capture).

SSRS
Integração com SharePoint nativo.
Novo Data Region Tablix.
Novos formatos de Rendering: rich-text, Word.
Melhorias no formato de Rendering Excel.
Entrega dos relatórios diretamente para o SharePoint.

SSAS
Personalization Extensions .
Best Practice Alerts.
Dynamic Named Sets.
Enhanced Dimension Design.
Enhanced Aggregate Design.
Novos algoritmos para Microsoft Time Series.
Subspace Computation.
MOLAP Write Back.
Scale-Out.
Backup.

Até o próximo post,
Landry

Resource Database e Tabelas de Sistema

Olá... depois de quase um mês sem fazer um post voltei... estava estudando para as provas Beta de SQL Server 2008. Estou aproveitando um intervalo de duas semanas entre as provas para escrever este post, espero que gostem!

No SQL Server 2000 era comum o acesso direto as tabelas de sistema para obter informações de metadata e até em situações extremas atualizá-las (SP_configure ‘allow updates’,1). No SQL Server 2005 todas as tabelas de sistema estão escondidas, todo o acesso ao metadata fica restrito as Stored Procedures, funções e Visões de sistema! Veja no link abaixo o diagrama das visões de sistema que podemos utilizar para acessar metadata:

SQL Server 2005 System Views Map
http://www.microsoft.com/downloads/details.aspx?FamilyID=2ec9e842-40be-4321-9b56-92fd3860fb32&displaylang=en

Para acessar as tabelas de sistema temos que abrir uma conexão DAC (Dedicated Administrator Connection), colocando ADMIN: na frente do nome da instância na conexão da janela de query no SSMS. A relação das tabelas de sistema pode ser encontrada no BOL pesquisando por “System Base Tables”.

O SQL Server 2005 introduziu um novo Banco de Dados de Sistema chamado mssqlsystemresource. Este banco contém os objetos de sistema que aparecem no schema SYS nos demais bancos de dados: Views, Stored Procedures e Funções de sistema (não contém tabelas de sistema).

O Banco de Dados mssqlsystemresource fica escondido, não aparece na lista de bancos no SSMS, porém se você navegar até a pasta abaixo verá os dois arquivos deste banco: mssqlsystemresource.mdf e mssqlsystemresource.ldf.
\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Temos algumas opções para os curiosos...

1) Iniciar a Instância em modo monousuário:
- Basta parar o serviço e iniciar utilizando o comando abaixo no prompt de comando:
cd c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
sqlservr.exe –c -m

Depois é só conectar no Banco e explorar seu conteúdo, veja na figura abaixo:

2) Utilizar os arquivos de dados e log do banco mssqlsystemresource para fazer Attach com outro nome de Banco:
- Copiar os arquivos de dados e log para outra pasta (não precisa parar o serviço).
- Executar o script abaixo (não se esqueça de alterar o caminho dos arquivos de dados e log!):

USE master
go
CREATE DATABASE mssqlsystemresource_Teste ON
( FILENAME = 'C:\mssqlsystemresource.mdf' ),
( FILENAME = 'C:\mssqlsystemresource.ldf' )
FOR ATTACH
go
if not exists (select name from master.sys.databases sd where name = N'mssqlsystemresource_Teste' AND
SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC mssqlsystemresource_Teste.dbo.sp_changedbowner @loginame=N'sa', @map=false
go

Mssqlsystemresource facilita a execução dos Service Packs
Em versões anteriores do SQL Server, os Service Packs tinham que executar vários scripts que alteravam os objetos de sistema em todos os banncos de dados. Dependendo do tamanho do servidor, estes scripts demoravam para rodar, aumentando o período de indisponibilidade do servidor. Centralizando o metadata no mssqlsystemresource, fica muito mais fácil para um Service Pack realizar as alterações necessárias, basta substituir os arquivos de dados e log do banco, reduzindo o tempo total de execução!

Backup do Banco de Dados Mssqlsystemresource
Como todos os objetos de sistema ficam agora no banco de dados mssqlsystemresource, ocorrendo qualquer problema nos seus arquivos o serviço do SQL Server não irá reiniciar. Veja a mensagem de erro no Event Viewer abaixo:

Sendo assim, é indicado o Backup do Banco de Dados mssqlsystemresource, juntamente com master e msdb. O problema é que o SQL Server não permite Backup no banco mssqlsystemresource, contudo é o único banco que permite a cópia dos seus arquivos de dados e log com o serviço ativo. O Restore também pode ser feito com uma cópia, porém tem que parar o serviço do SQL Server.

Existem duas instruções que retornam informações do banco mssqlsystemresource o Build e data de alteração respectivamente:
SELECT SERVERPROPERTY('ResourceVersion')
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

Até o próximo post,
Landry.

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.

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

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

Minha esposa acabou de entrar no Teatro com meus dois filhos (Luaninha e Erick) e fiquei no restaurante para trabalhar em um projeto que está começando a atrasar.... é um projeto de BI e a fase atual de documentação do ETL é a mais chata!!! Após a primeira garrafa de Bohemia (muiiiito gelada!), resolvi escrever um post, só para relaxar.

Vou escrever sobre o novo tipo de dados hierárquico em dois posts, no primeiro veremos como criar e manipular hierarquias no SQL Server 2005, no próximo veremos as novidades no SQL Server 2008, OK?

Hierarquia no SQL Server 2005
Vamos utilizar como exemplo a hierarquia da figura abaixo:



A abordagem tradicional para lidar com hierarquia utiliza auto-relacionamento, veja o script abaixo que cria no SQL Server 2005 a hierarquia da figura acima:

USE tempdb
go

CREATE TABLE Funcionario(
FuncionarioID int not null primary key,
Nome varchar(40) not null,
Cargo varchar(20) not null,
ChefeID int null)
go

INSERT Funcionario VALUES (1,'Jose','Presidente',null)
INSERT Funcionario VALUES (2,'Maria','Diretor',1)
INSERT Funcionario VALUES (3,'Pedro','Diretor',1)
INSERT Funcionario VALUES (4,'Ana','Gerente',2)
INSERT Funcionario VALUES (5,'Lucia','Gerente',2)
INSERT Funcionario VALUES (6,'Ronaldo','Gerente',3)
INSERT Funcionario VALUES (7,'Marcio','Coordenador',5)
go



Algumas operações comuns em hierarquia podem ser bem complexas no SQL Server 2005, como retornar todos os subordinados de um funcionário. Na consulta abaixo vamos retornar todos os subordinados da Diretora Maria, utilizando CTE com recursividade:

WITH Organograma (FuncionarioID, Nome, Cargo, ChefeID)
AS (
-- Cria a ancora para estabelecer ponto inicial
SELECT FuncionarioID, Nome, Cargo, ChefeID FROM Funcionario
WHERE Nome = 'Maria'
UNION ALL
-- Cria recursividade
SELECT f.FuncionarioID, f.Nome, f.Cargo, f.ChefeID
FROM Funcionario f JOIN Organograma o
ON f.ChefeID = o.FuncionarioID)

SELECT * FROM Organograma
go



Para incluir um novo funcionário Gerente no lugar de Ana, deslocando-a para Coordenadora, será necessário fazer vários UPDATEs nos seus subordinados.

No próximo post veremos como ficou mais fácil manipular hierarquias no SQL Server 2008, Até lá.
Landry.

Novos tipos de dados do SQL 2008: DATETIMEOFFSET

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

Nos dois últimos posts (http://sqlserver-brasil.blogspot.com/2008/04/novos-tipos-de-dados-do-sql-2008-date-e.html e http://sqlserver-brasil.blogspot.com/2008/04/novos-tipos-de-dados-do-sql-2008.html), escrevi sobre três tipos de dados novos para Data e Hora (DATE, TIME e DATETIME2). Neste post irei completar a série destinada aos novos tipos de dado Date e Time, escrevendo sobre suporte a Time Zone com o novo DATETIMEOFFSET.

DATETIMEOFFSET
Este tipo de dados é similar ao DATETIME2 onde é informada a precisão da hora, com acréscimo do Time Zone no intervalo de -14:00 a +14:00. Veja o exemplo abaixo:

DECLARE @dt DATETIMEOFFSET(0)
SET @dt = '20080415 22:00:00 -3:00' -- Brasilia

DECLARE @dt1 DATETIMEOFFSET(0)
SET @dt1 = '20080415 22:00:00 +9:00' -- Tokio

SELECT DATEDIFF(hh,@dt,@Dt1) 'Diferença Fuso Brasilia e Tokio'

-- Resultado abaixo:

Diferença Fuso Brasilia e Tokio
-------------------------------------------
-12

Vamos comparar agora todos os novos tipos de dados Data e Hora com os antigos DATETIME e SAMLLDATETIME:

SELECT CAST('20080120 20:30:05.1234567 +5:0' as DATE)
-- Resultado DATE: 2008-01-20

SELECT CAST('20080120 20:30:05.1234567 +5:0' as TIME(7))
-- Resultado TIME: 20:30:05.1234567

SELECT CAST('20080120 20:30:05.123' as SMALLDATETIME)
-- Resultado SMALLDATETIME: 2008-01-20 20:30:00.000

SELECT CAST('20080120 20:30:05.123' as DATETIME)
-- Resultado DATETIME: 2008-01-20 20:30:05.123

SELECT CAST('20080120 20:30:05.1234567 +5:0' as DATETIME2(7))
-- Resultado DATETIME2: 2008-01-20 20:30:05.1234567

SELECT CAST('20080120 20:30:05.1234567 +5:0' as DATETIMEOFFSET(7))
-- Resultado DATETIMEOFFSET: 2008-01-20 20:30:05.1234567 +05:00

No próximo post vou mostrar o tipo de dado hierarquico, Até lá.
Landry.

Novos tipos de dados do SQL 2008: DATETIME2

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

No último post, o primeiro da série que irá tratar dos novos tipos de dados no SQL Server 2008, falei sobre a tão esperada separação da data e hora nos novos tipos de dados DATA e TIME (http://sqlserver-brasil.blogspot.com/2008/04/novos-tipos-de-dados-do-sql-2008-date-e.html). Neste post vou apresentar uma extensão do tipo de dado DATETIME: DATETIME2.

DATETIME2
O tipo de dado DATETIME2 proporciona um melhor controle na precisão da HORA, além de aumentar o intervalo de datas aceito. Veja nas tabelas a seguir a comparação entre DATETIME2 e DATETIME.

Como exemplo execute o script abaixo:

DECLARE @DataHora3 DATETIME2(3)
SET @DataHora3 = '2004-02-27 16:14:00.1234567'
SELECT [DATETIME2(3)] = @DataHora3

DECLARE @DataHora7 DATETIME2(7)
SET @DataHora7 = '2004-02-27 16:14:00.1234567'
SELECT [DATETIME2(7)] = @DataHora7

-- Resultado abaixo:

DATETIME2(3)
-----------------------
2004-02-27 16:14:00.123

DATETIME2(7)
---------------------------
2004-02-27 16:14:00.1234567

No próximo post vou mostrar o tipo de dado TATETIMEOFFSET.
Até lá,

Landry.

Novos tipos de dados do SQL 2008: Date e Time

Até a versão 2005 o SQL Server disponibilizava apenas dois tipos de dados para data e hora (DATETIME e SMALLDATETIME), armazenando a data sempre junto da hora. Armazenar a data e hora em conjunto gera algumas dificuldades quando a necessidade da aplicação é de apenas manipular a data. Vamos explorar algumas situações que geram dificuldade ao manipular a data junto da hora e depois veremos as novidades no SQL Server 2008.

O script abaixo criar uma tabela de Vendas contendo uma coluna DATETIME e depois inclui algumas linhas.

USE tempdb
go

CREATE TABLE Vendas (
IDVenda int not null,
DataVenda datetime not null,
Cliente char(4000) not null,
TotalVenda decimal(10,2))

-- Inclui duas linhas para utilizar no filtro WHERE das consultas
INSERT Vendas VALUES
(3,'20070303 12:00:00.000','Ana',320.00)

INSERT Vendas VALUES
(3,'20070303 00:00:00.000','Pedro',120.00)

-- Inclui 10.000 linhas
DECLARE @i int
SET @i = 1
WHILE @i <= 10000 BEGIN

INSERT Vendas VALUES
(@i,getdate()-@i,'Cliente ' + ltrim(str(@i)),10.00 + @i)

SET @i = @i + 1
END
go

CREATE INDEX ix_Vendas_DataVenda
ON Vendas(DataVenda)
go

Para retornar todas as vendas de um determinado dia, não importando a hora, você terá certa dificuldade. Utilizando uma comparação simples de data, o SQL Server retornará apenas as vendas de meia noite (hora padrão), como podemos observar na consulta abaixo:

SELECT * FROM Vendas WHERE DataVenda = '20070303'

Uma opção para retornar todas as vendas de um dia é utilizar a função CONVERT, extraindo apenas a porção data da coluna DataVenda e comparando com o dia. O problema desta solução é utilizar função em coluna na cláusula WHERE, inviabilizando o uso de índice (Index Seek) gerando problema de desempenho. A solução é entrar com um intervalo de datas, evitando o uso de função em coluna. Para comparar o volume de I/O vamos utilizar SET STATISTICS IO ON e o plano de execução gráfico.

SET STATISTICS IO ON

SELECT * FROM Vendas
WHERE convert(char(8),DataVenda,112) = '20070303'
-- Table 'Vendas'. Scan count 1, logical reads 29
-- Plano de Execução: Index Scan

SELECT * FROM Vendas
WHERE DataVenda >= '20070303' and DataVenda < '20070304'
-- Table 'Vendas'. Scan count 1, logical reads 5
-- Plano de Execução: Index Seek

O SQL Server 2008, finalmente, disponibiliza tipos de dados onde data e hora ficam separados, são eles DATE e TIME.

Vamos alterar a estrutura da tabela Vendas, criando uma nova coluna HoraVenda do tipo de dado TIME e depois iremos alterar a coluna DataVenda para o tipo de dados DATE.

DROP INDEX Vendas.ix_Vendas_DataVenda

-- Cria a coluna HoraVenda do tipo de dados TIME e
-- preenche com as horas existentes na coluna DataVenda
ALTER TABLE Vendas ADD HoraVenda time null
go
UPDATE Vendas SET HoraVenda = DataVenda

-- Altera a coluna DataVenda para o tipo de dados DATE
ALTER TABLE Vendas alter column DataVenda date not null

CREATE INDEX ix_Vendas_DataVenda
ON Vendas(DataVenda)

Agora ficou mais fácil retornar as vendas de um determinado dia utilizando comparação simples, veja as estatísticas da consulta abaixo.

SELECT * FROM Vendas WHERE DataVenda = '20070303'
-- Table 'Vendas'. Scan count 1, logical reads 5
-- Plano de Execução: Index Seek

Até o próximo post.
Landry

Novos tipos de dados do SQL Server 2008

Vou iniciar uma seqüência de artigos sobre os novos tipos de dados no SQL Server 2008, segue abaixo uma breve descrição:

- DATE: armazena somente a data.
- TIME: armazena somente a hora.
- DATETIMEOFFSET: armazena data e hora obedecendo timezone.
- DATETIME2: similar ao DATETIME, porém com precisão variável.
- GEOMETRY: dados espaciais utilizando representação plana da Terra ("Flat Earth").
- GEOGRAPHY: dados espaciais utilizando representação redonda da Terra ("round earth").
- HIERARCHYID: representa hierarquias.

Os primeiros quatro tipos de dados (DATE, TIME, DATETIMEOFFSET, DATETIME2) são tipos regulares do SQL Server, já os três últimos foram desenvolvidos como UDT (User Defined Type) com .NET.

No próximo post vou falar dos dois primeiros tipos de dados: DATE e TIME.
Até lá, Landry.

Reduzindo o espaço ocupado em um Banco de Dados utilizando Vardecimal no SQL Server 2005 SP2

Neste artigo irei mostrar uma novidade no armazenamento do tipo de dados DECIMAL disponível a partir do Service Pack 2 do SQL Server 2005. Este novo recurso pode reduzir o tamanho da linha com consequente redução na ocupação do banco de dados, proporcionando melhor desempenho. Porém a adoção deste novo recurso deverá ser considerado com muito cuidado como veremos neste artigo.

Durante muito tempo o recurso de armazenamento variável ficou restrito aos tipos de dados string (varchar, nvarchar, text e ntext) e binário (varbinary e image), restando aos tipos de dados numéricos o armazenamento em tamanho fixo. O grande benefício do armazenamento variável é a redução do tamanho total da linha proporcionando uma melhor ocupação da página de dados (Data Page 8Kb), com conseqüente redução de I/O e melhora no desempenho.
Se uma coluna NomeCliente for do tipo de dado Varchar(100) o SQL Server só irá ocupar um byte por caractere incluído pelo usuário mais dois bytes do ponteiro que irá indicar o término da coluna dentro da linha. Por exemplo, se o usuário incluir “Maria Lucia” na coluna acima o SQL Server irá alocar 11 bytes referentes a cada caractere do nome e 2 bytes para o ponteiro, somando 13 bytes no total!

Já uma coluna decimal é armazenada na área da linha destinada a colunas de tamanho fixo, ocupando espaço de acordo com a tabela abaixo:

Observando uma coluna Decimal é muito comum termos uma precisão muito maior do que utilizamos na maior parte dos dados, sendo claro o benefício do armazenamento variável na redução do espaço ocupado e conseqüente melhora no desempenho.
Para comprovar a redução do espaço ocupado vamos criar um banco de dados contendo uma tabela com uma coluna Decimal, utilize o script abaixo.

Create database DBvardecimal
alter database DBvardecimal set recovery simple
go
use DBvardecimal
go

Create table TBdecimal (

col1 int identity(1,1),col2 char(500),col3 decimal(22,6))

declare @cont int,@val char(500)
set @cont = 1
while @cont <= 100000 begin

set @val = 'Linha - ' + convert(varchar(20),@cont)

insert TBdecimal (col2,col3) values (@val,12.2)

set @cont = @cont + 1

end

go

Foram incluídas 100.000 linhas na tabela, utilize o script abaixo para retornar o espaço ocupado pela tabela após a inclusão das linhas.

sp_spaceused 'TBdecimal'
-- Linhas: 100000
-- Reservado: 61640 KB
-- Dados: 61600 KB

As 100.000 linhas da tabela estão ocupando 61.600 KB em 7.700 páginas de dados (Data Pages 8KB). Agora vamos habilitar o armazenamento variável para o tipo de dado Decimal utilizando o script abaixo.

EXEC sp_db_vardecimal_storage_format 'DBvardecimal', 'ON'
EXEC sp_tableoption 'TBdecimal', 'vardecimal storage format', 1

O primeiro EXEC habilita o armazenamento Vardecimal no Banco de Dados DBvardecimal utilizando a Stored Procedure sp_db_vardecimal_storage_format e o segundo habilita o armazenamento Vardecimal na tabela TBdecimal. Se a Stored Procedure sp_db_vardecimal_storage_format for executada sem parâmetro ela retorna a lista dos Bancos de Dados na Instância indicando indicando se o armazenamento Vardecimal está habilitado ou não.

Basta rodar novamente a Stored Procedure sp_spaceused e comparar os resultados, veja a diferença na tabela abaixo.

Para determinar se a tabela foi habilitada para o armazenamento Vardecimal utilize a consulta abaixo.

SELECT name, object_id, type_desc FROM sys.objects
WHERE OBJECTPROPERTY(object_id,'TableHasVarDecimalStorageFormat') = 1

A estrutura da linha foi alterada para comportar o tamanho variável no tipo de dados Decimal, por isso o Relational Engine das edições anteriores ao Service Pack 2 não conseguem manipular Bancos de Dados com Vardecimal habilitado.

Se você tentar fazer um Attach pelo Management Studio de um Banco de Dados com Vardecimal habilitado em um SQL Server 2005 SP1 irá receber a seguinte mensagem de erro: “An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

No log da Instância irá aparecer a mensagem: “The database ‘DBvardecimal’ cannot be opened because it is version 612. This server supports version 611 and earlier. A downgrade path is not supported”.

Já no Restore a mensagem de erro será: “The database was backed up on a Server running version 9.00.3042. That version is incompatible with this server, which is running version 9.00.2047…”.

Muito cuidado! Uma vez habilitado o armazenamento Vardecimal não poderemos mais retornar ao Service Pack 1, por isso testem bastante as aplicações antes de adotar o armazenamento Vardecimal.

Até a próximo post,

Landry.

Atualização das Estatísticas de Banco de Dados - Parte 2

Parte 2 – Atualizando as Estatísticas de Banco de Dados

No post anterior (http://sqlserver-brasil.blogspot.com/2008/03/atualizao-das-estatsticas-de-banco-de.html) vimos que o Otimizador de consultas utiliza as informações das Estatísticas de Banco de Dados na escolha do plano de execução, sendo assim fundamental para se ter um bom desempenho mantê-las atualizadas. Se ocorrer uma grande alteração na distribuição de valores em uma coluna indexada e a estatística não for Atualizada, o Otimizador pode escolher um plano de execução menos eficiente.

O SQL Server disponibiliza uma propriedade de banco de dados chamada AUTO UPDATE STATISTICS (habilitada por padrão), que monitora a atualização nas tabelas e dispara um UPDATE STATISTICS quando o volume de alterações for grande. Mesmo com este recurso habilitado, um volume pequeno de alterações pode não disparar a atualização automática das estatísticas e fazer com que o Otimizador tenha como base valores desatualizados, escolhendo o plano de execução menos eficiente.

Por exemplo, vamos utilizar a tabela DETAILS da Parte 1 do artigo, onde a query abaixo retorna 301 linhas sendo mais eficiente o plano de execução com Index Seek e Boulkmark Lookup. Habilitando as estatísticas de I/O observamos:

SET STATISTICS IO ON
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 305

Agora vamos executar uma alteração na tabela modificando a quantidade de linhas com valores em SalesOrderID maiores que 75000.

UPDATE dbo.details SET SalesOrderID = 76000
WHERE SalesOrderID < color="#006600">-- 14.148 linhas alteradas

Esta alteração não provocou a atualização automática das estatísticas, pois representa um volume de linhas alteradas pequeno comparando com o total de linhas que a tabela possui 121.317 (11,66% de linhas alteradas). Executando a query novamente observamos que o Otimizador adotou o mesmo plano de execução (Index Seek com Bulkmark Lookup), porém neste caso o menos eficiente, já que agora o filtro WHERE seleciona 14.449 linhas.

SET STATISTICS IO ON
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 14479
-- Index Seek com Bulkmark Lookup

SELECT * FROM dbo.details with(index(0)) WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 994
-- Table Scan

Neste caso o plano de execução ideal seria o Table Scan, basta comparar o volume de páginas lidas (Logical Reads):
- 14479 páginas lidas com o plano Index Seek com Bulkmark Lookup.
- 994 páginas lidas com o plano Table Scan.

Repare o uso do Hint de tabela with(index(0)), obrigando o Otimizador a resolver a query com Table Scan!

Para atualizar as Estatísticas de Banco de Dados utilizamos à instrução UPDATE STATISTCS , atualizando todas as estatísticas de uma tabela. Executando a instrução abaixo o Otimizador passa a escolher o melhor plano, Table Scan:

UPDATE STATISTICS dbo.details

SET STATISTICS IO ON
SELECT * FROM dbo.details WHERE SalesOrderID > 75000
-- Table 'details'. Scan count 1, logical reads 994
-- Table Scan

Podemos concluir que é necessário atualizar as Estatísticas de Banco de Dados periodicamente, pois apenas a propriedade de banco de dados AUTO UPDATE STATISTICS não garante a freqüência ideal de atualização das estatísticas. O comando UPDATE STATISTICS deve ser executado para cada tabela, um modo mais simples é utilizar a Stored Procedure SP_UPDATESTATS que atualiza as estatísticas de todas as tabelas.

Até o proximo post.
Landry.

Atualização das Estatísticas de Banco de Dados - Parte 1

Parte 1 – Entendendo o uso das Estatísticas de Banco de Dados

Venho trabalhando com consultorias e treinamento em SQL Server desde a versão 7.0, e um dos problemas mais comuns são as consultas de baixo desempenho. Um motivo freqüente para o baixo desempenho de uma consulta é ter Estatísticas de Banco de Dados desatualizadas, pois o Otimizador de consultas acaba selecionando um plano de execução menos eficiente.

Este artigo foi dividido em duas partes, na primeira veremos como o Otimizador de consultas utiliza a Estatística de Banco de Dados para elaborar o plano de execução. Na segunda parte do artigo vou mostrar o prejuízo que uma Estatística desatualizada causa no desempenho de uma consulta.

As Estatísticas de Banco de Dados fornecem informações valiosas ao Otimizador, orientando a escolha do plano de execução de uma query. Ao criar um índice o SQL Server cria automaticamente uma estatística associada, contendo a distribuição dos valores da primeira coluna da chave do índice. Quando a propriedade de banco de dados AutoCreateStatistics está com TRUE (configuração padrão), o Otimizador cria a estatística sempre que detectar sua ausência durante a elaboração de um plano de execução.

Um dos componentes principais das Estatísticas de Banco de Dados é a distribuição de valores da coluna chave, onde é armazenada a quantidade de linhas contendo cada valor individual (se existem muitos valores individuais o SQL Server divide estes valores em intervalos e armazena o total de ocorrência dentro do intervalo).

Vamos observar o uso da estatística na prática criando a tabela abaixo e incluindo algumas linhas:

USE AdventureWorks
GO
IF OBJECT_ID('dbo.details', 'U') IS NOT NULL
DROP TABLE dbo.details
GO
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
OrderQty, ProductID,SpecialOfferID, UnitPrice,
UnitPriceDiscount, ModifiedDate
INTO dbo.details
FROM AdventureWorks.Sales.SalesOrderDetail

CREATE NONCLUSTERED INDEX idx_nc_col2
ON dbo.details(SalesOrderID)
GO

Foi criado um índice na tabela DETAILS utilizando como chave a coluna SALESORDERID, sendo criada uma estatística de banco de dados para esta coluna. Vamos agora trilhar o caminho que o Otimizador utilizou para elaborar o plano de execução da query:

SELECT * FROM dbo.details WHERE SalesOrderID > 75000

Existem dois planos que o Otimizador poderá escolher:

1) Table Scan verificando o filtro WHERE linha a linha
2) Index Seek para resolver o filtro WHERE e depois Bookmark Lookup para recuperar as demais linhas da tabela que não se encontram no índice (veja que no SELECT temos *, isto é, todas as colunas).

Vamos contabilizar o custo de I/O (Imput/Output - leituras de página de dados e índice) necessário para resolver cada plano acima. Para determinar o Plano 1 (Table Scan), o SQL Server recorre ao catálogo para identificar quantas páginas de dados a tabela está ocupando, utilizando a query abaixo:

SELECT rows as QtdLinhas, data_pages Paginas8k
FROM sys.partitions p JOIN sys.allocation_units a
ON p.hobt_id = a.container_id
WHERE p.[object_id] = object_id('details')
and index_id in (0,1)

Resultado:
QtdLinhas: 121317
Paginas8k: 994

Desta maneira o Otimizador identificou que para resolver a query com o Plano 1 (Table Scan) serão necessárias 994 leituras de páginas de 8kb (páginas de dados – data pages).

Para contabilizar o I/O do Plano 2 (Index Seek + Bulkmark Lookup) o Otimizador identifica quantas linhas serão retornadas pelo filtro WHERE SalesOrderID > 75000, para isso ele utiliza a Estatística de Banco de Dados criada quando o índice na coluna SalesOrderID foi gerado. Para visualizar uma Estatística de Banco de Dados o SQL Server disponibiliza a instrução DBCC SHOWSTATISTICS.

DBCC SHOW_STATISTICS ('dbo.details','idx_nc_col2')

Executando esta instrução obtemos o resultado abaixo:



Em azul temos a data e hora que a estatística foi atualizada, informação importante como vocês verão mais a frente. Em vermelho a coluna Steps determina a quantidade de faixas de valores geradas no terceiro e último Grid (o terceiro Grid possui 143 linhas). Se você rolar este Grid até o final veremos os valores abaixo:



Vamos analisar o resultado utilizando as duas últimas linhas em vermelho:

RANGE_HI_KEY – representa o último valor de cada faixa, por exemplo: na linha 142 (em vermelho na figura acima) temos o valor 75122 representando a faixa que tem início no valor 74661 (o valor seguinte da linha 141 valor 74660) até o próprio 75122.

RANGE_ROWS – quantidade de linhas que possuem valores iguais ao da faixa, excluindo o último valor em RANGE_HI_KEY, por exemplo: na linha 142 (em vermelho na figura acima) temos a faixa de 74661 até 75122, porém 1077 representa a quantidade de linhas contendo os valores de 74661 até 75121. O Otimizador não tem como saber a exata distribuição das ocorrências dentro da faixa, porém as demais colunas fornecem uma boa idéia desta distribuição!

EQ_ROWS – quantidade de linhas que possuem o último valor da faixa, definido em RANGE_HI_KEY, por exemplo: na linha 142 (em vermelho na figura acima) podemos afirmar que existem duas linhas na tabela com o valor 75122.

DISTINCT_RANGE_ROWS – quantidade de valores dentro da faixa (SELECT DISTINCT dentro do intervalo da faixa), por exemplo: na linha 142 (em vermelho na figura acima) existem 461 valores dentro da faixa (75122 - 74661 = 461). Observamos que é um seqüencial sem pular valor!

AVG_RANGE_ROWS – igual a RANGE_ROWS / DISTINCT_RANGE_ROWS, por exemplo: 1077 / 461 = 2.336226.

Agora, utilizando a estatística da Figura 2, podemos ter uma idéia aproximada da quantidade de linhas retornadas pelo filtro WHERE SalesOrderID > 75000:

Linha 142 da figura acima: 75121 – 75000 = 121 valores dentro da faixa, em AVG_RANGE_ROWS temos 2.336226 multiplicando por 121 encontramos 282.683346. Somando as duas linhas em EQ_ROWS referente ao valor 75122, temos um total de 284.683346

Linha 143 da figura acima: 3 linhas com valor 75123.

O Otimizador concluiu então que seriam retornadas aproximadamente 287.683346 linhas com o filtro WHERE SalesOrderID > 75000, gerando 287 Bulkmark Lookups para as páginas de dados. Somando-se alguns poucos I/Os para navegar no índice (Index Seek), ficou bem a baixo do Plano 1 (Table Scan) com um total de 994 páginas.

Reparem que analisando a estatística o Otimizador errou por pouco a previsão da quantidade de linhas 287, onde na verdade a query retornou 301 linhas gerando 305 I/Os (301 páginas no Bulkmark Lookups e 4 páginas no Index Seek).



Obs.: No SQL Server 2005 o Bulkmark Lookup aparece no plano de execução composto por duas fases: Nested Loops e RID Lookup (se a tabela não tem índice Cluster) ou Cluster Index Seek (se a tabela tem índice cluster) até SP1, no SP2 aparece como Key Lookup. No SQL Server 2000 aparecia uma única fase chamada de Bulkmark Lookup.

Agora que já conhecemos as estatísticas de Banco de Dados, veremos no proximo post a importância de mantê-las atualizadas, até lá!
Landry.