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

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”.

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.

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:

Agora vamos alterar a view usando ENCRYPTION:

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.

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.

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:

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

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

IMAGEM – image

Desta maneira, a forma correta para o script é:

CASO 3 – Uso do ORDER BY

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

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

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”.