SQL Server 2000: View Basics

INSTEAD OF trigger on a View

SQL SERVER 2000 allows triggers on a View. That’s really great. Previous version of SQL SERVER did not allow this. Again, this is a new feature in SQL SERVER 2000. But remember you can create only INSTEAD OF triggers on Views.

CREATE VIEW vwSample

As

SELECT

          CustomerID,

          CompanyName,

          ContactName

FROM DBO.CUSTOMERS

GO

Limitations of Views

There are some limitations when using Views. They are:

• SELECT INTO cannot be used in the View
   
• Temporary tables cannot be used within the View
   
• Parameterized views does not exists i.e., you cannot pass parameters to the Views
   
• COMPUTE & COMPUTE BY clauses cannot be used within the View

Views and User-Defined Functions

Views and User-Defined Functions almost serve the same purpose. But the major difference is that User-Defined Function can accept parameters, whereas Views cannot. And also the output of the User Defined Function can be directly used in the SELECT clause, whereas you cannot do it with a View.

Summary

Views are nothing but saved SQL statements. User can insert, delete or update the data using the Views in SQL SERVER 2000. Indexes and Triggers can also be created on Views, with an exception that the first Index should be UNIQUE CLUSTERED INDEX and the trigger should be INSTEAD OF trigger. Views and User-Defined Functions serve almost the same purpose with an exception that User-Defined Functions accept parameters, whereas Views will not.

Navneeth Diwaker Naik (navneeth_naik@satyam.com) is an employee of Satyam Computers Services Limited, Hyderabad, Andhra Pradesh. [INDIA].

Published with the explicit written permission of the author. Copyright 2004.