T-SQL e a utilização de Views

Podemos definir uma view como apenas uma tabela virtual composta por linhas e colunas. Os dados podem vir a partir de uma única tabela ou podem provir de muitas tabelas. Ela é criada para facilitar a visualização de dados que iremos precisar com uma maior frequência que outros. A exibição é criada utilizando-se a instrução CREATE VIEW e é armazenada no banco de dados em que ela for criada. Abaixo listamos algumas das razões pelas quais precisamos utilizar Views, que são:

• Quando não desejamos expor todas as colunas de uma tabela de usuários;
• Quando seu esquema de banco de dados é complexo, onde é necessário a criação de uma view a fim de simplificar o acesso do usuário;
• Quando queremos mudar o esquema de banco de dados, mas queremos manter a compatibilidade com versões anteriores para que o código existente não tenha que ser reescrito.

A melhor maneira de obter uma melhor compreensão de como usarmos as views é realizando alguns testes através de exemplos de utilização das views para as diferentes necessidades de negócios que possamos encontrar.

Criando nossa base de testes

A fim de demonstrarmos como funcionam as views e como elas podem simplificar o código T-SQL, precisaremos de alguns dados de teste para esses pontos de view. Ao invés de criarmos nossos próprios dados de teste, a maioria dos exemplos usará o banco de dados AdventureWorks2012. Caso queiram acompanhar e executar os exemplos apresentados aqui em seu ambiente, então é necessário baixar o banco de dados AdventureWorks2012_database através do link https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2012.bak

Simplificando o código SQL usando um modo de exibição (Views)

Usando uma view podemos listar colunas como um subconjunto de colunas da tabela, um conjunto de colunas que vêm de várias tabelas, um conjunto limitado de colunas com base em alguns critérios ou uma série de outros requisitos diferentes que possam ser retornados. Nesta seção forneceremos diferentes exemplos de como usar views a fim de atender diferentes necessidades de negócios.

Para o nosso primeiro exemplo vamos supor que se tenha um requisito no projeto de não apresentar todas as colunas em uma única tabela em um aplicativo ou uma consulta ad hoc. Para este exemplo vamos então supor que se deseja retornar apenas informações não pessoais da tabela HumanResource.Employee, apresentada pelo código da Listagem 1. Percebam que esta tabela já pertence a esta base de dados e que possui vários registros e com várias informações adicionais, das quais precisamos apenas das informações não pessoais.

