SQL Server 2000: View Basics

By : Navneeth Diwaker Naik Jan 16, 2004

Printer friendly

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