quinta-feira, 23 de dezembro de 2010

Cursores - exemplo básico de utilização

Cursores são estruturas da linguagem T-SQL que permitem o processamento das linhas retornadas por uma consulta (SELECT), através de estruturas complexas de programação, como repetições ou  comandos condicionais.

O exemplo básico de cursor consiste em uma repetição (loop) onde um mesmo conjunto de comandos é executado para todas as linhas do retorno de uma consulta.
No exemplo abaixo, utiliza-se um cursor para executar a chamada da procedure sp_helptext para cada procedure de um banco de dados. As procedures foram obtidas através de uma consulta na view ROUTINES do Schema INFORMATION SCHEMA.

DECLARE @schemaName VARCHAR(30)
    , @procName VARCHAR(30)
    , @fullName VARCHAR(60)

-- Cursor para percorrer os nomes dos objetos
DECLARE cursor_objects CURSOR FOR
    SELECT
          ROUTINE_SCHEMA
        , ROUTINE_NAME
    FROM
        INFORMATION_SCHEMA.ROUTINES
    WHERE
        ROUTINE_TYPE = 'PROCEDURE'

-- Abrindo Cursor para leitura
OPEN cursor_objects

-- Lendo a próxima linha
FETCH NEXT FROM cursor_objects INTO @schemaName, @procName

-- Percorrendo linhas do cursor (enquanto houverem)
WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @fullName = @schemaName + '.' + @procName

    EXEC sp_helptext @fullName

    -- Lendo a próxima linha
    FETCH NEXT FROM cursor_objects INTO @schemaName, @procName
END

-- Fechando Cursor para leitura
CLOSE cursor_objects

-- Desalocando o cursor
DEALLOCATE cursor_objects 


Algumas considerações:
  • Um cursor deve estar sempre associado a uma consulta, especificada ao declarar o cursor.
  • O comando FETCH popula as variáveis recebidas como parâmetro com os valores da próxima linha da consulta a ser lida. O número de variáveis passadas como parâmetro deve ser igual ao número de colunas retornadas na consulta associada ao cursor.
  • A variável global @@FETCH_STATUS retorna o resultado da última operação FETCH executada por um cursor na conexão.
    O status 0 significa que o comando FETCH retornou uma linha, qualquer outro resultado significa que não houve linha retornada.
  • Cursores são estruturas relativamente lentas se comparadas ao desempenho de consultas do banco. O uso descuidado dessa ferramenta pode causar sérios problemas de performance.

sábado, 11 de dezembro de 2010

Atualizando registros através de junções de tabelas


As aplicações corporativas inserem, atualizam e excluem milhares e até milhões de registros todos os dias atavés da execução de comandos SQL na base de dados.

A atualização de registros (UPDATE) é uma tarefa normal e muito simples na maioria dos bancos de dados atuais, principalmente porque normalmente deseja-se atualizar os registros de uma única tabela.

Existem situações onde precisamos atualizar registros seguindo critérios mais elaborados, não apenas filtrando as linhas da tabela, mas também as linhas que participam de uma determinada junção entre tabelas.

Para realizar uma operação deste tipo, pode-se utilizar a seguinte sintaxe para o comando de update:

    UPDATE
        Tabela1
    SET
        campoX =  valorX
    FROM
        Tabela1 T1
    INNER JOIN Tabela2 T2 ON
        T1.id = T2.fk_id
    WHERE 
       T1.campo1 = valor1
       and T2.campo2 = valor2

Para ilustrar, vejamos um exemplo baseado na base de dados AdventureWorks2008:


No modelo acima, temos a relação dos produtos gerenciados na base com as suas categorias e sub-categorias. As tabelas Product, ProductSubCategory e ProductCategory, todas pertencentes ao Schema Production, estabelecem esta relação.

Imagine que todos as bicicletas (produtos pertencentes à categoria "Bikes") tenham sofrido um reajuste de preço 20% no preço final ao consumidor e seja necessário realizar esta atualização diretamente na base de dados.

Observe que não é possível realizar esta operação apenas adicionando filtros à cláusula WHERE, a não ser que se tenha conhecimento de cada um dos identificadores das sub-categorias da categoria "Bikes", o que seria muito trabalhoso.

Para realizar esta tarefa, utilizaremos a estrutura citada acima, unindo as 3 tabelas em questão, atualizando apenas o campo ListPrice da tabela Product dos registros cuja subcategoria pertença à categoria "Bikes".

Veja o comando a ser executado:

    UPDATE
        Production.Product
    SET
        ListPrice = ListPrice * 1.2
    FROM
        Production.Product P
    INNER JOIN Production.ProductSubcategory S ON
        P.ProductSubcategoryID = S.ProductSubcategoryID
    INNER JOIN Production.ProductCategory C ON
        S.ProductCategoryID = C.ProductCategoryID
    WHERE C.Name = 'Bikes'


Dica: Pode-se observar que a sintaxe empregada é muito parecida com a de uma consulta utilizando junções. Para evitar maiores problemas, sugerimos sempre a construção da consulta para testes dos registros afetados antes da execução da atualização desejada.

segunda-feira, 6 de dezembro de 2010

Passando uma lista de valores como parâmetro para uma Stored Procedure


As Stored Procedures podem aumentar consideravelmente a complexidade das pesquisas realizadas por aplicações em uma base de dados. 

O uso de parâmetros é uma das vantagens fornecidas pelas Stored Procedures. Através dos parâmetros, podemos tornar o filtro de uma consulta mais ou menos abrangente.

Em alguns casos, porém, pode ser necessário passar uma lista com tamanho indefinido de valores como parâmetro de filtro para uma pesquisa. Infelizmente, no SQL Server 2008 não é possível criar Stored Procedures com número de parâmetros variável, e não existem tipos de dados semelhantes aos vetores utilizados nas linguagens de programação convencionais.

Existem diversas formas de solucionar este problema. Uma das formas mais flexíveis de contornar esta situação é o uso de parâmetros do tipo XML.

Vamos utilizar um exemplo para entender como resolver esta situação. Utilizaremos como fonte de dados a tabela temporária Pessoa que deve conter o nome da pessoa e um código identificador (ID). Segue abaixo o código para criação da tabela:

/* Criando tabela Pessoa */
CREATE TABLE Pessoa
(
    ID int identity (1,1)
    , nome varchar(100)
)

/* Inserindo valores na tabela Pessoa */
Insert into Pessoa Values ('Evandro'),('Ronaldo'),('Rita'),('Mariana'),('Augusto')

Em seguida, criaremos uma Stored Procedure para selecionar registros desta tabela utilizando uma lista de IDs como filtro na forma de um parâmetro do tipo XML. O formato de nosso parâmetro será o seguinte:

   <id> 1 </id>
   <id> 2 </id>
          ....

No trecho abaixo, podemos verificar o código da procedure já realizando o filtro na tabela Pessoa através da lista de IDs:

/* Criando Procedure para filtro de pessoas através de uma lista de IDs */
CREATE PROCEDURE PROC_selectPessoa
    @filtroXML XML
AS
    SELECT P.ID, Nome FROM Pessoa P
    INNER JOIN   
        (
              SELECT
                   ParamValues.ID.value('.','int') as ID    
             FROM
                   @filtroXML.nodes('/id') as ParamValues(ID)
        ) F
    ON P.id = F.id
GO

Depois de criar a Stored Procedure, podemos simular a chamada realizada por uma aplicação, criando uma variável do tipo XML.

/* Simulando chamada realizada pela aplicação, passando os ids 1 e 5 */
DECLARE @parametros XML
SELECT  @parametros = '<id> 1 </id> <id> 5 </id>'
EXEC PROC_selectPessoa @parametros

Ao executar o script proposto acima, teremos o seguinte resultado:

ID Nome
1 Evandro
5 Augusto

Na figura abaixo, podemos ver nosso script em execução:


Para maiores informações sobre o tipo de dados XML, verifique outros artigos sobre o tema:

sexta-feira, 3 de dezembro de 2010

[off-topic] Setup de aplicações .Net Framework 3.5 no VS2010

Um dos problemas que se tem encotrado ao migrar do Visual Studio 2008 para o Visual Studio 2010 (VS2010) foi a questão do Projeto Setup para as aplicações antigas.

Ao gerar o pacote de instalação de uma aplicação, o VS2010 adiciona automaticamente o “.Net Framework 4.0” como um dos pré-requisitos para a execução do instalador. Na máquina do desenvolvedor, isso não é problema, uma vez que este já possui esta versão do Framework instalada (juntamente com o VS2010).

Em ambientes de produção, porém, muitas empresas ainda trabalham com a versão 3.5 do Framework e o processo de migração pode ser demorado. Sendo assim, os setups gerados com esse pré-requisito não serão executados.

Para contornar esta situação, será necessário fazer as seguintes modificações no projeto Setup:

1 - Retirar o “Framework 4.0” dos pré-requisitos para instalação

Abrir as propriedades do projeto (clicando com o botão direito no nome do projeto e depois em “Propriedades”).
A seguinte janela será aberta:


Em seguida, clicar no botão “Prerequisites...”, para selecionar os pré-requisitos de instalação da aplicação, como na janela abaixo:


Nesta janela, será necessário desmarcar as opções relacionadas ao “Framework 4.0” e selecionar o “Framework 3.5”, como demonstrado.  

2-Retirar o “Framework 4.0” das condições para execução do setup.

Clicar com o direito no nome do projeto, Selecionar a opção “View” e em seguida a opção “Launch Conditions” 


As condições de execução do setup serão exibidas. Nesta etapa, selecione o item “.Net Framework” dentro da pasta “Launch Conditions”. Em seguida, exiba suas propriedades (pressionando a tecla F4).


Na janela de propriedades, selecione a versão correta do Framework.

Após estas alterações, será possível gerar o setup através do Visual Studio 2010 e executá-lo em um ambiente onde o .Net Framework 4.0 ainda não foi instalado.

terça-feira, 23 de novembro de 2010

Alterando o banco de dados padrão (default database) de um login

Todo login criado em um servidor SQL Server possui um banco de dados padrão (opção default database) configurado.

Este será o banco de dados ao qual o usuário irá se conectar sempre que não passar o banco de dados desejado no momento da conexão (como ocorre ao conectar-se utilizando o SQL Server Management Studio ou o Enterprise Manager)

Esta configuração pode impedir que o usuário se conecte ao servidor através das interfaces de gerenciamento (SSMS / Enterprise Manager / Query Analyzer) caso o banco de dados associado seja excluído. Neste caso, a mensagem "Cannot open user default database. Login failed" é exibida durante a tentativa de logon, como no exemplo abaixo:

"Cannot open user default database. Login failed"

Para contornar este problema, será necessário conectar-ser com outro usuário (cujo banco de dados padrão não tenha sido excluído) e executar a procedure sp_defaultdb, que configura o banco de dados padrão para um determinado usuário. 

Deve-se utilizar a seguinte sintaxe para esta procedure:

sp_defaultdb 'username', 'database'

Após a reconfiguração o usuário em questão passará a ter o banco de dados padrão informado.

Para evitar que este problema aconteça, é recomendado associar usuários a um banco de dados cuja exclusão seja pouco provável. O banco master pode ser uma alternativa para evitar este problema (lembrando que os usuários normais não devem ter permissões de alteração neste banco).

sexta-feira, 19 de novembro de 2010

Verificando se uma tabela temporária existe em um banco de dados

Ao trabalhar com scripts complexos, principalmente aqueles que podem ser executados mais de uma vez em uma mesma sessão no banco de dados, pode ser necessário verificar se uma tabela temporária já existe na base de dados.

Essa verificação não é tão trivial para tabelas temporárias quanto para as tabelas normais. Para tabelas normais, basta executar um SELECT na view sysobjects (para o SQL Server 2000 e 2005) ou na view INFORMATION_SCHEMA.TABLES (para o SQL Server 2008) filtrando o nome da tabela.

As views de sistema citadas acima guardam apenas informações sobre a base de dados a que pertencem. Sendo assim, não será possível utilizar a mesma verificação para as tabelas temporárias, uma vez que estas são armazanadas no tempdb.

Para contornar esta situação, pode-se verificar se uma tabela temporária existe através da função object_id(), que retorna o identificador único de um objeto no sistema cujo nome deve ser passado por parâmetro para a função. Caso o objeto não seja encontrado, a função retornará NULL.

No exemplo abaixo, verifica-se a existência da tabela #tmp e esta é excluída antes de ser criada novamente.

IF object_id('tempdb..#tmpPessoa') IS NOT NULL 
BEGIN
     DROP TABLE #tmpPessoa

END

CREATE TABLE #tmpPessoa
(
    id INT,

    nome VARCHAR(100)
)
GO


Observe que foi necessário passar o caminho completo da tabela temporária para que esta fosse encontrada (..).

sexta-feira, 5 de novembro de 2010

Dica: Espaço em disco ocupado por uma tabela

O administrador de uma bases de dados precisa possuir informações detalhadas sobre as tabelas de um banco para tomar decisões sobre a criação de índices, particionamentos, esquemas de backup, etc.

O número de linhas e o espaço ocupado por uma tabela são informações fundamentais em diversas situações. Pode-se encontrar esta informação através das propriedades da tabela no SQL Server Management Studio.

Para aqueles que preferem utilizar linha de comando, a procedure de sistema "sp_spaceused" pode auxiliar na obtenção destas informações. Esta procedure retorna as seguintes informações sobre uma tabela (cujo nome deve ser passado como parâmetro):

  • Número de linhas
  • Espaço em disco reservado para a tabela
  • Espaço em disco ocupado pelos dados 
  • Espaço em disco ocupado pelo índice
  • Espaço em disco livre

