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

Random Posts:


Similar Posts: