[SQL Server] Exame 70-461 – Tópico 4: Criando e Modificando Constraints – Parte 01

Publicado; 27/11/2012 | Autor: Adeilson Brito | Arquivado em: Exame 70-461 | Tags: exame 70-461, exame sql server 2012 | 4 Comentários

Um importante passo, que faz parte do projeto de um banco de dados, é decidir a melhor maneira de como forçar e garantir a integridade dos dados. A integridade pode ser forçada completa e unicamente na camada de aplicação, ou exclusivamente no nível da camada de dados (leia-se banco de dados), ou, ainda, através de uma abordagem híbrida (esta é a abordagem mais comumente utilizada). Discussões e polêmicas à parte, existe um mínimo de “coisas” que você deveria implementar na camada de banco, visando garantir a integridade. Iremos falar sobre isso no decorrer deste post.

Tipos de Integridade de Dados

Conceitualmente existem três tipos de integridade de dados, conforme ilustra a figura abaixo.

· Integridade de domínio (coluna): especifica o conjunto de valores válidos para uma determinada coluna, bem como se a coluna aceita nulo ou não.

· Integridade de entidade (linha): requer que cada linha seja identificada de forma única na tabela, ou seja, cada linha possui uma identificação exclusiva. Isto é normalmente chamado de chave primária (primary key).

· Integridade referencial (entre tabelas): mantém um relacionamento consistente entre a Tabela A e a Tabela B. A chave primária da Tabela A existe como uma coluna na Tabela B, onde é conceitualmente chamada de chave-estrangeira. Desta forma, não será possível inserir um valor na coluna chave-estrangeira da Tabela B que não exista na coluna chave-primária da Tabela A. Por exemplo, não será possível inserir na tabela de Vendas o código de um cliente que não exista na tabela de Clientes.

Dentro do SQL Server temos as seguintes opções para forçar e garantir a integridade de dados:

Tipo de dados	Define o tipo de dados que a coluna pode armazenar. Exemplo: DATETIME2
Nulidade	Determina se ou não um valor deve estar presente em uma coluna
Constraints	Define regras que limitam o conjunto de valores que podem ser armazenados em uma coluna ou, no nível da tabela, como os valores em diferentes colunas estão relacionados
Triggers	Define um código que é executado automaticamente quando uma tabela é modificada. Através disso podemos aplicar restrições de valores e, inclusive, regras de negócio, dentre outras coisas.

Nulidade de Coluna (NULL / NOT NULL)

Este mecanismo permite forçar a integridade de domínio. Ele define se uma coluna é obrigatória. Existem muitas bases de dados legadas por aí cuja questão da nulidade fica completamente a cargo da aplicação, enquanto que nas tabelas todas as colunas estão setadas para aceitar nulo. Este tipo de design é um risco!

Vejamos como implementar a nulidade em tabelas utilizando as opções NULL e NOT NULL. Neste ponto estou destruindo e recriando o banco DBExame70461.

use master
GO

if db_id('DBExame70461') IS NOT NULL
BEGIN
    alter database DBExame70461 set single_user with rollback immediate;
    drop DATABASE DBExame70461;
END;

CREATE DATABASE DBExame70461;
GO

USE DBExame70461;
GO

CREATE TABLE dbo.Produto
(    Id INT PRIMARY KEY,
    Numero INT,
    Nome VARCHAR(50) NOT NULL,
    Descricao VARCHAR(500) NULL
);
INSERT dbo.Produto (Id, Nome) VALUES (1, 'Melancia');

Explicitamente defini a coluna NOME como obrigatória e a coluna DESCRICAO como opcional. E a coluna NUMERO, é obrigatória? Por padrão quando não explicitamos NULL ou NOT NULL a coluna será nulável. E a coluna ID? Neste caso, a coluna foi definida como chave-primária e, por consequência, ela será implicitamente criada como NOT NULL. Por fim, na instrução INSERT eu especifiquei apenas as colunas não nuláveis (obrigatórias).

Nulo não é valor, pelo contrário, nulo indica ausência de valor.

Constraints

Como já foi mencionado, constraint é um mecanismo que permite restringir o conjunto de valores para uma dada coluna ou, no nível da tabela, como os valores de diferentes colunas estão relacionados (relacionamento entre colunas da mesma tabela ou entre colunas de tabelas diferentes). Os tipos de constraints são:

Default Values (Valor Padrão)	Integridade de domínio (coluna)
Check (Checagem / Verificação)	Integridade de domínio (coluna)
Primary Key (chave-primária)	Integridade de entidade (linha)
UNIQUE KEY (chave-única)	Integridade de entidade (linha)
Foreign Key (chave-estrangeira)	Integridade referencial (entre colunas ou tabelas)

DEFAULT Constraints

Este tipo de constraint fornece um valor padrão para uma coluna. Ter um valor padrão é um recurso útil nas instruções de INSERT. Vejamos abaixo.

USE DBExame70461;

DROP TABLE dbo.Produto;