Veja no exemplo abaixo como utilizá-la:

EXEC sp_spaceused 'Production.Product'

O resultado obtido pelo comando acima pode ser observado na figura abaixo:

quarta-feira, 8 de setembro de 2010

Dica: Número de linhas afetadas por um comando SQL

Quando executamos comandos de SELECT/INSERT/UPDATE/DELETE sobre tabelas ou Views em um banco de dados SQL Server, o número de linhas afetadas pelo comando é registrado no console do Management Studio (ou Query Analyzer até a versão 2000).

Entretanto, existem casos em que este número pode ser importante para a lógica de uma aplicação, ou mesmo para decidir se uma tarefa foi executada com sucesso ou não.

Assim, o SQL Server permite recuperar esta informação através da variável @@ROWCOUNT. A cada execução de comando, esta variável é atualizada e passa a conter o número de linhas afetadas pelo comando executado.

Veja como utilizá-la no exemplo abaixo:

-- Criando tabela temporária para testar o uso de @@ROWCOUNT
CREATE TABLE #teste (id INT)      

-- Inserindo um único registro na tabela #teste
INSERT INTO #teste VALUES(0)           
SELECT @@ROWCOUNT 


-- Inserindo vários (3) registros na tabela #teste
INSERT INTO #teste VALUES(10),(20),(30)
SELECT @@ROWCOUNT


-- Selecionando todos os registros da tabela #teste
SELECT * FROM #teste                   
SELECT @@ROWCOUNT


-- Removendo tabela temporária
DROP TABLE #teste                

Veja o resultado da execução deste script na figura abaixo:


Um bom exemplo de aplicação para esta variável seria em uma Stored Procedure que realiza uma operação de insert/update/delete. Através dessa variável, pode-se criar uma validação para retornar o status da operação (sucesso/fracasso) baseado no número de linhas afetadas.

terça-feira, 7 de setembro de 2010

Tratamento de erros no SQL Server (TRY-CATCH)

Em linguagens de progamação como C# e Java, é comum o uso do bloco TRY-CATCH para tratar as exceções lançadas durante a execução das aplicações.

Ao implementar Stored Procedures, Funções e Triggers, ou mesmo batches mais complexos, pode ser necessário tratar os possíveis erros ocorridos nos comandos.

Até a versão 2000 do SQL Server, era necessário utilizar a função de sistema @@ERROR após cada um dos comando executado, pois o valor de retorno dessa função é reiniciao a cada comando executado.

As versões 2005 e 2008 do SQL Server permitem implementar o tratamento de erros através do bloco TRY-CATCH, de maneira semelhante às linguagens de programação convecionais. 

Este bloco é formado por dois sub-blocos:

TRY - contém os comandos necessários para executar a tarefa desejada
CATCH - contém os comandos para tratamento de possíveis erros ocorridos no bloco TRY

Quando um erro ocorre no dentro do bloco TRY a execução é desviada para o bloco CATCH, o que permite continuar a execução do script ou interrompê-lo, de acordo com as necessidades do usuário e gravidade do erro gerado.
Sintaxe
A sintaxe definida para este bloco de comandos é a seguinte

BEGIN TRY
     [comandos SQL para execução de uma tarefa]
END TRY
BEGIN CATCH
     [comandos SQL para tratamento do erro]
END CATCH

Exemplo
Abaixo, temos um tratamento de erro realizado através deste bloco de comandos:

-- Criando tabela para teste
CREATE TABLE #teste (id int)

-- Adicionando restrição à tabela.
-- Somente IDs menores do que 10 serão aceitos

ALTER TABLE #teste ADD CONSTRAINT chkID CHECK (id < 10)

-- Executando a tarefa em transação
BEGIN TRANSACTION

BEGIN TRY
       
    INSERT INTO #teste VALUES (5)
    INSERT INTO #teste VALUES (2)
    INSERT INTO #teste VALUES (13)  -- Inserção de ID inválido

    COMMIT TRANSACTION -- Efetivando alterações na base

END TRY
BEGIN CATCH
   
    PRINT 'Erro ao executar script'
    ROLLBACK TRANSACTION -- Desfazendo as alterações na base
         
END CATCH

SELECT * FROM #teste

DROP TABLE #teste

Ao executar o script, obtemos o resultado abaixo. Verifique que nenhuma linha foi inserida na tabela #teste.

As mensagens exibidas no console do SQL estão abaixo:


Observe que nenhuma mensagem de erro do sistema foi lançada. Apenas a mensagem do tratamento foi exibida.
Informações sobre o erro
O SQL Server permite recuperar as informações do erro lançado no bloco TRY, através de algumas funções do sistema:

ERROR_NUMBER() -Retorna o número do erro ERROR_MESSAGE() - Exibe a mensagem do erro ERROR_SEVERITY() - Retorna o grau de severidade do erro ERROR_LINE()  - Retorna a linha em que o erro ocorreu

Referências
http://msdn.microsoft.com/pt-br/library/ms175976.aspx

quarta-feira, 25 de agosto de 2010

Dica: Criptografando os comandos de Stored Procedures e Views

Ao compilar uma aplicação construída em uma linguagem de programação usual, obtém-se um ou mais arquivos binários executáveis. Nesses arquivos, o código-fonte não é acessível, o que aumenta a proteção da lógica utilizada para escrever o programa.

O SQL Server permite fazer algo semelhante com as stored procedures e views armazenadas em seus bancos de dados. Em termos práticos, é possível criptografar os comandos SQL contidos em uma procedure ou view de forma que o comando sp_helptext não seja capaz de exibir o script do objeto em questão.

O uso da cláusula WITH ENCRYPTION permite que esta tarefa seja realizada.

Para exemplificar, utilizaremos a tabela Vendas.Clientes, definida abaixo:

CREATE TABLE Vendas.Clientes
(
  id                     INTEGER INDENTITY(1,1) PRIMARY KEY,
  nome               VARCHAR(50),
  email               VARCHAR(50),
  rendaMensal   MONEY
)

Criptografando uma Stored Procedure
Para criptografar uma Stored Procedure, deve-se incluir a opção WITH ENCRYPTION, ao criá-la , conforme o exemplo abaixo:

CREATE PROCEDURE Vendas.proc_rendaCliente
    @id int
WITH ENCRYPTION
AS
   SELECT nome, rendaMensal FROM Vendas.Cliente
   WHERE id = @id
GO

Ao executar a procedure do sistema sp_helptext passando como parâmetro o nome da procedure criada, o SQL Server mostra uma mensagem informando que a procedure não pode ser exibida porque foi criptografada. Veja o resultado abaixo:


Criptografando uma Stored Procedure
A criptografia dos comandos de uma View é muito semelhante à de uma Stored Procedure. Para criptografar uma View, deve-se incluir a opção WITH ENCRYPTION, ao criá-la , conforme o exemplo abaixo:

CREATE VIEW Vendas.vw_rendaClientes
WITH ENCRYPTION
AS
    SELECT nome, rendaMensal FROM Vendas.Cliente
GO

Ao executar a procedure do sistema sp_helptext passando como parâmetro o nome da view criada, o SQL Server mostra uma mensagem informando que o código da view não pode ser exibido porque  ela foi criptografada. Veja o resultado abaixo:

Conclusão
Criptografar os comandos de Stored Procedures e Views pode ser uma alternativa para aumentar a segurança de uma banco de dados, ocultando a lógica de processos implementados no banco e ocultando regras de negócio de uma solução.

Uma vez criptografado, não é possível recuperar o código que gerou o objeto. Portanto, é muito importante ter uma cópia do código-fonte em algum lugar seguro para fins de backup e manutenções futuras.

Referência
CREATE PROCEDURE (Transact-SQL) (MSDN)

sábado, 21 de agosto de 2010

Identificando página de dados danificadas no SQL Server

Por mais garantidos que sejam o hardware e software de armazenamento de dados.Não há como garantir a ausência de falhas.

O SQL Server fornece uma ferramenta importante para indentificar pequenos erros, que afetam páginas isoladas da base de dados.

O próprio SQL Server analisa as páginas de dados, avaliando sua integridade. A tabela suspect_pages contém as páginas que apresentaram algum tipo de defeito durante a verificação.

Assim, uma maneira simples de identificar as páginas danificadas em um servidor SQL Server é utilizar o seguinte comando:
use msdb
go
select * from suspect_pages

quinta-feira, 19 de agosto de 2010

Desabilitando restrições ao inserir muitos registros

O uso de restrições (CONSTRAINTS) nas tabelas de um banco de dados é extremamente recomendável para garantir a integridade dos dados armazenados.

Entretanto, quando os registros são inseridos ou alterados em tabelas que possuam contraints, o SQL Server precisará validar todas as constraints para cada cada alteração realizada, o que diminui a performance destas operações.

Em situações comuns de uso do banco de dados, esse impacto não é percebido, principalmente se o banco de dados foi implementado seguindo as boas práticas. Por outro lado, em uma carga de dados, ou em qualquer situação onde seja necessário realizar muitas inserções ou alterações de registros, essa verificação adicional pode impactar severamente na perfomance destas operações.