Listagem 1. Estrutura da tabela HumanResources.Employee.

  CREATE TABLE [HumanResources].[Employee](
                  [BusinessEntityID] [int] NOT NULL,
                  [NationalIDNumber] [nvarchar](15) NOT NULL,
                  [LoginID] [nvarchar](256) NOT NULL,
                  [OrganizationNode] [hierarchyid] NULL,
                  [OrganizationLevel]  AS ([OrganizationNode].[GetLevel]()),
                  [JobTitle] [nvarchar](50) NOT NULL,
                  [BirthDate] [date] NOT NULL,
                  [MaritalStatus] [nchar](1) NOT NULL,
                  [Gender] [nchar](1) NOT NULL,
                  [HireDate] [date] NOT NULL,
                  [SalariedFlag] [dbo].[Flag] NOT NULL,
                  [VacationHours] [smallint] NOT NULL,
                  [SickLeaveHours] [smallint] NOT NULL,
                  [CurrentFlag] [dbo].[Flag] NOT NULL,
                  [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
                  [ModifiedDate] [datetime] NOT NULL); 

As informações não-pessoais que os aplicativos e usuários ad hoc requerem são as seguintes colunas: BusinessEntityID, NationalIDNumber, LoginID, OrganizationNode, OrganizationLevel, jobTitle e HoreDate. A fim de criarmos uma view que só retorne um subconjunto de colunas da tabela apresentada, utilizaremos o código presente na Listagem 2.

Listagem 2. Criando view das informações não-pessoais da tabela HumanResources.Employee.

CREATE VIEW [HumanResources].[EmployeeInfo]
  AS
                  SELECT [BusinessEntityID] 
                        ,[NationalIDNumber]  
                        ,[LoginID]  
                        ,[OrganizationNode]               
                        ,[OrganizationLevel]
                        ,[JobTitle] 
                        ,[HireDate]  
                        ,[CurrentFlag]  
      FROM [HumanResources].[Employee];

Ao analisarmos a instrução CREATE VIEW a partir da Listagem 2, podemos ver que é muito simples o que queremos fazer. O código para o ponto de view que queremos é apenas uma instrução SELECT simples que contém as colunas que queremos apresentar nos critérios de seleção. Uma vez criada a view, ela pode ser consultada sempre que precisarmos, como uma tabela normal da base de dados. O script presente na Listagem 3 demonstra duas instruções SELECT diferentes que recuperam dados da tabela HumanResources.Employee usando o ponto de view que criamos com o código da Listagem 2.

Listagem 3. Duas instruções SELECT que retornam dados usando a view criada.

SELECT * FROM [HumanResources].[EmployeeInfo];
  SELECT * FROM [HumanResources].[EmployeeInfo]
                  WHERE JobTitle like '%Manager%'; 

Ao analisarmos o código da Listagem 3 podemos ver que o objeto referenciado após a cláusula FROM é o nome da view que criamos anteriormente na Listagem 2. A primeira instrução SELECT na Listagem 3 retornou todas as linhas da tabela HumanResources.Employee, mas retornando as colunas não-pessoais na cláusula SELECT dentro da view. A segunda instrução SELECT na Listagem 3 demonstra ainda que podemos restringir as linhas retornadas usando uma instrução WHERE, assim como faríamos ao fazer referência a uma tabela.

Algumas vezes não trabalhamos de forma tão simples quanto a que vimos anteriormente. As vezes é necessário a manipulação de mais de uma tabela do banco utilizando Joins e também uma view. A partir dos dados coletados destas tabelas para uma view, podemos recuperar dados desta view de forma similar a anterior, passando parâmetros. Ao fazer isso, podemos simplificar o código para consultar o banco de dados e esconder a complexidade de um projeto de banco de dados dentro de uma view. Para demonstrarmos isso e clarearmos um pouco do conceito, criemos uma view, como a da Listagem 4, que recupera os dados de pedidos de vendas contidos em várias tabelas do banco.

Listagem 4. View que possui dados de várias tabelas com a utilização de Joins.

CREATE VIEW SalesOrderCombined2007
  AS
  SELECT 
                   OH.SalesOrderID
                  ,OH.OrderDate
                  ,OH.ShipDAte
                  ,ST.Name AS TerritoryName
                  ,BTA.City AS BillToCity
                  ,STA.City AS ShiptToCity
                  ,OH.TotalDue
  FROM Sales.SalesOrderHeader OH
                   JOIN Sales.SalesTerritory ST
                   ON OH.TerritoryID = ST.TerritoryID
                   JOIN Person.Address BTA
                   ON OH.BillToAddressID = BTA.AddressID
                   JOIN Person.Address STA 
                   ON OH.ShipToAddressID = STA.AddressID 
  WHERE YEAR(OH.OrderDate) = 2007; 

A view SalesOrderCombined2007 que acabamos de gerar na Listagem 4 junta uma série de tabelas em conjunto e retorna apenas um subconjunto de colunas a partir dessas tabelas. Além disso, a view tem uma cláusula WHERE. Ela retorna apenas os dados se ela está relacionada com uma ordem de venda que foi colocada no ano de 2007. Essa view criada elimina a necessidade de compreendermos como é a participação de um número de tabelas em conjunto, utilizando diferentes colunas chaves. Ao executarmos uma instrução SELECT contra a view SalesOrderCombined2007, todos os joins são feitos sem que vocês precisem fazer referência a eles em sua instrução SELECT. Adicionando este tipo de view em nossas consultas a base de dados, podemos eliminar as possibilidades de escrever joins como critérios incorretamente.

Há momentos em que precisamos evoluir nosso projeto de banco de dados ao longo do tempo, mas não queremos quebrar o código já existente. Uma view pode controlar esta exigência de negócio. Para demonstrar isso, vejamos o código da Listagem 5.

Listagem 5. Estrutura antiga e nova de schema.

--- Iniciando Schema antigo
  CREATE TABLE DateDimOld (
  ID INT IDENTITY, 
  DT DATE, 
  DOWTitle varchar(10));
  GO
  -- Populando DateDimOld
  INSERT INTO DateDimOld(DT, DOWTitle) VALUES 
    ('12/1/2013',DATENAME(DW,'12/1/2013')),
    ('12/2/2013',DATENAME(DW,'12/2/2013')),
    ('12/3/2013',DATENAME(DW,'12/3/2013'));
  GO
  SELECT * FROM DateDimOld;
  GO
  --- Encerrando Schema antigo
  --  Iniciando Schema novo
  CREATE TABLE DOWTitle (
  DowTitleID INT IDENTITY PRIMARY KEY, 
  DOWTitle VARCHAR(10));
  GO
  CREATE TABLE DateDimNew (
  ID INT IDENTITY, 
  DT DATE, 
  DOWTitleID INT);
  GO
  ALTER TABLE DateDimNew  WITH CHECK ADD  CONSTRAINT [FK_DateDimNew_DOWTitle_DOWTitleID] FOREIGN KEY(DOWTitleID)
  REFERENCES DOWTitle (DOWTitleID)
  GO
  -- Populando DOWTitle
  INSERT INTO DOWTitle (DOWTitle) VALUES 
    (DATENAME(DW,'12/1/2013')),
    (DATENAME(DW,'12/2/2013')),
    (DATENAME(DW,'12/3/2013'));
  GO
  -- Populando DateDimNew
  INSERT INTO DateDimNew (DT,DOWTitleID) VALUES
    ('12/1/2013', 1),
    ('12/2/2013', 2),
    ('12/3/2013', 3);
  GO
  -- Removendo Schema antigo
  DROP TABLE DateDimOld
  GO
  -- Criando view similar ao Schema antigo
  CREATE VIEW DateDimOld AS
  SELECT DDN.ID, DDN.DT, DOWT.DOWTitle 
                  FROM DateDimNew AS DDN
                  JOIN DOWTitle AS DOWT
                  ON DDN.DOWTitleID = DOWT.DowTitleID;
  GO
  -- Apresentando VIEW e simulando o Schema antigo
  SELECT * FROM DateDimOld
  -- Encerrando o Schema novo. 

Ao analisarmos o código presente na Listagem 5 veremos que existem duas seções diferentes de código. Na primeira seção estão definidos, povoados e exibidos alguns dados de um esquema antigo que tem uma única tabela chamada DateDimOld. Esta tabela contém uma coluna de data chamado DT com um dia de semana e a coluna chamada DOWTitle é associada a essas colunas para uma coluna de identificação. Na segunda seção definimos um novo esquema para substituir o esquema antigo em uma seção. Nesta segunda seção criamos duas tabelas: a primeira tabela é denominada DOWTitle que contém as colunas DOWTitle e DOWTitleID; e a segunda tabela é nomeada DateDimNew. Esta tabela contém colunas de identidade, DT e DOWTitleID. A coluna DOWTitleID é uma coluna de chave estrangeira na tabela de DOWTitle. Este novo esquema é um esquema normalizado, enquanto o velho esquema é um esquema desnormalizado. Na segunda seção do código realmente deletamos a tabela criada na primeira seção do código e criamos uma view com o mesmo nome DateDimOld. A view DateDimOld nos permite consultar o novo esquema normalizado, assim como teríamos que fazer ao consultar a tabela de DateDimOld no esquema antigo. Esta nova view DateDimOld nos permitiu fornecer compatibilidade com versões anteriores para qualquer código que poderíamos ter construído, que usa o design do esquema antigo.

Como vocês podem ver aqui, há uma série de maneiras diferentes de expormos as views de modo que elas possam ser usadas. Nos nossos exemplos aqui estamos apenas mostrando a seleção de dados em uma view. As views também podem ser usadas para atualizar as tabelas. Além disso, existem outras opções que podem ser usadas na criação de views. Vejamos a seguir.

Updates de Tabelas com Views

Como mencionado anteriormente, uma view pode ser usada para atualizar dados em tabelas também. Para que possamos demonstrar isso, vamos executar o código presente na Listagem 6

Listagem 6. Inserindo dados em tabelas usando uma VIEW.

INSERT INTO DateDimOld (DOWTitle) 
  VALUES (DATENAME(DW,'12/4/2013')); 

O código apresentado na Listagem 6 realmente não atualizará a tabela DateDimOld (que foi excluída de qualquer maneira anteriormente), mas em vez disso, irá atualizar o DOWTitle da tabela subjacente que faz parte da definição da view para DateDimOld. Depois de executarmos a instrução INSERT na Listagem 6, uma linha foi criada na tabela DOWTitle que contém o valor "Wednesday" na coluna DOWTitle. Desde que o DateDimOld é uma view normalizada referente a nossas tabelas, também precisaremos colocar outra linha na tabela DateDimNew para que a view DateDimOld exiba o valor "Wednesday". Para fazermos isso, executaremos então o código da Listagem 7.

Listagem 7. Adicionando nova linha na tabelaDateDimNew.

INSERT INTO DateDimNew (DT, DOWTitleID) 
     SELECT '12/4/2013', DOWTitleID FROM DOWTitle 
         WHERE DOWTitle = DATENAME(DW,'12/4/2013'); 

Devido ao fato de que a coluna DOWTitleID não era parte da view DateDimOld, não eramos capazes de usar a view para atualizar a tabela de DateDimNew. Em vez disso, tínhamos que escrever o código da Listagem 7 para referenciar a tabela view subjacente diretamente.

Existem algumas limitações no uso de views para atualização das tabelas subjacentes de uma view. Elas serão passadas a seguir:

• Apenas uma única tabela base em uma view pode ser atualizada;
• Colunas sendo atualizadas devem ser diretamente referenciadas na view, sem qualquer cálculo sobre elas;
• Colunas sendo modificadas não devem ser afetadas por um GROUP BY, DISTINCT, ou cláusula HAVING;
• Sua view não contém uma cláusula TOP, quando o CHECK OPTION (mais sobre esta opção abaixo) é utilizado.

Nas demonstrações com CREATE VIEW, que apresentamos até agora, as views criadas não irão limitar o que podemos fazer para as tabelas subjacentes. Há algumas mudanças que vocês podem fazer com as tabelas subjacentes que usa uma view que pode quebrar a view, ou retornar resultados inesperados. Uma dessas mudanças que iria quebrar uma view seria para apagar uma coluna que uma view faz referência. Há situações em que vocês podem querer certificar-se de que suas views são imunes a esses tipos de problemas. Quando criamos uma view, há algumas cláusulas adicionais que você podemos colocar no CREATE VIEW ou a instrução SELECT que vai ajudar a eliminar esses problemas potenciais irritantes.

A primeira coisa que vocês podem fazer é ligar a sua view aos esquemas de tabela subjacentes. Ao vincular suas tabelas para os esquemas subjacentes, vocês irão restringir as alterações de tabela que podem quebrar a sua view. Para demonstrarmos, executaremos o código presente na Listagem 8.

Listagem 8. Criando uma view com associação de esquema.

ALTER VIEW DateDimOld WITH SCHEMABINDING AS 
  SELECT DDN.ID, DDN.DT, DOWT.DOWTitle 
                  FROM dbo.DateDimNew AS DDN
                  JOIN dbo.DOWTitle AS DOWT
                  ON DDN.DOWTitleID = DOWT.DowTitleID;
  GO

Na Listagem 8 descartamos e recriamos a view DateDimOld. Quando recriamos, adicionamos a cláusula WITH SCHEMABINDING. Isto criou uma exibição de esquema associada. Quando fizemos essa mudança que também tivemos que modificar a instrução SELECT um pouco na view. A mudança que fizemos foi ter dois nomes para todas as tabelas. Recomenda-se que elas sempre usem duas nomenclaturas ao fazer referência a tabelas do SQL Server, independentemente do SQL Server exigir ou não. Esta exigência significava que tínhamos a acrescentar é o "dbo." na frente dos dois nomes de tabelas na nossa view original. Fora isso, esta view é exatamente como ela era originalmente. Para mostrarmos como a utilização de do schema limita o que podemos fazer para a tabela subjacente, deixe-nos executar o código da Listagem 9.

Listagem 9. Tentando alterar a tabela com schema limitado.

ALTER TABLE dbo.DateDimNew 
    ALTER COLUMN DT INT;

Ao analisarmos a saída desta alteração veremos que fomos impedidos de modificar a coluna DT, que está incluído na definição da view. Através da criação de uma view associada ao esquema, temos então a certeza que alguém não virá e modificará qualquer parte das tabelas que podem afetar nossa view DateDimOld.

Outra opção disponível com a criação de uma view é o WITH CHECK OPTION. A verificação da opção WITH permite colocar restrições na sua finalização a fim de certificar-se de qualquer atualização feita às tabelas subjacentes são selecionáveis ​​através de uma view. Para mostrarmos como o WITH CHECK OPTION é usado, apresentaremos um exemplo do código na Listagem 10.

Listagem 10. Criando uma view que possui WITH CHECK OPTION.

CREATE TABLE DayOfTheWeek(DayOfTheWeek varchar (10), 
                DayOfTheWeekNum int);
  INSERT INTO  DayOfTheWeek VALUES
      ('Monday',0),
      ('Tuesday',1),
      ('Wednesday',2),
      ('Thursday',3),
      ('Friday',4);
  GO
  CREATE VIEW DisplayDayOfTheWeek 
  AS 
  SELECT DayOfTheWeek, DayOfTheWeekNum FROM DayOfTheWeek
  WHERE DayOfTheWeekNum < 5
                  WITH CHECK OPTION;

No código da Listagem 10 podemos ver que criamos e preenchemos uma tabela chamada DayoftheWeek. Também criamos uma view nomeada DisplayDayOfTheWeek que restringe os dias retornados usando uma cláusula WHERE, e adicionamos o WITH CHECK OPTION. Ao adicionarmos o WITH CHECK OPTION, o SQL Server não nos permite inserir ou atualizar uma linha usando a view DisplayDayOfTheWeek a menos que o valor de DayOfTheWeekNum seja inferior a 5. Para testarmos isso podemos executar o código na Listagem 11.

Listagem 11. Código para testar a utilização de WITH CHECK OPTION.

INSERT INTO  DisplayDayOfTheWeek VALUES
      ('Saturday',5);
  UPDATE DisplayDayOfTheWeek
  SET DayOfTheWeekNum = 5
  WHERE DayOfTheWeek = 'Friday';

Quando o código na Listagem 11 tenta inserir uma nova linha com um valor superior a 5, ou atualizar a linha existente friday para um valor DayOfTheWeekNum maior que 5, recebemos um erro. Na verdade, irá gerar esta mensagem duas vezes, uma para o INSERT e uma vez para o UPDATE.

Ao analisarmos a mensagem que vocês podem ver que o WITH CHECK OPTION causou a nossa declaração de inserção e atualização na Listagem 11 podemos ver o seguinte, se quisermos realmente inserir ou atualizar essas linhas, devemos ter duas opções. Um opção seria a de remover o WITH CHECK OPTION. Isso permite que alteremos a tabela subjacente através da view, mas a seleção da view ainda não mostraria os valores que atenderiam as condições da definição da view. Se você quer inserir e atualizar as linhas e tê-los exibidos pela view, em seguida, a segunda opção que seria mudar a condição WHERE no fim para permitir que os novos valores sejam selecionados. Tenham em mente que o WITH CHECK OPTION só se aplica às mudanças feitas através da view; isso não impede atualizações ou inserções feitas diretamente para a tabela subjacente. Se vocês quiserem controlar o tipo de declarações que podem ter impacto nas suas views, então vocês devem considerar o uso de associação de esquema e/ou o WITH CHECK OPTION.

Considerações sobre o desempenho ao usarmos Views

Existem problemas de desempenho com o uso de views? Da mesma forma como em tudo na vida, existem exceções a regras. A utilização de views não poderia ser diferente. O desempenho de uma view vai depender do que a view está fazendo. Uma view simples que lê uma única tabela sem cláusulas JOIN provavelmente irá realizar de forma muito semelhante a uma consulta que faz referência a apenas uma única tabela. Mas se você tem uma view que faz referência a uma view que faz referência a uma outra view, e essas views contenham várias cláusulas JOIN? A consulta subjacente de que, na verdade, é executada por sua instrução SELECT simples que faz referência a uma view poderia explodir para fora em uma instrução SELECT muito complexa, com várias cláusulas JOIN e poderia acabar fazendo um trabalho muito maior do que o esperando.

Uma outra coisa que vale a pena mencionar sobre problemas de desempenho com views é quando uma view contém uma série de tabelas unidas, mas que você só quer retornar dados de uma única tabela no modo de exibição. Neste caso, o SQL Server ainda terá que juntar todas as tabelas na exibição para retornar os dados de uma única tabela. Isso pode levar a um trabalho extra para o SQL Server para se juntar todas essas tabelas na view e tempo de resposta mais lento para as consultas que só querem retornar dados de uma única tabela no modo de exibição. Se você achar que você está indo só para retornar dados de uma única tabela em uma view e desempenho é importante, então seria melhor escrever sua consulta para usar uma tabela única em vez de usar uma exibição que contém junção de várias tabelas.

Views são uma ótima maneira de simplificar o código e ocultar a complexidade do esquema de banco de dados. Mas esconder essa complexidade pode causar sérios problemas de performance. Se vocês pretendem usar views, verifiquem se vocês sabem o que a view está fazendo nos bastidores. Compreender o trabalho que o mecanismo da query terá que fazer para executar consultas em seus pontos de vista vai ajudá-los a desenvolver um código que funciona bem.

Protegendo dados usando uma View

Outra razão das pessoas usarem uma view é o de garantir o acesso a algumas colunas de uma tabela. Suponhamos que tenhamos um requisito de negócio para permitir aos usuários fazer relatórios a partir de tabelas que contenham dados confidenciais, como um número de segurança social, ou o número do cartão de crédito. Vocês não podem querer que eles tenham acesso a essas colunas confidenciais. Uma maneira de se certificar de que eles não podem ler estas colunas de dados confidenciais é criar uma view da tabela que exclui as colunas confidenciais, e não fornecem aos usuários direitos SELECT na tabela subjacente.

Neste artigo tratamos com relação a utilização de views e vimos que elas são uma ótima maneira de implementarmos a segurança, simplificarmos a consulta complexa de esquemas de banco de dados, dentre outros recursos. Mas há um lado negro na utilização das views, se vocês começarem a utilizar views sem entender a sua utilização, o impacto no desempenho poderá ser prejudicial. Como vocês estão olhando para uma determinada necessidade de negócio que requer uma solução T-SQL, considerem uma view como apenas uma das muitas ferramentas que vocês podem ser capazes de usar para implementar suas soluções. Até a próxima!