CREATE TABLE dbo.Produto
(    Id INT PRIMARY KEY DEFAULT 1,
    Numero INT DEFAULT 77,
    Nome VARCHAR(50) NOT NULL DEFAULT 'Produto geral',
    Descricao VARCHAR(500) NULL DEFAULT 'Descrição geral'
);
GO

INSERT dbo.Produto (Id, Numero, Nome, Descricao)
VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT);

INSERT dbo.Produto (Id, Numero, Nome, Descricao)
VALUES (2, 88, 'Batata', DEFAULT);

INSERT dbo.Produto (Id)
VALUES (3);

Note que destruí e recriei a tabela. Para criar a constraint bastou adicionar a palavra reservada DEFAULT na instrução CREATE TABLE. No comando INSERT usei a palavra DEFAULT para que seja gravado o valor padrão na respectiva coluna. Na terceira instrução INSERT eu especifiquei apenas a coluna ID, omitindo as demais. Isso é válido quando as colunas omitidas são nuláveis ou possuem constraint default.

CHECK Constraints

Este tipo de constraint limita os valores que são permitidos em uma coluna. Vamos explorar algumas possibilidades.

CREATE TABLE dbo.Vendedores
(    Id INT NOT NULL PRIMARY KEY,
    Nome varchar(50) NOT NULL,
    Sexo char(1) NULL CHECK (Sexo in('M','F')) DEFAULT 'M',
    Comissao int NOT NULL
        constraint Ck_Vendedores_Comissao
        CHECK (Comissao BETWEEN 1 AND 5),
    TipoDeSalario varchar(10) NOT NULL
        CHECK (TipoDeSalario in ('Hora','Semana','Mes')),
    Telefone varchar(9) NULL CHECK (LEN(Telefone) >= 8)
);

Para criar a constraint bastou utilizar a palavra reservada CHECK na instrução CREATE TABLE. Na coluna COMISSAO, de forma explícita, informei o nome para a constraint: CK_Vendedores_Comissao. O benefício de criar uma constraint check com nome customizado é a facilidade em futuras alterações usando o comando ALTER TABLE. Note que para a coluna TELEFONE foi definida uma constraint que obriga o usuário a informar um número de telefone com pelo menos oito dígitos. Perceba, ainda, que para a coluna SEXO criei duas constraints: uma CHECK e outra DEFAULT. A figura abaixo mostra como ficou a tabela após a sua criação.

Vejamos o que acontece quando se tenta inserir, por exemplo, um registro de vendedor com uma comissão maior que 5% e com um número de telefone de 3 dígitos.

-- Tentando inserir registro com comissão acima de 5%
INSERT dbo.Vendedores (Id, Nome, Sexo, Comissao, TipoDeSalario, Telefone)
VALUES (1, 'Antonio Silva', 'M', 7, 'Mes', 123456789);

-- Tentando inserir registro com telefone de 3 dígitos
INSERT dbo.Vendedores (Id, Nome, Sexo, Comissao, TipoDeSalario, Telefone)
VALUES (1, 'Antonio Silva', 'M', 5, 'Mes', 123);

Agora, vamos falar de algo perigoso. Veja que definimos a coluna SEXO como nulável. Contudo, a constraint CHECK nessa coluna obriga informar apenas os valores M e F. Mas, o que acontecerá se tentarmos inserir um NULL nesta coluna? Devido ao fato da coluna aceitar NULL então a constraint não considera um NULL como falso, ou, em outras palavras, o NULL é simplesmente ignorado.

-- Inserindo com sucesso um NULL na coluna SEXO 
INSERT dbo.Vendedores (Id, Nome, Sexo, Comissao, TipoDeSalario, Telefone)
VALUES (1, 'Antonio Silva', NULL, 1, 'Mes', 123456789);

Imaginemos um cenário em que tenhamos de importar um arquivo texto contendo um cadastro de vendedores. Ao abrir o arquivo texto notamos que foi informado valores de comissão fora da faixa de 1 a 5 (existem comissões zeradas e comissões maiores que 5). Para fazer a importação será necessário desabilitar a constraint na coluna COMISSAO e, após a importação, reabilitá-la.

-- Desabilitando a constraint check da coluna COMISSAO
ALTER TABLE dbo.Vendedores
    NOCHECK CONSTRAINT CK_Vendedores_Comissao;

-- Inserindo valores fora da faixa de 1 a 5
INSERT dbo.Vendedores (Id, Nome, Sexo, Comissao, TipoDeSalario, Telefone)
VALUES 
(2, 'Jose', 'M', 7, 'Mes', 123456789),
(3, 'Pedro', 'M', 0, 'Mes', 123456789);

-- Habilitando a constraint check da coluna COMISSAO
ALTER TABLE dbo.Vendedores
    CHECK CONSTRAINT CK_Vendedores_Comissao;

Para eliminar uma constraint, independentemente do seu tipo (seja CHECK, DEFAULT, PRIMARY KEY, etc), faça conforme abaixo.

ALTER TABLE dbo.Vendedores
    DROP CONSTRAINT CK_Vendedores_Comissao;

