More View Usage Variations in SQL Server
More View Usage Variations in SQL Server
This article will show you how to use view for any scenarios that need improvisation such as partitioned views, function in view, and encrypting views.
Partitioned View
You can make a union of a set of views, provided the views are union-compatible and then select from or update the view.
CREATE VIEW All_Authors AS
SELECT * FROM CA_Authors
UNION
SELECT * FROM NY_Authors
When you update a partitioned view, SQL Server is smart enough to know from you values to what table the new or changed row belongs.
Function in view
You can add a function to a view and have it return the result of the function by including the input in the view declaration. For example :
CREATE VIEW Vw_GetAverageSales (TitleType, AvgYTDSales)
AS
SELECT type, AVG(YTD_Sales)
FROM titles
GROUP BY type
Encrypting view
You can also create a view with encryption, just as you can with triggers and stored procedures. Just add the WITH ENCRYPTION option to the view declaration:
CREATE VIEW CA_Authors
WITH ENCRYPTION
AS
SELECT au_lname, au_fname FROM Authors
WHERE state = ‘CA’
The purpose of encryption is to store the view definition in an encoded format. This can be useful if you are delivering the database as a product and the view definition contains proprietary information.
Written by,
Kasim Wirama, MCSD.NET, MCDBA
Database Administrator