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