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
Wow! Finally found the solution to this issue – Thanks Devi, I’ve been looking everywhere for this.