Opções de Criação de uma View e Entendimento de Erros Gerados

Posted on junho 3, 2011

3


A View nada mais é do que visualizações que são criadas para encapsular o código e a complexidade das consultas, no BOL temos a seguinte definição:

“Creates a virtual table that represents the data in one or more tables in an alternative way.CREATE VIEW must be the first statement in a query batch. ”

No momento da criação/alteração de uma view, temos opções na qual podemos utilizar, elas impactaram no comportamento da view, e se não utilizadas podem dar bastante dor de cabeça, vamos entender agora cada uma e ver um cenário de aplicação para elas.

Vamos criar uma tabela e depois inserir alguns dados nela.

USE tempdb

go

IF OBJECT_ID(‘Clientes’,‘U’)IS NOT NULL

      DROP TABLE Clientes

 

–Criação da Tabela

CREATE TABLE Clientes

(

    ID INT IDENTITY(1,1) NOT NULL

  , Nome VARCHAR(20) NOT NULL

  , CPF VARCHAR(14) NOT NULL

  , DataNascimento DATE NOT NULL

  , Sexo CHAR(12) NOT NULL

  , EstadoCivil VARCHAR(20) NOT NULL

  , Profissao VARCHAR(50) NULL

  , UF CHAR(2) NULL

)

GO

 

ALTER TABLE Clientes

ADD CONSTRAINT PK_Clientes_ID

PRIMAR YKEY (ID)

GO

 

ALTER TABLE Clientes

ADD CONSTRAINT UNQ_Clientes_Nome_CPF_DataNascimento

UNIQUE (Nome, CPF, DataNascimento)

GO

–Inserção de Dados Básicos

INSERT INTO Clientes(Nome, CPF, DataNascimento, Sexo, EstadoCivil, Profissao, UF)

VALUES (‘Luan.Moreno’,‘022.366.551-77’,‘1988-07-20’,‘Masculino’,‘Solteiro’,‘DBA’,‘DF’),

         (‘GB’,‘551.223.542-12’,‘1980-12-12’,‘Masculino’,‘Casado’,‘Desenvolvedor’,‘SP’),

         (‘Luciano.Caixeta’,‘222.341.231-12’,‘1982-03-12’,‘Masculino’,‘Casado’,‘Consultor’,‘DF’),

         (‘Vitor.Meriat’,‘123.332.511-17’,‘1990-01-20’,‘Masculino’,‘Casado’,‘Desenvolvedor’,‘MG’)

SELECT * FROM Clientes

 

image

 

Agora podemos ver as opções disponíveis na criação de uma view.

Opções:

* Schemabinding – Essa opção faz com  que a referência que a view faz das tabelas envolvidas não seja alterada ou perdida, por exemplo se vocês tentar excluir  uma campo da tabela na qual uma view está sendo referenciada, o SQL Server mostrará um erro, no BOL temos a seguinte definição:

“Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.”

1 – Criação sem Schemabinding

CREATE VIEW vw_EmpregadosSrNimbus

AS

SELECT C.Nome AS Nome

        ,C.DataNascimento AS DataNascimento

        ,C.CPF AS CPF

        ,C.EstadoCivil AS EstadoCivil

        ,C.Sexo AS Sexo

        ,C.UF

FROM Clientes AS C

 

Porém, se por acaso alguêm, ou algum processo deletar ou alterar a estrutura da tabela dos campos, quando tentarmos acessar a tabela teremos:

 

Msg 208, Level 16, State 1, Procedure vw_EmpregadosSrNimbus, Line 9
Invalid object name ‘Clientes’.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function ‘vw_EmpregadosSrNimbus’ because of binding errors.

Isso acontece porque não temos na definição dessa view nenhuma cláusula ou opção que restrinja isso.

 

2 – Criação com Schemabinding

CREATE VIEW vw_EmpregadosSrNimbus

WITH SCHEMABINDING

AS

SELECT C.Nome AS Nome

        ,C.DataNascimento AS DataNascimento

        ,C.CPF AS CPF

        ,C.EstadoCivil AS EstadoCivil

        ,C.Sexo AS Sexo

        ,C.UF

FROM dbo.Clientes AS C

 

Agora se tentarmos excluir a tabela temos:

Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE ‘Clientes’ because it is being referenced by object ‘vw_EmpregadosSrNimbus’.

Como a view faz referencia para esse objeto o mesmo não pode ser excluído.

