Views are nothing but saved SQL statements, and are sometimes referred as “Virtual Tables”. Keep in mind that Views cannot store data (except for Indexed Views); rather they only refer to data present in tables.
Let’s checkout the basic syntax for creating a view:
CREATE VIEW <View_Name>
AS
<SELECT Statement>
GO
There are two important options that can be used when a view is created. They are SCHEMABINDING and ENCRYPTION. We shall have a detailed look on both of these, shortly, but first of all, let’s take a look of an example of a typical view creation statement without any options.
USE Northwind
GO
CREATE VIEW vwSample
As
SELECT CustomerID, CompanyName, ContactName FROM CUSTOMERS
GO
The above T-SQL will create a view named vwSample. Now instead of using the complete SELECT statement to return the above results, you can just use a simple statement like this:
SELECT * from vwSample
Please drop the view if you are following along on your computer, because I will be using the same name throughout the chapter.
DROP VIEW vwSample
Creating Views with the SCHEMABIND Option
Creating a view with the SCHEMABINDING option locks the tables being referred by the view and prevents any changes that may change the table schema.
Notice two important points while creating a view with SCHEMABINDING OPTION:
- The objects should be referred to by their owner names [two part name].
- SELECT * is not permitted.
Here’s an example for a view with the SCHEMABIND OPTION:
CREATE VIEW vwSample
With SCHEMABINDING
As
SELECT
CustomerID,
CompanyName,
ContactName
FROM DBO.CUSTOMERS — Two part name [ownername.objectname]
GO
The above T-SQL statement creates a View with the name vwSample. After creating the view, try to alter the table CUSTOMERS, you cannot do it! This is the power of the SCHEMABINDING option. Since the vwSample is referencing the table CUSTOMERS, you will not be able to execute the ALTER statement on the table CUSTOMERS.
Remember to drop the view.
DROP VIEW vwSample
Creating Views with the ENCRYPTION Option
This option encrypts the definition of the view. Users will not be able to see the definition of the View after it is created.
USE NORTHWIND
GO
CREATE VIEW vwSample
With ENCRYPTION
As
SELECT
CustomerID,
CompanyName,
ContactName
FROM DBO.CUSTOMERS
GO
SELECT *
FROM SYSCOMMENTS
WHERE ID = (SELECT ID FROM SYSOBJECTS WHERE XTYPE = ‘V’ AND NAME = ‘vwSample’)
The view definition will be stored in an encrypted format in the system table named ‘syscomments’.
Note: Once encrypted, there is no way to decrypt it again. So be very careful when you are using the ENCRYPTION option
Remember to drop the view.
DROP VIEW vwSample