SQLSERVERLEARNER

Advanced topics in SQL Server Views:
CHECK OPTION
ENCRYPTION
SCHEMABINDING
VIEW_METADATA

Creating Sample Data:

--DROP TABLE tempForView
CREATE TABLE tempForView(
ID INT
);
GO

INSERT INTO tempForView
SELECT 1
UNION
SELECT 2
UNION
SELECT 3;
GO

Check Option:
Check option ensures that the modification of data in the view will come up in the view again.

Example:

CREATE VIEW vw_TempWithOutCheckOption
AS
SELECT * fROM tempForView WHERE ID > 0
GO

CREATE VIEW vw_TempWithCheckOption
AS
SELECT * fROM tempForView WHERE ID > 0
WITH CHECK OPTION;
GO

Lets try to inset/Update data in view vw_TempWithOutCheckOption.

UPDATE vw_TempWithOutCheckOption SET ID = -1 WHERE ID = 1
GO

INSERT INTO vw_TempWithOutCheckOption(ID)
SELECT -2
GO

SELECT * FROM vw_TempWithOutCheckOption
GO

The above query runs and the data will be inserted/Updated in the underlying table but will not come up in the view.

Now when we try to insert update data in the view vw_TempWithCheckOption

UPDATE vw_TempWithCheckOption SET ID = -2 WHERE ID = 2
GO

INSERT INTO vw_TempWithCheckOption(ID)
SELECT -3
GO

SELECT * FROM vw_TempWithCheckOption
GO

As we are trying to insert/Update data which would not be part of the view, we are getting the error:

Msg 550, Level 16, State 1, Line 2
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.

Encryption:

Encryption can be used when you want to hide the definition of the View.

CREATE VIEW vw_TempWithEncryption
WITH ENCRYPTION
AS
SELECT * fROM tempForView WHERE ID > 0;
GO

CREATE VIEW vw_TempWithOutEncryption
AS
SELECT * fROM tempForView WHERE ID > 0;
GO


SELECT TEXT fROM sys.syscomments WHERE ID = OBJECT_ID('vw_TempWithEncryption')
SELECT TEXT fROM sys.syscomments WHERE ID = OBJECT_ID('vw_TempWithOutEncryption')

The definition of the view that is created with encryption is not present in sys.syscomments.

SchemaBinding:
When a view is created with schema binding then it does not allow DDL modifications to the underlying tables which affect the view definition.

CREATE VIEW vw_TempWWithSchemaBinding
WITH SCHEMABINDING
AS
SELECT ID fROM dbo.tempForView WHERE ID > 0;
GO

DROP TABLE dbo.tempForView

It does not allow to drop the table dbo.tempForView as the view is referencing it and it is created with SCHEMABINDING.

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

VIEW_METADATA:
when the applications accessing SQL Server request browse-mode metadata SQLServer basically provides the details of the TABLES from which the columns in the views are built.
But when a view is created with this option then SQL Server does not provide the base tables of the columns in the view.

More info can be found in MSDN:
http://msdn.microsoft.com/en-us/library/ms187956.aspx