Imaginemos agora o seguinte cenário (absurdo diga-se de passagem): alterar a tabela e adicionar uma constraint que não permita cadastrar, para novas inserções, nomes de vendedores contendo a palavra SILVA. Atente para o fato de que a nossa tabela de VENDEDORES já contém uma pessoa com o sobrenome SILVA. Veja:

Vamos tentar criar essa constraint e ver o que acontece.

-- Tentando criar uma constraint na coluna NOME
ALTER TABLE dbo.Vendedores
    ADD CONSTRAINT CK_Vendedores_Nome CHECK (Nome NOT LIKE '%Silva%');

O SQL Server retorna o seguinte erro:

Msg 547, Level 16, State 0, Line 1

The ALTER TABLE statement conflicted with the CHECK constraint “CK_Vendedores_Nome”. The conflict occurred in database “DBExame70461”, table “dbo.Vendedores”, column ‘Nome’.

Isso acontece porque no momento da criação da constraint o SQL Server verifica, por padrão, se existe algum valor na coluna que não atenda à regra especificada. Como o nosso cenário diz que essa regra deva ser aplicada apenas para “novas inserções”, vejamos como contornar tal situação.

ALTER TABLE dbo.Vendedores WITH NOCHECK
    ADD CONSTRAINT CK_Vendedores_Nome CHECK (Nome NOT LIKE '%Silva%');

Usamos a opção WITH NOCHECK, a qual instrui o SQL Server a criar a constraint sem fazer qualquer verificação. Desta forma, a constraint terá efeito somente para novas inserções. Exemplo:

INSERT dbo.Vendedores (Id, Nome, Sexo, Comissao, TipoDeSalario, Telefone)
VALUES (4, 'Bruno Silva', 'M', 2, 'Mes', 123456789);

Table-Level CHECK Constraints

Além de checar valores para uma coluna em particular, uma constraint CHECK pode também ser aplicada no nível da tabela para checar valores relacionados em mais de uma coluna (desde que sejam da mesma tabela). Segue exemplo.

CREATE TABLE dbo.Ferias
(    Id INT NOT NULL PRIMARY KEY,
    Funcionario VARCHAR(50) NOT NULL,
    DataDeInicio DATETIME2 NOT NULL,
    DataDeFim DATETIME2 NOT NULL,
    CONSTRAINT CK_Ferias_Periodo CHECK (DataDeInicio < DataDeFim)
);

-- Tentando inserir uma Data de Início inválida
INSERT dbo.Ferias (Id, Funcionario, DataDeInicio, DataDeFim)
VALUES (1, 'Mariana Souza', '2012-10-31', '2012-10-30');

Veja que a constraint check força a integridade, garantindo que a data de início não seja igual ou maior que a data de fim.

Conclusão

No próximo post vamos finalizar o assunto de constraints, abordando os tipos primary key, unique key e foreign key. Até lá!

---

[SQL Server] Exame 70 461 – Tópico 2: Criando e Alterando Views – Parte 4 (final)

Publicado; 10/09/2012 | Autor: Adeilson Brito | Arquivado em: Exame 70-461 | Tags: exame 70-461, exame sql server 2012 | 1 comentário

Neste artigo vamos abordar um tipo especial de view conhecida como View Indexada (note a palavra Indexada – ela faz toda a diferença).

Introdução a Índices

Antes de entrarmos no assunto de views indexadas é necessário conhecermos um pouco sobre índices. No BOL – Books Online (o help do SQL Server) encontramos a seguinte definição para índice: é uma estrutura associada com uma tabela ou view que acelera a recuperação das linhas da tabela ou view. Portanto, um índice é algo que acelera nossos SELECT’s, sendo de vital importância para o desempenho das bases de dados. Uma maneira muito simples, e interessante, para entendermos como um índice funciona é fazermos uma correlação com um livro. Imaginemos que você tem em mãos um catálogo telefônico e que deseja localizar a página onde se encontra o nome José da Silva. Você tem duas maneiras de fazer isso: a primeira é ir varrendo as páginas do catálogo, com o esforço de olhar página por página até encontrar o nome José da Silva; a segunda é consultar o índice disponível no início do catálogo e, a partir da informação contida no índice, ir diretamente para a página onde se encontram os nomes que começam com a letra J. É evidente que a segunda maneira é mais rápida! Quando uma tabela possui índices o SQL Server pode então utilizá-los para atender mais rapidamente as consultas.

No SQL Server existe os seguintes tipos de índices:

· Clustered

· Nonclustered

· XML

· Spatial

· Full-Text Search

· Column Store (introduzido no SQL Server 2012)

Alguns autores costumam citar, ainda, índices filtrados como um tipo de índice, mas, pessoalmente, prefiro considerar isso apenas como uma opção para índices Clustered ou Nonclustered. ATENÇÃO: vale destacar que o assunto de Índices não é cobrado diretamente pela grade de conteúdos da prova 70-461, mas deter o mínimo de conhecimento sobre esse assunto poderá ajudar na resolução de algumas questões. Para o tópico que estamos focando neste post, bastará entendermos o que são índices Clustered e Nonclustered.

