Lição 2: Declarativa de Integridade de dados

maio 25, 2011

 

Validação de dados é uma das rotinas mais comum de um desenvolvedor.

Validação de dados

Há duas formas de validar a integridade dos dados, usando a integridade de dados declarativa ou integridade de dados procedural.

Integridade de dados declarativa é um conjunto de regras que são aplicadas a uma tabela e suas colunas utilizando o comando ALTER TABLE e CREATE TABLE. Essas regras são conhecidas como constraints.

A integridade de dados procedural é implementada através de procedures com validações de dados ou de triggers que checam os dados.

Como implementar a Integridade de dados

Há cinco tipos de constraints, PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE e DEFAULT.

PRIMARY KEY e UNIQUE constraints

Uma coluna ou uma combinação de colunas criam um identificador único para uma linha, que é reforçada pela criação de um índice único e não aceita duplicidade. Devido a isso primary key e unique constraints possuem a mesma limitação de tamanho como uma chave ou índice, ou seja, não pode haver mais de 16 colunas ou 900 bytes de dados.

Quando nada é especificado o índice criado é clustered e o índice para um unique é do tipo non-clustered. Você pode alterar esse comportamento especificando o tipo de index, veja o exemplo:

–Primary key as clustered index

ALTER TABLE MyTable

ADD PRIMARY KEY (MyTableId)

–Primary key as a nonclustered index

ALTER TABLE MyTable

ADD PRIMARY KEY NONCLUSTERED (MyTableId)

FOREIGN KEY constraints

 

FOREIGN KEY constraints

Foreign key constraint identifica uma coluna ou combinação de colunas que devem existir na mesma tabela ou em outra tabela. A foreign key constraint gerencia a integridade referencial entre duas tabelas ou apenas uma. Para implementar uma foreign key constraint você deve seguir essas regras:

– As colunas referenciadas devem ter o mesmo tipo de dados nos dois locais.

– As colunas referenciadas devem possuir um índice único.

Devido ao índice único, as colunas da foreign key devem respeitar a mesma limitação da primary key.

Quando ocorre uma violação de integridade referencial devido a um delete ou update a linha em questão retorna uma mensagem de erro e desfaz o comando que provocou o erro. Você pode definir qual ação o SQL Server deve realizar nesses casos, veja abaixo:

– NO ACTION – Comportamento padrão do SQL Server, desfaz a ação e retorna um erro.

– SET NULL – Altera os valores referenciados para NULL, não retorna erro.

– SET DEFAULT – Altera os valores referenciados para o valor default da coluna, não retorna erro.

– CASCADE – Exclui linhas referenciadas em um comando delete (ON DELETE) e atualiza linhas referenciadas em um comando update (ON UPDATE), não retorna erro.

Veja como implantar essas ações:

 

CREATE TABLE Customers (

        CustomerID INT PRIMARY KEY );

CREATE TABLE Orders (

        OrderID INT PRIMARY KEY

        ,CustomerID INT NULL

               REFERENCES Customers

                  ON DELETE SET NULL

                  ON UPDATE CASCADE );

 

CHECK CONSTRAINTS

Check constraints são uma série de regras que validam o dado antes de incluir na coluna. Veja algumas vantagens:

– São de fácil implementação.

– São verificadas automaticamente.

– Podem melhorar a performance.

Veja o script de uma simples check constraint que verifica se o preço do produto não é negativo:

ALTER TABLE Products

ADD CHECK (Price>=0.0);

Essa simplicidade é uma grande vantagem sobre as triggers, porém há algumas desvantagens:

– As mensagens de erro são geradas automaticamente e não podem ser substituida.

– Uma check constraint não pode “enxergar” o valor anterior da coluna.

