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

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.

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.

IMAGEM – image

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 é:

Onde:

Exemplos

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

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.

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 é:

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 é:

Onde:

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.

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.