Today, I want to continue talking about database objects that are associated with stored (compiled) code.
A view can be though as a virtual table whose contents are defined by a TSQL query based on one or more tables in the database.
Typical uses of a view are the following:
- To simplify or customize the perception each user has of the database.
- Security mechanism to grant users access to the view, not the underlying base tables.
- To provide a backward compatible interface to emulate a table whose schema has changed.
Like most Data Definition Language (DDL) constructs, a user defined view has three operations associated with it: CREATE VIEW, ALTER VIEW and DROP VIEW.
I will be using the Adventure Works 2012 sample database supplied by Microsoft during this talk. I will be working with the [HumanResources].[vHireDate] to demonstrate the different options a developer has when creating a view.
The first lesson is to examine the WITH CHECK OPTION that forces all data modification statements executed against the view to follow the criteria set within SELECT statement. In short, you can not modify a record and have it disappear from the view.
The example below creates a view base on a SELECT statement filtered by employees whose first name starts with J. It then finds Jay Adams and tries to do a legal name change to Hillary. This change fails when the view is used in a UPDATE statement but passes when the base table is used.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
<span style="color: #008000;">-- -- Lesson A - Check Option -- -- Alter view to include check option ALTER VIEW [HumanResources].[vHireDate] AS SELECT p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate FROM HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID WHERE p.FirstName Like 'J%' WITH CHECK OPTION ; GO -- Show the data SELECT * FROM [HumanResources].[vHireDate] GO -- Find Jay Adams SELECT * FROM Person.Person WHERE BusinessEntityID = 67; GO -- Legal name change (view prevents it) UPDATE [HumanResources].[vHireDate] SET FirstName = 'Hillary' WHERE BusinessEntityID = 67; GO -- Legal name change (table allows it) UPDATE Person.Person SET FirstName = 'Jay' WHERE BusinessEntityID = 67; GO </span> |
The second lesson is that the definition of the view can be masked from prying eyes with the WITH ENCRYPTION option. I am using the sp_helptext system stored procedure to display the view definition which works for the first call but not the second one.
Please note, the algorithm for the masking is well know and will not stop an experienced hacker. Check out this link a decryption routine.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
<span style="color: #008000;">-- -- Lesson B - Encryption Option -- -- Get the views definition sp_helptext '[HumanResources].[vHireDate]'; GO -- Alter view to include encryption option ALTER VIEW [HumanResources].[vHireDate] WITH ENCRYPTION AS SELECT p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate FROM HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID WHERE p.FirstName Like 'J%'; GO -- Get the views definition sp_helptext '[HumanResources].[vHireDate]'; GO </span> |
The third lesson I want to teach today is the WITH SCHEMABINDING option. This option binds the view to the underlying table. Any changes in the TABLE or COLUMN name will not be allowed once it is bound. I am using the sp_rename system stored procedure to attempt the object name changes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
<span style="color: #008000;">-- -- Lesson C - Schema Binding -- -- Alter view to include schema binding option ALTER VIEW [HumanResources].[vHireDate] WITH SCHEMABINDING AS SELECT p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate FROM HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID GO -- Try to rename the column sp_rename 'Person.Person.FirstName', 'FirstName1', 'Column' GO PRINT ' '; GO -- Try to rename the table sp_rename 'Person.Person', 'Person1' GO </span> |
The fourth and final lesson today is to introduce the WITH VIEW_METADATA option. I have been using SQL Server since version 6.5. I have never had a good reason to use this clause.
Taking the definition of this option right from MSDN – “Specifies that the instance of SQL Server will return to the DB-Library, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view.”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<span style="color: #008000;"> -- -- Lesson D - View Metadata -- -- Alter view to include schema binding option ALTER VIEW [HumanResources].[vHireDate] (First_Name, Last_Name, BusEntity_Id, Hire_Date) WITH VIEW_METADATA AS SELECT p.FirstName , p.LastName , e.BusinessEntityID , e.HireDate FROM HumanResources.Employee e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID GO -- Show the data SELECT * FROM [HumanResources].[vHireDate] GO </span> |
I created a local ODBC connection using the SQL Server client driver and a corresponding MS ACCESS linked table. I was not able to view the base tables before or after this option. I think only a low level SQL API call can access this data. I do not know of any client tools, such as SSMS or MS ACCESS, show the base tables used in a view.
To recap this exploration, the WITH CHECK OPTION ensures that the end user will not experience disappearing records when modifying data through the view. The WITH ENCRYPTION option will protect the definition of your views from users that have limited access rights.
The WITH SCHEMABINDING option ensures schema changes can not occur without updating the VIEW. If you do not use this option, sp_refreshview should be run when changes are made to the objects underlying the view. Otherwise, the view might produce unexpected results when it is queried.
Last but not least, I never had any real use for the WITH VIEW_METADATA option. This option depends on whether your developers / users write low level API calls to MS SQL Server.
Next time, I will be exploring the rules of engagement when modifying data via a VIEW.
