SQL Server 2000: View Basics

Indexed Views

SQL SERVER 2000 allows an index to be created on a View. Wow! Previous versions of SQL SERVER will not allow you to do this. But one important point to be noted here is that the first index on the View should be a UNIQUE CLUSTERED INDEX only. SQL SERVER 2000 will not allow you to create any other INDEX unless you have an UNIQUE CLUSTERED INDEX defined on the view.

Let’s check out a sample example for an Indexed View:

CREATE VIEW vwSample

As

SELECT

          CustomerID,

          CompanyName,

          ContactName

FROM DBO.CUSTOMERS

GO

CREATE UNIQUE CLUSTERED INDEX indClustered

ON NORTHWIND.DBO.VWSAMPLE (CUSTOMERID)

GO

The above statement will create a unique clustered index on the View.

Remember to drop the view.

DROP VIEW vwSample

Different Uses of Views

Views can be used to insert/update and delete data from a table. Let’s have a detailed look how to do this. First of all we shall see how to insert data in the table using a view.

The below T-SQL will create a table named TEST and a View with the name vwSample.

USE NORTHWIND

GO

CREATE TABLE [Test]

(

TestID INT,

TestName VARCHAR (100)

)

GO

CREATE VIEW vwTest

As

SELECT

          TestID, TestName

FROM Test

GO

Now we shall insert the data into the table Test using the view. How?

Execute the below T-SQL statement. This will insert the data into the table Test.

INSERT INTO vwTest

VALUES (1, 'Test 1')

SELECT * from Test

TestID           TestName
-------------------------------
1                Test 1

Now we shall see how to Update the data using the same View.

UPDATE vwTest

SET TestName = 'New Value'

WHERE TestID = 1

SELECT * from Test

TestID           TestName
--------------------------------
1                New Value

Similarly we can also delete the data from table using the same view.

Remember to drop the view.

DELETE FROM vwTest