Para contornar esta situação, é possível desabilitar a verificação de constraints, desde que se tenha a garantia de que os dados a serem inseridos/atualizados estão em conformidade com as restrições do banco.

A cláusula NOCHECK do comando ALTER TABLE desabilita estas verificações.

Sintaxe
ALTER TABLE NomeDaTabela
       NOCHECK CONSTRAINT NomeDaConstraint
Exemplo
ALTER TABLE Cliente
       NOCHECK CONSTRAINT fk_cliente_x_empresa
Referência
ALTER TABLE (Transact-SQL)

sexta-feira, 13 de agosto de 2010

Tech-Ed 2010 - 13 a 15 de Setembro




As inscrições para o Tech-Ed 2010 já começaram!

O Tech-Ed é o maior evento técnico brasileiro voltado para profissionais de TI e desenvolvedores que utilizam a tecnologia Microsoft.

Acesse o site http://www.teched.com.br para maiores informações.

As palestras estão disponíveis através do endereço http://www.teched.com.br/2010/Palestras.aspx. Veja as palestras da trilha "Plataforma de Banco de Dados" filtrando pelo código DBP.

quinta-feira, 12 de agosto de 2010

Dica: Executando várias vezes um mesmo batch

Em certas ocasiões, é necessário executar várias vezes uma mesma sequência de comandos no SQL Server (batch). Essa tarefa pode ser muito trabalhosa quando o número de repetições atinge grandes valores.

Se os comandos forem sempre os mesmos e não houver mudança de parâmetros, pode-se utilizar o comando GO seguido pelo número de vezes que o comando será executado, respeitando a sintaxe abaixo:

GO <número_de_execuções>

O comando GO não é um comando do SQL Server, mas sim uma instrução interpretada pelas interfaces de gerenciamento do SQL Server (SQL Server Management Studio, sqlcmd e osql).

Esse comando pode ser especialmente útil para popular bases de teste.

Exemplo
No exemplo abaixo, criamos uma tabela com um campo inteiro e inserimos 15 registros com valores aleatórios:

CREATE TABLE #tblExemplo (number DECIMAL)
GO

INSERT INTO #tblExemplo VALUES(RAND() * 100)
GO 15

SELECT * FROM #tblExemplo

DROP TABLE #tblExemplo

GO

Pode-se observar o resultado do script na imagem abaixo:



Referências
GO (Transact-SQL)

terça-feira, 10 de agosto de 2010

Trabalhando com Database Snapshots

O que é um Database Snapshot?
Snapshot é uma "fotografia" da base de dados em um estado consistente em um determinado momento.
Este recurso, disponível nas versões do SQL Server 2005 e 2008, permite a criação rápida de um ambiente somente-leitura para relatórios, testes, proteção contra erros de usuário, etc.

Como funciona?
Ao criar um snapshot, cria-se um novo banco vazio (Snapshot DB) que será preenchido com os dados originais conforme estes são alterados após a criação do Snapshot.

Quando um Snapshot é configurado para uma base de dados (base de dados fonte), o SQL Server executa um processo chamado copy-on-write. Neste processo, ao alterar uma página de dados na base de dados fonte, uma cópia da página original é armazenada na base de dados Snapshot. Essa operação é válida somente para a primeira alteração na página de dados, mantendo, assim, a cópia original na base Snapshot.

Ao ler os dados da base Snapshot, o SQL Sever retorna os dados da base de dados fonte, porém as páginas alteradas são substituídas pelas páginas armazenadas no banco de dados Snapshot, resultando em uma "visão" do momento em que o Snapshot foi criado.

Criando um Snapshot
Para criar um Snapshot, utiliza-se o comando CREATE TABLE com algumas modificações:

CREATE DATABASE SnapshotDatabaseName
ON
(name = sourcefilename1 , FILENAME =  snapshotfilepath1),
(name = sourcefilename2 , FILENAME =  snapshotfilepath2)
AS
SNAPSHOT OF SourceDatabaseName

Restaurando um Snapshot
Pode-se também restaurar uma base de dados, para o estado em que o Snapshot foi criado. Para isso, utiliza-se o comando:

RESTORE DATABASE databaseName FROM
DATABASE_SNAPSHOT = SnapshotDatabaseName

Considerações
Este não pode ser utilizado como backup, uma vez que a base Snapshot só armazena as página de dados alteradas na base original desde a criação do Snapshot.

Referências
How Database Snapshots Work (MSDN)

segunda-feira, 9 de agosto de 2010

Conflitos de COLLATION em operações de comparação

Em bancos de dados SQL Server, as Collations são utilizadas para definir a maneira como os dados do tipo string (nchar, nvarchar, e ntext) são armazenados.
Em resumo, uma collation define:
  • A codificação (Character Set) utilizada para armazenar os caracteres não-Unicode.
  • O algoritmo de ordenação utilizado para ordenar o retorno das consultas
O SQL Server permite que seja configurada uma collation default para o servidor,atribuindo-a a todos os campos cuja collation não é explicitamente definida durante a criação.

Além disso, as collations podem ser definidas em diferentes granularidades. É possível definir collations para bancos de dados, tabelas, campos de tabelas e variáveis do SQL Server.

Entretanto, o uso de diversas collations em uma mesma base de dados pode causar problemas ao desenvolvedor, pois algumas collations não são compatíveis entre si. Um problema comum ao escrever consultas envolvendo comparação de campos de texto com collations diferentes é o confilto nessa operação de igualdade.

"Cannot resolve collation conflict for equal to operation"

Uma solução para este problema é indicar ao processador de consultas qual collation deverá ser utilizada para realizar a comparação entre os dois valores. Para isso, deve-se utilizar o comando COLLATE. Além disso, pode-se passar como argumento a palavra-chave DATABASE_DEFAULT, que retorna a collation definida para o banco de dados em uso. Veja o exemplo abaixo:


SELECT userName FROM Usuarios
INNER JOIN Clientes ON
Clientes.userName COLLATE DATABASE_DEFAULT
= Usuarios.userName  COLLATE DATABASE_DEFAULT

No exemplo, foi feita a junção entre as tabelas "Usuarios" e "Clientes", utilizando-se o campo userName, presente em ambas as tabelas. Supõe-se que este campo possua collations diferentes e conflitantes nestas tabelas. O comando COLLATE resolve o conflito na operação de igualdade do JOIN, utilizando a collation padrão do banco.

Referências:
Comando COLLATE (MSDN)

sábado, 24 de julho de 2010

Introdução ao tipo de Dados XML - Parte 2

Utilizando SCHEMA COLLECTIONS para validar um campo XML
De acordo com o texto anterior, existem 2 formas de armazenar conteúdo XML em um banco de dados. O primeiro permite armazenar qualquer conteúdo XML desde que possua a sintaxe correta, é o XML não-tipado. Assim, para campos deste tipo, a única validação realizada é a da sintaxe do conteúdo inserido.

Em bancos de dados corporativos, a consistência e a integridade das informações armazenadas é fundamental. Dessa forma, permitir que o banco armazene qualquer tipo de conteúdo em um campo XML pode não ser viável, principalmente quando existem campos obrigatórios a serem armazenados em tal campo.

Para isso, o SQL Server permite que um campo ou variável do tipo XML seja tipado, isto é, tenha sua estrutura definida através de um SCHEMA COLLECTION.

Criando um SCHEMA COLLECTION
A sintaxe básica para criar um Schema Collection está descrita abaixo:

CREATE XML SCHEMA COLLECTION SchemaName AS  'conteúdo XML'

O exemplo a seguir ilustra o uso deste comando:

CREATE XML SCHEMA COLLECTION ContactSchema AS
'<schema xmlns="http://www.w3.org/2001/XMLSchema">
    <element name = "Contato">
        <complexType>
            <sequence>
                <element name = "nome" type="string"/>
                <element name = "email" type="string" />
            </sequence>
        </complexType>
    </element>
</schema>'

No exemplo, foi definido um Schema Collection chamado ContactSchema com uma estrutura Contato cujos atributos são nome e e-mail, ambos do tipo string.

Os Schema Collections de um banco de dados podem ser acessados através do caminho nome_do_banco->Programmability -> Types -> XML Schema Collections, através da caixa de ferramentas "Object Explorer". A Figura abaixo mostra o Schema Collection criado no exemplo anterior:


Aplicando o Schema Collection
Depois de criar o Schema Collection, pode-se aplicá-lo aos campos XML das tabelas do banco ou mesmo às variáveis XML utilizadas.

No exemplo abaixo, aplicamos o Schema Collection ContactSchema à variável @contactInfo

DECLARE @contactInfo XML (ContactSchema)

Nas linhas a seguir, um conteúdo XML é associado à variável criada.

SET @contactInfo =
'<Contato>
<nome> Evandro </nome>
<email> evdjramos@gmail.com </email>
</Contato>'


