All about SQL Server Views
Posted on : 30-04-2013 | By : Devi Prasad | In : SQL Server Basics
0
Advanced topics in SQL Server Views:
CHECK OPTION
ENCRYPTION
SCHEMABINDING
VIEW_METADATA
Creating Sample Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--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:
|
1 2 3 4 5 6 7 8 9 10 |
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.
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 3 4 5 6 7 8 9 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
|
1 2 3 4 5 6 7 |
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