Quando criamos um índice clustered em uma determinada tabela então as linhas de dados, daquela tabela, são fisicamente armazenadas e ordenadas conforme a chave que compõe o índice. Nota: vale esclarecer que a chave de um índice é constituída de uma ou mais colunas da tabela. Por exemplo, a chave do índice XPTA é a coluna CPF da tabela PESSOA; a chave do índice XPTO são as colunas CNPJ e DATA_COMPRA da tabela COMPRA. Como o índice clustered afeta a ordem física em que as linhas de dados são armazenadas, então só é possível termos um índice clustered por tabela. Uma tabela que possui índice clustered é conhecida como tabela clusterizada. Uma tabela sem índice clustered é conhecida como tabela heap (Heap Table).

Um índice nonclustered não afeta a ordem física em que as linhas da tabela são armazenadas. Ele é uma estrutura auxiliar, separada das linhas de dados da tabela. Na versão 2012 do SQL Server é possível criarmos até 999 índices Nonclustered (não recomendo ninguém cometer tamanha loucura em ambientes OLTP!).

Segue a sintaxe resumida para criação de índices Clustered / Nonclustered:

CREATE [UNIQUE] NONCLUSTERED INDEX NomeDoIndice
ON TabelaXYZ (ColunaA, ColunaB);

CREATE [UNIQUE] CLUSTERED INDEX NomeDoIndice
ON TabelaXYZ (ColunaA);

A expressão UNIQUE, que está entre colchetes e que é opcional, indica se o índice é único, ou seja, se o valor da chave pode ou não ser repetido no decorrer das linhas. Quando UNIQUE é omitido durante a criação então o índice não tem valor exclusivo para a chave. Perceba que a diferença na sintaxe é basicamente o uso das palavras Clustered e Nonclustered.

Views Indexadas (Indexed Views)

Como já foi comentado anteriormente, é possível criarmos índices em views, apesar de o mais usual ser a criação de índices em tabelas. Contudo, ao contrário das tabelas, o primeiro índice a ser criado obrigatoriamente em uma view é um índice do tipo Unique Clustered e a partir daí, opcionalmente, criarmos índices Nonclustered conforme necessidade.

Uma pergunta para testar a sua leitura até aqui: é possível criar um índice Nonclustered em uma view que ainda não possua índice Clustered? NÃO, não é possível.

Quando criamos um índice Unique Clustered sobre uma view o SQL Server irá “materializar” aquela view. Neste caso, o SQL Server cria uma estrutura interna que armazena o resultado (result set) da view, exatamente como se fosse uma tabela com índice clustered. Você pode pensar nessa estrutura como um tipo especial de tabela cujo esquema (colunas) é definido pela instrução SELECT da view. Uma view que possui índice Unique Clustered é conhecida como View Indexada (em outros SGDBs você encontra conceito equivalente como Views Materializadas).

Mas por que usar views indexadas? Quais os benefícios? Imagine aquela view com vários JOIN’S e agregações, referenciando tabelas de vários milhões de linhas. Uma view indexada pode, nesse cenário, acelerar, e muito, as consultas, pois, como os dados já estão persistidos numa estrutura separada, o SQL Server não precisará acessar várias tabelas, aplicar várias agregações, etc. Pelo contrário, bastará ao SQL Server resgatar as linhas diretamente na view indexada. Evidentemente que não existe almoço grátis. Existe um trade-off que precisa ser considerado antes de sairmos por aí criando views indexadas. É necessário balancear o benefício que teremos ao acelerar as consultas contra o custo na modificação de dados. Uma view indexada causa impacto sobre as modificações de dados nas tabelas referenciadas pela view. Toda vez que fizermos um INSERT / UPDATE / DELETE em alguma tabela que é referenciada por uma view indexada, o SQL Servir precisará também replicar a modificação na view indexada. Assim, um INSERT, por exemplo, acontecerá em dois lugares: uma vez na tabela e outra na view indexada.

Criação

Na criação de views indexadas é obrigatório que:

· Usemos a opção WITH SCHEMABINDING;

· Criemos o índice clustered como unique.

Agora vamos alterar a view vwProdutos, criada em post anterior desta série, tornando-a numa view indexada. O primeiro passo é alterar a view e incluir a opção SCHEMABINDING.

USE DBExame70461
GO

ALTER VIEW vwProdutos
WITH SCHEMABINDING
AS

SELECT p.ID , p.Nome AS Produto, p.CategoriaID
FROM dbo.Produto p
where p.CategoriaID = 2
GO

Agora vamos criar um unique índice clustered.

create unique clustered index vwProdutos_ID
ON dbo.vwProdutos (ID);

Veja que o índice é unique. É muito importante não esquecer esse detalhe.

Conclusão

A criação de views indexadas é simples, vez que basta incluir a opção SCHEMABINDING na sintaxe e criar um índice unique clustered na view. É vital estarmos atentos a esses detalhes, pois é quase certo que você encontre na prova 70-461 pelo menos uma questão abordando views indexadas.

---

[SQL Server] Exame 70 461 – Tópico 2: Criando e alterando views – Parte 3