Você pode enriquecer a check constraint utilizando funções de usuário, utilizando T-SQL ou ainda utilizar funções CLR (tanto em C# quanto em VB)

CHECK E FOREIGN KEY CONSTRAINTS VS. QUERY PERFORMANCE

Check e foreign key podem melhorar a performance, pois ambos são regras que o optimizer pode usá-los para criar planos de execução mais eficientes, o código abaixo é um simples exemplo de como isso acontece:

CREATE TABLE Customers (

                CustomerID INT PRIMARY KEY );

CREATE TABLE Orders (

               OrderID INT PRIMARY KEY

               ,CustomerID INT NOT NULL

                    CONSTRAINT FK_OrdersCustomers

                            REFERENCES Customers(CustomerID) );

Para entedermos melhor o que o SQL Server faz quando executa a consulta selecione a opçao Include Actual Execution Plan ou pressione Ctrl+M.

SELECT o.* FROM Orders AS o

WHERE

         EXISTS (SELECT * FROM Customers AS c

             WHERE c.CustomerID = o.CustomerID);

Perceba pelo plano de execução que a tabela Customers não foi acessada, apenas a tabela Orders, isto porque o optimizer sabe ela que não é necessaria para o operado EXISTS porque a foreign key constraint requer que todos os pedidos (orders) possuem referência com um cliente (customer), que é o que a cláusura where verifica.

 

clip_image001

 

Agora desabiliete a foreign key:

ALTER TABLE Orders NOCHECK CONSTRAINT FK_OrdersCustomers;

Execute a mesma consulta novamente e perceba que nesse caso o optimizer executa o operador EXISTS. Devido a foreign key constraint estar desabilitada o SQL Server não pôde ter certeza que todos os pedidos possuem clientes válidos. Em tabelas grandes isso pode fazer uma grande diferença.

clip_image002

Para habilitar novamente a utilize o seguinte código:

ALTER TABLE Orders CHECK CONSTRAINT FK_OrdersCustomers;

ALTER TABLE Orders

     WITH CHECK

       CHECK CONSTRAINT FK_OrdersCustomers

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com

Anúncios

Lição 1: Trabalhando com Tabelas e Tipos de Dados

maio 17, 2011

 

Modelar, criar e manter tabelas é uma das funções mais importantes de um desenvolvedor de banco de dados.

Tipos de Dados

Antes de criar uma tabela você precisa entender as diferenças entre os tipos de dados que você pode utilizar em uma coluna. Há dois tipos diferentes de tipos de dados no Sql Server:

  • Tipos de dados de sistema.
  • Tipos de dados de usuários (User-Defined types – UDTs) ou Sql Common Language Runtime (CLR).

Cadeias de Caracteres

Existem alguns tipos de dados string no Sql Server, são eles: char, varchar, nchar, nvarchar, text, ntext.

A diferença entre char e varchar (assim como nchar e nvarchar) é fato do char possuir tamanho fixo e varchar tamanho variável, ou seja char sempre aloca o espaço suficiente em disco para armazenar o tamanho declarado e varchar armazena apenas o tamanho do dado inserido. A vantagem de utilizar char é que as atualizações feitas na coluna nunca requer o deslocamento da linha pois os dados inseridos sempre utilizam o mesmo tamanho, em contra partida varchar aloca muito menos espaço do que char.

Para os tipos de dados char e varchar a collation é utilizada para especificar o code page (conjunto de caracteres) do conteúdo da coluna. A collation também é utilizada para definir como ordenar e comparar os dados armazenados. A nomenclatura da collation especifica algumas características:

  • CI – Case Insensitive
  • CS – Case Sensitive
  • AI – Accent Insensitive
  • AS – Accent Sensitive

Nchar e nvarchar armazenam caracteres utilizando o Unicode universal code page (UCS-2). 

 

Tipos numéricos exatos

Os números numéricos exatos são inteiros ou decimais, produzem o mesmo resultados exceto pelo processamento ou pela magnetude (tamanho) dos números.

cbcpt5ok

 

Tipos numéricos aproximados

O sql server suporta dois tipos numéricos com pontos flutuantes o real e o float, assim como o decimal, ambos aceitam parâmetros. Os parâmetros definem o numero de bits que será utilizado para armazenar o dado.

image

 

Manipulando Data e Hora

A tabela abaixo possui um breve resumo sobre os tipos de dados de data e hora.

image

É necessário entender as diferenças entre smalldatetime e datetime pois há dois grandes problemas, um deles é que a hora e dia são armazenados juntos e isso precisa ser levado em consideração, veja o exemplo a seguir:

SELECT SalesOrderID

             ,CustomerID

             ,OrderDate

FROM Sales.SalesOrderHeader

WHERE OrderDate=‘20080818’

A Consulta apenas retorna pedidos realizados no dia 18/08/2008 exatamente as 00:00:00. Para resolver esse problema você precisa especificar o horário na consulta, como nos exemplos abaixo:

–Query #1

SELECT SalesOrderID ,CustomerID ,OrderDate

FROM Sales.SalesOrderHeader

WHERE OrderDate BETWEEN ‘2008-08-18T00:00:00’ and ‘2008-08-18T23:59:59.997’

 

–Query #2

SELECT SalesOrderID ,CustomerID ,OrderDate

FROM Sales.SalesOrderHeader

WHERE OrderDate>=‘20080818’ and OrderDate <‘20080819’

 

Ambas consultas produzem o mesmo resultado, porém considere utilizar o segundo exemplo pois o mesmo atende tanto datetime quanto datetime2.

O tipo de dados time aceita um parâmetro para especificar a precisão ou número de decimais por fração de segundo. Quando o parâmetro não é especificado a precisao é de 7 casas decimais.

 

Tabelas Básicas

No Sql Server há três tipos de variações de tabelas: permanentes, temporárias locais e temporárias globais, além dessa variações temos os tipos de tabelas e parâmetros da tabela.

 

Criando uma tabela

Antes de criar uma tabela é necessario especificar o schema. Schema é similar a namespace em diversas linguagens de programação, o Sql Server possui os schemas sys para objetos de sistema e dbo para novos objetos.

CREATE SCHEMA Sales;

GO 

CREATE TABLE Sales.Customers(

CustomerId INT NOT NULL,

Name NVARCHAR(50) NOT NULL);

Tabelas também podem ser criadas a partir de uma query utilizando o comando select into:

SELECT * INTO Sales.Customers_Copy from Sales.Customers

 

Nomenclatura de colunas e tabelas

Ambos, tabelas e colunas, são identificadores e precisam obedecer algumas regras de nomenclatura:

Identificadores Padrão (Standard)

  • O primeiro caracteres deve ser letra ou underscore (_).
  • Caracteres seguintes podem ser dígitos, @, $, # e _.
  • Não deve ser uma palavra reservada do T-SQL.
  • Espaços em branco não são aceitos.

 

Identificadores Delimitados

As mesmas caracteristas do Standard, com exceto que voce pode criar nomes com espaço em banco utilizando as aspas (“”) ou colchetes ([]). Precisa estar setado como ON a propriedade QUOTED_IDENTIFIER.

 

Criando a tabela

Agora vamos criar uma tabela, veja o exemplo:

CREATE TABLE HR.Employees(

EmployeeID INT IDENTITY(1000,2) NOT NULL

,FirstName NVARCHAR(50) NOT NULL

,LastName NVARCHAR(50) NOT NULL

,PhoneNumber VARCHAR(15) NULL

,BirthDate DATE NOT NULL );

Esse código cria a tabela Employees com cinco colunas. Podemos especificar o schema que será utilizado, nesse caso HR.

As colunas são definidas utilizando três propriedades básicas: nome da

coluna, tipo de dado e nullabilidade (se a coluna aceita null o ou não).

 

Nomenclatura

Ao escolher a nomenclatura de tabelas e colunas é importante seguir o padrão da organização ou algumas diretrizes. São elas:

  • Utilize PascalCasing (também conhecido como Camel Casing).
  • Evite abreviações.
  • Longos nomes que usuários entendem são preferíveis a nomes curtos que podem não ser entendidos.

 

Escolhendo Tipos de dados

A escolha do tipo de dado certo para cada coluna é muito importante, para ajudá-lo segue algumas diretrizes que você pode seguir:

  • Sempre utilize um tipo de dado que utilizar o mínimo de espaço em disco.
  • Mudar o tipo de dados posterior à criação da tabela pode ser muito custoso, não utilize um tipo de dado se o mesmo não atende as necessidades da aplicação.
  • Sempre que possível utilize tipos de dados com tamanho variável, como nvarchar e varchar.
  • Um dos poucos casos em que o char e nchar são indicados é quando o valor da coluna requer alteração constante, nesse caso o custo de mover a linha com tamanho variável é maior do que a com tamanho fixo.
  • Evite o uso dos tipos datetime e smalldatetime, pois ambos ocupam mais espaço em disco e provem menos precisão do que os tipos datetime2, date e time.
  • Utilize varchar (Max), nvarchar (Max) e varbinary (Max) ao invés de text, ntext e image, pois poderão ser descontinuados em futuras versões.
  • Use o tipo de dado rowversion ao invés de timestamp, pois poderá ser descontinuado em futuras versões.
  • Somente Utilize varchar (max), nvarchar(max) e varbinary(max) e xml, quando não há como especificar a quantidade de caracteres a ser utilizada, pois esses campos não podem ser utilizados para índices.
  • Use float e real apenas quando a precisão do decimal for insuficiente.

 

Nulo ou Não Nulo?

Você nunca deve utilizar valores no lugar do nulo (ex –1 para inteiro), isso pode causar problemas futuramente. Nulo é um valor desconhecido e será diferente de –1, em uma função de agregação, por exemplo, AVG, o –1 é incluso no cálculo e o nulo não.

 

Identity

Todas tabelas possuem uma coluna ou um conjunto de colunas que identificam a linha, a primary key . Não é fácil definir uma boa coluna para ser a primary key , pois esses dados podem variar com frequência. Para contornar essa situação você pode utilizar uma coluna que gere automaticamente uma chave. O comando IDENTITY é utilizado para determinar uma coluna por tabela onde o valor será acrescido ou decrescido automaticamente quando um valor novo é inserido, esse campo não aceita valores nulos.

Sintaxe:

CREATE TABLE HR.Employees(

EmployeeID INT IDENTITY(1000,2) NOT NULL

,FirstName NVARCHAR(50) NOT NULL

,LastName NVARCHAR(50) NOT NULL

,BirthDate DATE NOT NULL

);

A numeração irá iniciar do 1000 e será acrecido de 2 em 2,  o ser valor negativo também.

A propriedade IDENTITY  não poderá ser utilizada em um campo já existente, somente em campos novos.

Quando um insert  falha a coluna dará sequencia para o próximo valor, veja no exemplo:

clip_image002

 

Compressão

Na versão SQL Server 2008 Enterprise, você pode comprimir dados em tabelas e índices, essa compressão pode ser implementada em dois níveis: linha e página.

ALTER TABLE HR.Employees

REBUILD

WITH (DATA_COMPRESSION=PAGE);

Quando a compressão de dados é ativada, o SQL Server irá converter todos os tipos de dados para variable-length. Quando há mais tipos de dados de tamanho fixo (como Datetime2, int, decimal e char), utilize o nível de compressão por linha.

Compressão a nível de pagina inclui a compressão a nível de linha e utiliza um dicionário de paginas com os prefixos das colunas a fim de evitar redundância de dados. Considerando o exemplo abaixo:

Linha 01 | Andressa Martins

Linha 02 | Lucas Romão

Linha 03 | Andressa Martins

 

Utilizando o dicionário de pagina os dados serão armazenados assim:

Linha 01 | Andressa Martins

Linha 02 | Lucas Romão

Linha 03 | 01

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com