A figura abaixo mostra o resultado da execução do scripta (foi adicionado um comando SELECT na variável @contactInfo para exibir o conteúdo armazenado.


Ao associar o conteúdo à variável com Schema Collection, o SQL Server valida o XML, verificando, além de sua sintaxe, sua estrutura. 

Caso um valor inválido seja atribuído à variável, um erro será disparado, como mostra a figura abaixo


Neste exemplo, o atributo obrigatório email do elemento Contato não foi definido, causando o erro mostrado.

Aguarde!
Na continuação deste artigo, teremos veremos consultas em XML e dicas de implementação de stored procedures e funções com utilização de parâmetros XML.

sábado, 17 de julho de 2010

Introdução ao tipo de Dados XML - Parte 1

Definição
O tipo de dado XML permite que o conteúdo de um arquivo estruturado com formato XML seja armazenado em uma variável ou coluna do banco de dados.
Versões do SQL Server
O tipo de dados XML foi umas das novidades introduzidas no SQL Server 2005. Até a versão 2000 do SQL Server, era possível armazenar o conteúdo de um arquivo XML em uma tabela através de colunas do tipo text e ntext, porém não era possível utilizar os benefícios da estrutura hierárquica do arquivo, uma vez que este era armazenada como texto puro.
Vantagens
Este tipo especial de dados permite que o conteúdo de um arquivo XML seja armazenado de forma que sua estrutura hierárquica seja padronizada através de um Schema e consultada através de XQuery. Assim, em uma tabela com colunas deste tipo, pode-se criar queries onde o filtro (cláusula WHERE) realize comparações com nós da árvore XML.
XML Tipado e XML Não-tipado
O SQL Server possibilita a definição de Schemas para controlar os campos XML, isto é, ao definir um schema para um campo, cria-se uma regra para a estrutura do XML a ser armazenado por ele. Sendo assim, apenas os arquivos ou trechos de arquivos que obedeçam a estrutura definida poderão ser inseridos na tabela.
Variáveis XML
Assim como os tipos comuns de dados do SQL Server, o tipo XML possibilita a criação de variáveis deste tipo. O comando abaixo mostra como criar uma variável deste tipo:

DECLARE @varXML XML
A cláusula FOR XML
Caso seja necessário retornar os dados de uma consulta ou mesmo trabalhá-los utilizando o formato XML, pode-se facilmente convertê-lo para esta estrutura através a cláusula FOR XML.
Pode-se aplicar o FOR XML ao resultado de uma consulta da seguinte forma:

SELECT campo1, campo2
FROM Tabela
WHERE campo2 = const
FOR XML {AUTO | RAW('NomeDoElemento')}

Exemplo básico utilizando variável XML e comando FOR XML

-- Criando e populando tabela temporária com dados de pessoas
CREATE TABLE #Pessoa (id int identity(1,1), nome varchar(100), sexo VARCHAR(1))
INSERT INTO #Pessoa VALUES ('Evandro','M'),('Ricardo','M'),('Márcio','M'),('Mariana','F'),('Patrícia','F'),('José','M')

-- Definindo variável do tipo XML
DECLARE @varXML XML

-- Utilizando o comando FOR XML para converter o resultado da consulta em um XML
SELECT  @varXML =
    (SELECT
        id,nome,sexo
    FROM
        #Pessoa
    ORDER BY
        nome
    FOR XML RAW('Pessoa'))

-- Exibindo o conteúdo da variável XML
select @varXML as XML

-- Apagando a tabela temporária
DROP TABLE #Pessoa

Ao executar o script, obtém-se o seguinte resultado:


Ao clicar no link do resultado da consulta, pode-se acessar o conteúdo XML retornado:


O XML gerado pode ser utilizado nos processamentos subsequentes ou utilizado para retornar dados a uma aplicação.

Aguarde! Nos próximos artigos falaremos sobre Schemas em XML estruturados e teremos dicas sobre o uso de variáveis e parâmetros XML.

terça-feira, 25 de maio de 2010

Limpando os Buffers de dados e o Cache de Procedures

O tempo de resposta de uma aplicação comercial é um requisito quase que obrigatório para o seu sucesso. Diversos fatores contribuem para a eficiência das aplicações desenvolvidas, a performance das consultas à base de dados é um desses fatores.

Para otimizar as consultas, existem muitas práticas e ferramentas, como índices, filtros, redução do número de colunas, etc. E é muito aconselhável lançar mão do maior número possível.

Muitas vezes, ao implementar consultas, seja via procedure ou comandos ad-hoc, não conseguimos testar sua eficiência, pois, uma vez executada, o SQL Server guarda informações sobre sua execução para otimizar futuros acessos. Este é um poderoso recurso, principalmente para acelerar o retorno das queries executadas com maior frequência.

Para testar a performance real das consultas implementadas, é necessário desconsiderar a ação dos buffers e caches. Para isso, pode-se utilizar os comandos:

-- Limpa cache de procedures
DBCC FREEPROCCACHE

-- Limpa buffers (comandos add-hoc)
DBCC DROPCLEANBUFFERS