Publicado; 02/09/2012 | Autor: Adeilson Brito | Arquivado em: Exame 70-461 | Tags: exame 70-461, exame sql server 2012 | Deixe um comentário

Views Atualizáveis (Updatable Views)

É possível através de uma view fazermos modificações nos dados da tabela base referenciada. Em outras palavras, podemos executar instruções UPDATE, DELETE e INSERT diretamente contra a view. Este recurso aumenta a granularidade da segurança, algo que estaremos abordando em tópico futuro. Contudo, executar modificações de dados diretamente sobre uma view é algo que possui algumas restrições:

· A modificação não pode afetar mais que uma tabela base: ou a view, em sua instrução SELECT, referencia apenas uma única tabela, ou a view possui uma trigger do tipo instead of;

· A modificação não pode afetar colunas agregadas;

· A modificação precisa preencher as colunas NOT NULL da tabela base.

Analisemos um exemplo.

use DBExame70461

go

— 1) Inicialmente criar duas tabelas: Categoria e Produto.

CREATE TABLE Categoria

( ID INT NOT NULL PRIMARY KEY,

Descricao VARCHAR(50) NOT NULL

);

CREATE TABLE Produto

( ID INT NOT NULL PRIMARY KEY,

CategoriaID INT NOT NULL,

Nome VARCHAR(50) NOT NULL,

Preco DECIMAL(15,2) NULL

);

— 2) Popular com alguns valores.

INSERT Categoria VALUES(1,’Frutas’),(2,’Carros’);

INSERT INTO Produto (ID,CategoriaID,Nome,Preco)

VALUES

(1,1,’Maçã’,null),(2,1,’Abacaxi’,2.5),

(3,2,’Tucson’,70000),(4,2,’Fiat Uno’,null);

GO

Vamos agora criar uma view que referencia essas duas novas tabelas e, em seguida, realizar uma operação de DELETE (ou pelo menos tentar).

— Cria a view

CREATE VIEW vwProdutos

AS

SELECT

p.ID

, p.Nome AS Produto — NOTE o uso de ALIAS para a coluna Nome

, c.Descricao AS Categoria — NOTE o uso de ALIAS para a coluna Descricao

, p.Preco

FROM dbo.Produto p

JOIN dbo.Categoria c ON p.CategoriaID = c.ID

GO

— Executando a view e conferindo os resultados

SELECT * FROM vwProdutos;

Agora vamos tentar excluir o registro de ID = 4 (Fiat Uno).

— Deletando o Fiat Uno…???

DELETE FROM vwProdutos WHERE ID = 4;

O SQL Server retornou erro informando que a view não é atualizável porque referencia múltiplas tabelas. Desta forma, não importa se a operação é INSERT, UPDATE ou DELETE.

Vamos alterar a definição da view, fazendo com que referencie apenas a tabela Produto. Iremos, inclusive, remover a coluna Preco (lembre-se que essa coluna não é obrigatória) e, portanto, a view será ainda atualizável.

— Alterando a definição da view

— Removeremos a coluna Preco que não é obrigatória

— Incluiremos a coluna CategoriaID que é obrigatória

— Esta modificação torna a view atualizável

ALTER VIEW vwProdutos

AS

SELECT

p.ID

, p.Nome AS Produto

, p.CategoriaID

FROM dbo.Produto p

GO

Vejamos o que acontece quando executamos o script abaixo.

INSERT INTO vwProdutos (ID, Produto, CategoriaID) VALUES (5, ‘Melancia’, 1);

DELETE FROM vwProdutos WHERE ID = 4;

SELECT * FROM vwProdutos;

Note que tudo funcionou perfeitamente. Uma nova linha foi inserida e o registro de ID = 4 foi excluído.

CHECK OPTION

Esta é uma parte opcional da sintaxe de criação de views e só tem aplicabilidade quando trabalhamos com views atualizáveis. Quando usamos CHECK OPTION o SQL Server forçará qualquer instrução de modificação de dados, executada contra a view, a seguir o filtro definido na cláusula WHERE do SELECT da view.

Vamos rever a sintaxe para a criação / alteração de views:

CREATE VIEW NomeDaView

[ WITH ENCRYPTION / SCHEMABINDING / VIEW_METADA ]

AS

SELECT * FROM Tabela

[ WITH CHECK OPTION ]

GO

Vale destacar que aquilo que aparece entre colchetes é de uso opcional na sintaxe. Atente para o detalhe de que CHECK OPTION é declarado no final da view. Vamos exemplificar o uso dessa opção, alterando a view que criamos em nossos exemplos anteriores.

ALTER VIEW vwProdutos

AS

SELECT

p.ID

, p.Nome AS Produto

, p.CategoriaID

FROM dbo.Produto p

where p.CategoriaID = 2

WITH CHECK OPTION

GO

Conforme a alteração que fizemos, a partir de agora a view retornará exclusivamente produtos da categoria de código 2. Da mesma forma, o uso de CHECK OPTION permitirá inserir/alterar/deletar somente os produtos dessa categoria.