Porque Usar?

  • Esta opção é sempre uma boa prática, porque sempre temos que garantir que  a view criada esteja sempre dispoível para o cliente, imagine um cliente acessar a view e ter um erro deste tipo.
  • Use sempre para garantir que a estrutura da sua view esteja protegida.

* Encryption – Essa opção faz com que a view seja criptografada, no BOL temos a seguinte definição:

Encrypts the entries in sys.syscomments that contain the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

1 – Criação sem Encryption

CREATE VIEW vw_EmpregadosSrNimbus

AS

SELECT C.Nome AS Nome

        ,C.DataNascimento AS DataNascimento

        ,C.CPF AS CPF

        ,C.EstadoCivil AS EstadoCivil

        ,C.Sexo AS Sexo

        ,C.UF

FROM Clientes AS C

 

sp_helptext‘vw_EmpregadosSrNimbus’

 

Se tentarmos ver o texto da view, iremos conseguir pois não temos nenhuma opção imposta para que isso não aconteça

 

 

Texto Gerado:

CREATE VIEW vw_EmpregadosSrNimbus

AS

SELECT C.Nome AS Nome

        ,C.DataNascimento AS DataNascimento

        ,C.CPF AS CPF

        ,C.EstadoCivil AS EstadoCivil

        ,C.Sexo AS Sexo

        ,C.UF

FROM Clientes AS C

 

 

2 – Criação com Encryption 

 

CREATE VIEW vw_EmpregadosSrNimbus

WITH ENCRYPTION

AS

SELECT C.Nome AS Nome

        ,C.DataNascimento AS DataNascimento

        ,C.CPF AS CPF

        ,C.EstadoCivil AS EstadoCivil

        ,C.Sexo AS Sexo

        ,C.UF

FROM dbo.Clientes AS C

Agora se tentarmos ver o texto da view, temos:

sp_helptext‘vw_EmpregadosSrNimbus’

The text for object ‘vw_EmpregadosSrNimbus’ is encrypted.

Porque Usar?

  • Use sempre para manter a integridade e segurança do seu código.
  • Em ambientes de missão crítica essa opção é essencial para que nada seja visto por pessoas não autorizadas.

* Check Option – Essa opção faz com que qualquer modificação de registro feita sobre a view seja obrigatoriamente visível por ela. Ou seja, se a inserção de um ou mais registros não aparecer na view então a inserção dos dados não será efetivada, no BOL temos:

Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

1 – Criação sem Check Option

CREATE VIEW vw_EmpregadosSrNimbus

AS

SELECT C.Nome AS Nome

        ,C.DataNascimento AS DataNascimento

        ,C.CPF AS CPF

        ,C.EstadoCivil AS EstadoCivil

        ,C.Sexo AS Sexo

        ,C.UF

FROM Clientes AS C

WHERE C.UF =‘DF’

Temos os seguintes dados na tabela:

image

 

Aqui estou pegando somente os empregados que estão no DF, sendo assim irei inserir um novo registro e vamos ver o resultado

INSERT INTO vw_EmpregadosSrNimbus(Nome, CPF, DataNascimento, Sexo, EstadoCivil, UF)

VALUES (‘Fabricio.Braz’,‘012.421.545-77’,‘1985-02-02’,‘Masculino’,‘Casado’,‘MG’)

 

 

image

 

 

A inserção do novo registro não apareceu na view, porque não temos nehuma validação neste casso, aqui realizei a inserção de uma UF = MG pela view, só que na definição do filtro é somente por UF = DF.

 

2 – Criação com Check Option

CREATE VIEW vw_EmpregadosSrNimbus

AS

SELECT C.Nome AS Nome

        ,C.DataNascimento AS DataNascimento

        ,C.CPF AS CPF

        ,C.EstadoCivil AS EstadoCivil

        ,C.Sexo AS Sexo

        ,C.UF

FROM Clientes AS C

WHERE C.UF =‘DF’

WITH CHECK OPTION

Se agora tentarmos inserir o registro anterior com a opção Check Option especificada, temos:

INSERT INTO vw_EmpregadosSrNimbus(Nome, CPF, DataNascimento, Sexo, EstadoCivil, UF)

VALUES (‘Fabricio.Braz’,‘012.421.545-77’,‘1985-02-02’,‘Masculino’,‘Casado’,‘MG’)

 

Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

Isso porque a UF do registro que seria inserido, não bate com o filtro especificado na view.

Porque Usar?

  • Sempre que for necessário utilizar view’s para atualizar ou inserir novos registros

Espero ter esclarecido esse assunto…

Abs.

Posted in: Script's