SQL Server Views … to make sure others “view” what you want them to view !!!

In essence, SQL Server view can be considered as a “Virtual Table”. When we create a view, user “feel” no difference between it and a table. And we can query it exactly same way as we query any other table. View can be really handy when we are using some super complex query to get data, by shortening the query. But keep in mind that when we define a view, actually nothing happens … consider view as a just shortcut to execute some very long query … period.

Creating view by any means doesn’t “improve” the performance of query. It just simplifies the way user can execute a query smile_zipit . So if you original query generates super big , complex execution plan then it will do SAME for view.

With that said, following are few use of VIEWs.

First example is demo. of just simple view.

-- Simple View
CREATE VIEW dbo.vFoo1
AS  SELECT  *
    FROM    viewTest
GO

But what if underlying table gets changed ?? Well, to prevent any changes to underlying tables used in views we have to create a view with a parameter called SCHEMABINDING. So when someone tries to change underlying table structure (say, adding or removing column) error is generated.

-- using Schemabinding in view
CREATE VIEW dbo.vFoo2
WITH SCHEMABINDING
AS  SELECT  FirstName,
            LastName,
            AddressLine1,
            City,
            [State/Province],
            Country
    FROM    dbo.viewTest
GO

Just like any select query, we can create a view which can have WHERE clause included that essentially acts as a filter. Such a views are called “Filtered Views”. And they can be really useful when we want to restrict our end users’ access to some given restriction (e.g. NY office users can only view records where Office Code value is NY ??).

-- Filtered View
CREATE VIEW dbo.vFoo3
WITH SCHEMABINDING
AS  SELECT  FirstName,
            LastName,
            City
    FROM    dbo.viewTest
    WHERE   Country = 'United States'
GO

SELECT  *
FROM    dbo.vFoo3

The latest feature of view is ability to create an Index. But we can only create CLUSTERED INDEX on it. The view which as an index on it is called “Materialized View” or “Indexed View”. And as you might have already guessed, when we create clustered index on a view, we can have significant performance gain. But like any case of indexing, this performance gain doesn’t come for free fingerscrossed … it can cause issues with cases where data is volatile and changes frequently, so you have to be careful while indexing the views.

-- Indexed View (a.k.a. Materialized view)
alter VIEW dbo.vFoo4
WITH schemabinding
AS  SELECT  FirstName,
            LastName
    FROM    dbo.viewTest
    WHERE   country = 'Germany'
GO
-- we can Only create UNIQUE Clustered Index 
CREATE UNIQUE CLUSTERED INDEX ix_vFoo4 on dbo.vFoo4 ( FirstName, LastName )
GO

View Designing Best Practice :

1. Don’t create view with Select * , instead try to use column names.

2. Use WITH SCHEMABINDING, to prevent underlying schema changes which might break the view.

Restrictions for creating Views (Words to Avoid in definition in VIEW ) :

ANY, NOT ANY	OPENROWSET, OPENQUERY, OPENDATASOURCE
arithmetic on imprecise (float, real) values	OPENXML
COMPUTE, COMPUTE BY	ORDER BY
CONVERT producing an imprecise result	OUTER join
COUNT(*)	reference to a base table with a disabled clustered index
GROUP BY ALL	reference to a table or function in a different database
Derived table (subquery in FROM list)	reference to another view
DISTINCT	ROWSET function
EXISTS, NOT EXISTS	self-join
expressions on aggregate results (e.g. SUM(x)+SUM(x))	STDEV, STDEVP, VAR, VARP, AVG
full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)	Subquery
imprecise constants (e.g. 2.34e5)	SUM on nullable expressions
inline or table-valued functions	table hints (e.g. NOLOCK)
MIN, MAX	text, ntext, image, filestream, or XML columns
non-deterministic expressions	TOP
non-unicode collations	UNION
contradictions SQL Server 2005 can detect that mean the view would be empty (e.g. where 0=1 and …)

That’s It for now…

It’s Just a Thought … fingerscrossed

Gaurang Sign

Leave a Reply