— Tentando inserir um produto de categoria <> 2

INSERT INTO vwProdutos (ID, Produto, CategoriaID) VALUES (6, ‘Jaca’, 1);

Veja que não é possível inserir registro que não atenda ao critério especificado na cláusula WHERE.

— Inserindo um novo veículo

INSERT INTO vwProdutos (ID, Produto, CategoriaID) VALUES (6, ‘FUSCA 77’, 2);

Pronto. Registro inserido com sucesso.

Conclusão

No próximo post estarei abordando exclusivamente o assunto views indexadas e, finalmente, concluindo o tópico 2 do exame 70-461. Dentro em breve publicarei um post contendo material complementar para apoiar os estudos. Quando fecharmos o primeiro módulo – Criando Objetos de Banco de dados (Create Database Objects), disponibilizarei um pequeno simulado abordando esse módulo. Não deixe de acompanhar a série.

---

[SQL Server] Exame 70 461–Tópico 2: Criando e alterando views – Parte 2

Publicado; 26/08/2012 | Autor: Adeilson Brito | Arquivado em: Exame 70-461 | Tags: exame 70-461, exame sql server 2012 | Deixe um comentário

Dando sequência ao conteúdo do Tópico 2, vamos explorar outras opções da sintaxe de criação / alteração de Standard Views (views padrões) . Além do SCHEMABINDING, apresentado no post anterior, temos a opção ENCRYPTION, a qual ofusca o código fonte da view. Por exemplo, o código abaixo, sem a opção ENCRYPTION, deixará exposto o script de criação da view para os usuários do banco de dados AdventureWorks2012, que possuam GRANT de VIEW DEFINITION sobre o objeto.

use AdventureWorks2012
GO

CREATE VIEW [dbo].[vwEmpregadosDoSexoMasculino]
AS
SELECT
    p.FirstName AS PrimeiroNome
    , p.LastName AS UltimoNome
    , h.HireDate AS DataContratacao
FROM HumanResources.Employee h
JOIN Person.Person p
    ON h.BusinessEntityID = p.BusinessEntityID
WHERE h.Gender = ‘M’;

Para visualizar o script de criação do objeto os usuários podem recorrer ao SSMS – SQL Server Management Studio, clicando com o botão direito sobre a view, acionando a opção Script View as / CREATE To. Também é possível recuperar o script de criação usando T-SQL, conforme opções abaixo:

— Opções para visualizar o fonte da view
EXEC sys.sp_helptext ‘vwEmpregadosDoSexoMasculino’;

SELECT * from sys.sql_modules s
where s.object_id = object_id(‘vwEmpregadosDoSexoMasculino’);

Agora vamos alterar a view usando ENCRYPTION:

ALTER VIEW [dbo].[vwEmpregadosDoSexoMasculino]
with encryption
AS
SELECT
    p.FirstName AS PrimeiroNome
    , p.LastName AS UltimoNome
    , h.HireDate AS DataContratacao
FROM HumanResources.Employee h
JOIN Person.Person p
    ON h.BusinessEntityID = p.BusinessEntityID
WHERE h.Gender = ‘M’;

Qualquer usuário do banco, e até mesmo membros da server role sysadmin, não conseguirão visualizar ou recuperar o script de criação do objeto. Tente executar o código abaixo e analise o retorno.

EXEC sys.sp_helptext ‘vwEmpregadosDoSexoMasculino’;

Assim, a cláusula WITH ENCRYPTION funciona como um recurso para a proteção da definição da view. Entretanto, atente para o detalhe de que essa opção torna indisponível a ação de recuperar o código fonte até mesmo para o criador do objeto.

Segue algumas considerações quanto ao uso dessa cláusula:

1. Mantenha cópia do código fonte da view – isto é muito importante quando se usa a cláusula WITH ENCRYPTION;
2. Tenha em mente de que a opção ENCRYPTION proporciona uma ofuscação limitada – ela não é uma proteção inviolável. Muitos utilitários de terceiros, existentes “por aí”, podem quebrar a criptografia;
3. E o mais importante (sob minha ótica): encriptar código de objetos programáveis, como views, podem atrapalhar e tornar mais árduo o trabalho de diagnosticar problemas de performance, especialmente quando fazemos trace.

Limitações

Seguem algumas limitações que precisam ser consideradas na criação de standard views:

1. Máximo de 1024 colunas referenciadas;
2. Não suporta clásula INTO;
3. Não pode referenciar tabela temporária e variável de tabela;
4. Não suporta ORDER BY, exceto se usar TOP. Neste ponto existem alguns fatos interessantes sobre ordenação em views, os quais não são relevantes para o exame 70-461. Contudo, publicarei no futuro um post tratando dessas questões;
5. Uma instrução CREATE VIEW XXX precisa ser a única existente dentro de um lote (diretiva GO);
6. SELECT * somente pode ser usado na definição da view se a opção SCHEMABINDING não estiver presente.

As limitações expostas anteriormente tendem a aparecer no exame. Por isso, aprenda. Vejamos alguns casos.

CASO 1 – Avalie se a instrução abaixo é válida.

use AdventureWorks2012
go

CREATE VIEW vwCategorias
with schemabinding
AS
    SELECT * FROM Production.ProductCategory
GO

 

Se você respondeu NÃO então você está correto. A instrução não é válida porque a cláusula WITH SCHEMABINDING não permite o uso do “*”. Para criar a view usando “*” então faça conforme abaixo:

CREATE VIEW vwCategorias
AS
    SELECT * FROM Production.ProductCategory
GO

 

CASO 2 – Identifique o que está errado na instrução abaixo.

use AdventureWorks2012
GO

DROP VIEW vwCategorias;

CREATE VIEW vwProdutos
AS
select p.ProductID, p.Name
from Production.Product p;
go

 

Basta lembrar da limitação “Uma instrução CREATE VIEW XXX precisa ser a única existente dentro de um lote (diretiva GO)”. Vejamos:

Desta maneira, a forma correta para o script é:

use AdventureWorks2012
GO

DROP VIEW vwCategorias;
GO

CREATE VIEW vwProdutos
AS
select p.ProductID, p.Name
from Production.Product p;
go

 

CASO 3 – Uso do ORDER BY

Importante memorizar que somente o segundo script é válido na criação de views.

use AdventureWorks2012
GO

— Forma incorreta do uso de ORDER BY
CREATE VIEW vwVendas
AS
    SELECT h.SalesOrderID, h.OrderDate
    FROM Sales.SalesOrderHeader h
    ORDER BY h.OrderDate DESC
GO

— Forma correta do uso de ORDER BY
CREATE VIEW vwVendas
AS
    SELECT TOP 100 PERCENT h.SalesOrderID, h.OrderDate
    FROM Sales.SalesOrderHeader h
    ORDER BY h.OrderDate DESC
GO

 

Uma alternativa, particularmente interessante, é o uso da função de ranqueamento ROW_NUMBER. Veja como:

CREATE VIEW vwVendas
AS
    SELECT
        row_number() over(ORDER BY  h.OrderDate DESC) Numero,
        h.SalesOrderID,
        h.OrderDate
    FROM Sales.SalesOrderHeader h

 

Conclusão

Minha intenção inicial era abordar o “Tópico 2: Criando e alterando views”, escrevendo no máximo 2 posts. Contudo, isso não foi possível, já que existem ainda questões importantíssimas, merecedoras de artigo dedicado, e eu não desejo alongar-me na escrita . Assim, continue acompanhando a série e não perca “as cenas do próximo capítulo”.

---

[SQL Server] Exame 70 461 – Tópico 2: Criando e alterando views – Parte 1

Publicado; 23/08/2012 | Autor: Adeilson Brito | Arquivado em: Exame 70-461 | Tags: exame 70-461, exame sql server 2012 | 3 Comentários

No exame 70-461 é praticamente 100% garantido cair uma ou mais questões envolvendo views. Como se trata do exame mais básico dentre aqueles que compõem a certificação para o SQL Server 2012, será suficiente dominar a sintaxe para criar, alterar e dropar views padrões e views indexadas – existe ainda um outro tipo de view no SQL Server conhecida como Views Particionadas, as quais são apropriadamente exigidas na prova 70-464. Neste post estaremos utilizando a base de dados AdventureWorks2012 – faça o download aqui. Recomendo fortemente que você faça o download dessa base, pois ela é referenciada na maioria das questões que compõem a prova. Assim, é interessante acostumar com o modelo, nome das tabelas, campos, etc. Isso, com certeza, facilitará o entendimento dos enunciados da prova.

Views

Uma view nada mais é do que uma instrução select armazenada que é utilizada como uma tabela lógica (virtual) – ela é referenciada como se fosse uma tabela, mas fisicamente apenas a query é armazenada. O exemplo da figura abaixo, mostra uma view denominada vEmployee criada com base na tabela Employee. Veja que a view expõe apenas as colunas LastName e FirstName.

Vários são os benéfícios ao utilizarmos views. Vejamos alguns:

1. Expor somente aquilo que o usuário necessita, tanto em termos de colunas quanto de linhas, além de abstrair toda a complexidade da elaboração da query;
2. Restringir acesso – é possível conceder GRANT de SELECT somente na view, deixando as tabelas referenciadas pela view diretamente inacessíveis ao usuário (olha a granuralidade que isso proporciona!!!);
3. Reaproveitar código – imagine aquela query ad-hoc que você executa constantemente, fazendo join com múltiplas tabelas e agregações. Ao invés de ficar reescrevendo ou recuperando a instrução de arquivo fonte, que tal transformar isso numa view;
4. Aumentar reutilização de planos de execução.

Vejamos alguns exemplos de criação e alteração de views.

 

Cenário 1 – Criação

Criar a view “vwEmpregadosDoSexoMasculino” que retorne a data de contratação, o nome do cargo, o primeiro e último nome dos empregados  do sexo masculino.

ATENÇÃO: o banco AdventureWorks2012 vem configurado com o collation Latin1_General_100_CS_AS, o qual é Case e Accent Sensitive. Desta forma, existirá diferença se você digitar José de jose ou JOSE. Isso se aplica também aos nomes de objetos como tabelas, esquemas, etc. Se você tentar executar o comando SELECT * FROM Person.person, por exemplo, o SQL Server retornará o erro 208 – Invalid object name. Fique atento!!!

A sintaxe para a criação de views é:

CREATE VIEW [ schema_name . ] view_name [ (column [ ,…n ] ) ]
[ WITH <view_attribute> [ ,…n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]

Onde:

<view_attribute> ::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ]
}

Exemplos

use AdventureWorks2012
GO

CREATE VIEW vwEmpregadosDoSexoMasculino
AS

SELECT
    p.FirstName AS PrimeiroNome
    , p.LastName AS UltimoNome
    , h.HireDate AS DataContratacao
    , h.JobTitle AS Cargo
FROM HumanResources.Employee h
JOIN Person.Person p
    ON h.BusinessEntityID = p.BusinessEntityID
WHERE h.Gender = ‘M’;

go

No exemplo acima os nomes das colunas são definidos inline (dentro da própria query). É possível usarmos a alternativa abaixo:

CREATE VIEW vwEmpregadosDoSexoMasculino (PrimeiroNome, UltimoNome, DataContratacao, Cargo)
AS
    SELECT
        p.FirstName
        , p.LastName
        , h.HireDate
        , h.JobTitle
    FROM HumanResources.Employee h
    JOIN Person.Person p
        ON h.BusinessEntityID = p.BusinessEntityID
    WHERE h.Gender = ‘M’;
go

 

Se por acaso alguém alterar a estrutura da tabela Employee e remover a coluna JobTitle, o que acontecerá com a view? Hum, ela deixará de funcionar retornando um erro de binding durante a execução. Isso pode ser um inconveniente dependendo do cenário. Para evitar esse tipo de situação podemos utilizar a opção SCHEMABINDING na criação de uma view. Essa opção “prende” os objetos que a view referencia, impedindo mudanças de estruturas nos objetos enquanto a view existir (ou até que essa opção seja retirada da view). Vejamos então um exemplo usando SCHEMABINDING.

CREATE VIEW vwEmpregadosDoSexoMasculino
WITH SCHEMABINDING
AS
SELECT
    p.FirstName AS PrimeiroNome
    , p.LastName AS UltimoNome
    , h.HireDate AS DataContratacao
    , h.JobTitle AS Cargo
FROM HumanResources.Employee h
JOIN Person.Person p
    ON h.BusinessEntityID = p.BusinessEntityID
WHERE h.Gender = ‘M’;
go

DICA DE PROVA: é muito provável existir questão abordando o SCHEMABINDING. Por isso, entenda o uso dessa opção.

 

Cenário 2 – Alteração

Alterar a view vwEmpregadosDoSexoMasculino, removendo a opção schemabinding e também a coluna Cargo.

A sintaxe para alteração de views é:

ALTER VIEW [ schema_name . ] view_name [ ( column [ ,…n ] ) ]
[ WITH <view_attribute> [ ,…n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]

Onde:

<view_attribute> ::=
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]    
}

Repare que a sintaxe de alteração é praticamente idêntica à de criação – só muda de CREATE para ALTER. Isso significa que você terá que escrever o script completo da view, alterando aqueles pontos que necessitam de ajustes. Mas espera aí! Então fazer um ALTER acaba sendo a mesma coisa que dropar e criar novamente a view? ERRADO. Existe uma diferença sutil, mas essencial, nessa história. E um detalhe: é uma pegadinha para a prova! Assim eu reforço a questão: Qual a diferença entre alterar uma view e dropá-la e criá-la novamente? A diferença reside no fato de que quando você altera uma view as permissões de segurança são mantidas, enquanto que ao dropar uma view as permissões são, naturalmente, perdidas!

Vamos agora, portanto, alterar a nossa view conforme o enunciado do cenário, removendo a opção schemabinding e a coluna cargo.

ALTER VIEW vwEmpregadosDoSexoMasculino
AS
SELECT
    p.FirstName AS PrimeiroNome
    , p.LastName AS UltimoNome
    , h.HireDate AS DataContratacao
FROM HumanResources.Employee h
JOIN Person.Person p
    ON h.BusinessEntityID = p.BusinessEntityID
WHERE h.Gender = ‘M’;
go

 

Conclusão

No próximo post da série estarei falando sobre as limitações das views, bem como sobre views indexadas, encerrando o tópico 2.

 

Questão de revisão do Tópico 1

No post: Tópico 1 – Criando e alterando tabelas, nós criamos a tabela Empregado no banco DBExame70461. Propositalmente não mencionei como remover uma coluna de uma tabela via T-SQL. Assim, quero deixar a questão abaixo, propondo um simples desafio aos que estão acompanhando a série.

Cenário

Alterar a estrutura da tabela Empregado, do banco DBExame70461, removendo a coluna Email. Gere o script e cole nos comentários deste post. Estarei acompanhando e postando a resposta em breve.

---