Jeff Maass Blog

My momma always said share.

Why Views are evil.

Synopsis: Views are evil, bad, buggy, temperamental, tortuous, and should be avoided.

Years ago, when I started out as a wee developer graduating from Microsoft Access, I tried and did port my favorite functionality of Access to Sql Server 2000.  The idea was simple, take some complicated table joins and hide all of those complicated relationships.  In MS Access, that's called a Query.  In Sql Server, it is called a View. 

In my enthusiasm, I told the report developer, "No more duplicating the same joins across all those stupid stored procedures!  Do it the right way, centralize that code up into a view.  See, how easy this is?"

I was sooooooo smart, everyone admitted it, except the CIO at raise time ;).  Um, until about a year later, when that same report writer came to me saying that the reports with views weren't running so well.  The why, I still don't know.  I honestly didn't have time to find out.  Though to be honest with myself and you, I had noticed an trend with the views which was already making me queasy.    As time progressed, tables were added.  Lots of tables.  And since, surprise, surprise, the database was reasonably well de-normalized, and we were just making complicated things simple, we added LOTS of tables to those darned views. 

So, here is why I say views are evil:

  1. I've historically been burned by them.  Is that not reason enough?
  2. sp_refreshview  — http://technet.microsoft.com/en-us/library/ms187821.aspx, google terms: SQL2005 BOL sp_RefreshView.  Can I have an Amen to anyone who learned this the hard way?
  3. Views hide complexity by hiding joins.  It is as simple as that.  Joins are a major cause of performance problems.  That hidden simplicity encourages poor T-SQL programming practices.  What I frequently have seen is select X_column1 from viewXY group by X_column1.  Looks fine to me, to you, to everyone.  Wrong!  The view says: Select X_column1….Y_column50 from tableX X join tableY Y on X.xID = Y.XID.  A simple select from the tableX which has a unique index on column1 would have done the trick and done the trick efficiently!
  4. One must work especially hard to make a view updatable/insertable. 

What about indexed views you ask?  Well, I have an answer!

http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx  .  Follow along with me under:

Benefits of Using Indexed Views

"Analyze your database workload before implementing indexed views." 

Hmmmm.  A little further down. 

"Applications that benefit from the implementation of indexed views include:

• Decision support workloads

• Data marts

• Data warehouses

• Online analytical processing (OLAP) stores and sources

• Data mining workloads"

Whew, finally!

"On the contrary, online transaction processing (OLTP) systems with many writes, or database applications with frequent updates, may not be able to take advantage of indexed views because of the increased maintenance cost associated with updating both the view and underlying base tables."

oh, oh, here comes my point:

"Identifying an appropriate set of indexes for a database system can be complex."

So, my answer(s).  A) Views and indexed views are not the same thing.  B) Indexed views may help, but ONLY if you are willing to spend quite a bit of time helping them help you.  In my experience, sadly in most development shops, time isn't given for such labor to occur. C) I work primarily on OLTP systems.

=====================

More reading on views:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx  Not all editions of support Indexed views the same. 

http://sql-server-performance.com/Community/forums/p/7447/43319.aspx  Adrian's explanation was wonderful.

http://www.sql-server-performance.com/article_print.aspx?id=154&type=tip Tips on creating effective indexed views.

http://www.sql-server-performance.com/tips/views_general_p1.aspx Oh look, someone already beat me to it.  There is nothing new under the sun.  I read this AFTER I wrote most of this blog.  AFTER.

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1170220_tax301334,00.html?adg=301324&bucket=ETA#views  Nuff said.

=====================

Conclusion:

Honestly, after writing this post, I'm starting to think maybe I should reevaluate just where I could use indexed views.  Since I live an ignorant life,  I had written them off without investigating them more thoroughly.  I'm thinking, investigate, recommend, implement, get noticed by the CIO, get a raise…..


=====================
PS
2008-02-20

=====================

Scripts:
I was asked to show what was said in above post was accurate in SQL 2005.  Feel free to copy, paste, read, and then run the code below.

/==============================================================================================================================================================================================
–#region Setup Test Schema/

USE master

go

IF NOT EXISTS ( SELECT TOP 1 1 FROM sys.databases WHERE name = 'MaasSqlTest' ) BEGIN CREATE DATABASE MaasSqlTest END

GO

USE MaasSqlTest

GO

IF ( OBJECT_ID('dbo.ViewTest_Detail') IS NOT NULL ) BEGIN DROP TABLE dbo.ViewTest_Detail END GO

IF ( OBJECT_ID('dbo.ViewTest_Group') IS NOT NULL ) BEGIN DROP TABLE dbo.ViewTest_Group END GO

CREATE TABLE dbo.ViewTest_Group ( ViewTest_Group_ID INT IDENTITY(1 , 1) PRIMARY KEY CLUSTERED , name NVARCHAR(50) UNIQUE )

GO

CREATE TABLE dbo.ViewTest_Detail ( ID INTEGER IDENTITY(1 , 1) PRIMARY KEY CLUSTERED , ViewTest_Group_ID INTEGER , DateEntered DATETIME DEFAULT ( GETUTCDATE() ) , name NVARCHAR(50) FOREIGN KEY ( ViewTest_Group_ID ) REFERENCES dbo.ViewTest_Group ( ViewTest_Group_ID ) )

GO

IF ( OBJECT_ID('dbo.ViewTest_GroupDetail') IS NOT NULL ) BEGIN DROP VIEW dbo.ViewTest_GroupDetail END

GO

CREATE VIEW dbo.ViewTest_GroupDetail AS SELECT G.ViewTest_Group_ID , G.name GroupName , D.name DetailName , D.DateEntered DetailTime FROM MaasSqlTest.dbo.ViewTest_Group G JOIN MaasSqlTest.dbo.ViewTest_Detail D ON G.ViewTest_Group_ID = D.ViewTest_Group_ID

GO

/** –#endregion Setup Test Schema ==============================================================================================================================================================================================**/

/============================================================================================================================================================================================== –#region Populate our new tables/

GO SET NOCOUNT ON ;

DECLARE @cntr INTEGER ; SET @cntr = 1 ;

WHILE @cntr <= 10 BEGIN INSERT INTO dbo.ViewTest_Group ( name ) VALUES ( 'GroupID = ' + CAST(@cntr AS NVARCHAR(50)) ) SET @cntr = @cntr + 1 ; END

SET @cntr = 1 ;

WHILE @cntr <= 10 BEGIN INSERT INTO dbo.ViewTest_Detail ( ViewTest_Group_ID , name ) SELECT G.ViewTest_Group_ID , 'Detail=' + G.Name FROM dbo.ViewTest_Group G

    <span class="kwrd">SET</span> @cntr = @cntr + 1 ;
<span class="kwrd">END</span>

GO

/** –#endregion Populate our new tables ==============================================================================================================================================================================================**/

/============================================================================================================================================================================================== –#region Test out various queries using use "Display Estimated Execution Path" button/ BEGIN

--run query <span class="kwrd">plan</span> <span class="kwrd">on</span> this one
<span class="kwrd">SELECT</span>
    *
<span class="kwrd">FROM</span>
    MaasSqlTest.dbo.ViewTest_GroupDetail

--now run query <span class="kwrd">plan</span> <span class="kwrd">on</span> this one
<span class="kwrd">SELECT</span>
    ViewTest_Group_ID
,   GroupName
<span class="kwrd">FROM</span>
    MaasSqlTest.dbo.ViewTest_GroupDetail
<span class="kwrd">GROUP</span> <span class="kwrd">BY</span>
    ViewTest_Group_ID
,   GroupName

--maybe the <span class="kwrd">group</span> <span class="kwrd">by</span> caused <span class="kwrd">both</span> tables <span class="kwrd">to</span> be accessed, so run query <span class="kwrd">plan</span> <span class="kwrd">on</span> this one
<span class="kwrd">SELECT</span>
    ViewTest_Group_ID
,   GroupName
<span class="kwrd">FROM</span>
    MaasSqlTest.dbo.ViewTest_GroupDetail

--can we make it more efficient?
<span class="kwrd">SELECT</span>
    ViewTest_Group_ID
,   GroupName
<span class="kwrd">FROM</span>
    MaasSqlTest.dbo.ViewTest_GroupDetail
<span class="kwrd">WHERE</span>
    ViewTest_Group_ID = 1

/**==============================================================================================================================================================================================
--#region Highlight <span class="kwrd">between</span> the two ==== lines**/
<span class="kwrd">BEGIN</span>
    --<span class="kwrd">Group</span> <span class="kwrd">by</span> via the <span class="kwrd">view</span>
    <span class="kwrd">SELECT</span>
        ViewTest_Group_ID
    ,   GroupName
    <span class="kwrd">FROM</span>
        MaasSqlTest.dbo.ViewTest_GroupDetail
    <span class="kwrd">GROUP</span> <span class="kwrd">BY</span>
        ViewTest_Group_ID
    ,   GroupName


    --<span class="kwrd">Get</span> rid <span class="kwrd">of</span> the <span class="kwrd">view</span>.  Query the underlying <span class="kwrd">table</span> explicitly..
    <span class="kwrd">SELECT</span>
        ViewTest_Group_ID
    ,   Name GroupName
    <span class="kwrd">FROM</span>
        MaasSqlTest.dbo.ViewTest_Group
    <span class="kwrd">GROUP</span> <span class="kwrd">BY</span>
        ViewTest_Group_ID
    ,   Name
<span class="kwrd">END</span>
/**
--#endregion Highlight <span class="kwrd">between</span> the two ==== lines
==============================================================================================================================================================================================**/

END /** –#endregion Test out various queries using use "Display Estimated Execution Path" button ==============================================================================================================================================================================================**/

Legacy Comments


Joe Webb
2008-02-12
re: Why Views are evil.
I've found that in some cases views can be useful, but by and large I tend to avoid them for performance reasons.

For example, when you

SELECT
*
FROM
myView
WHERE
col1 < 100

The select statement inside the view is applied with its where clause then your col1 < 100 where clause is applied. That's extra work for SQL Server.

Joe


Jeff
2008-02-12
re: Why Views are evil.
Funny, one of my upcoming posts is going to be about how great Views are -- because they are great if you use them well; like anything -- store procedures, tables, databases, classes, interfaces, generics, etc -- they can be abused. As always, never blame the tool, blame the person using the tool or the way that tool was used. If you have been burned by Views in the past, then someone was doing something wrong -- it is as simple as that. It's like proclaiming "ints are evil!" because you once wrote an app that tried to use ints to stored dates or phone numbers or something.

Jeff M
2008-02-12
# re: Why Views are evil.
I agree completely. NEVER blame the tool. Blame the idiot who implemented views in the reporting system without understanding how they worked. I blame me!..........Whilst one should not blame the tool, one should in fact develop a healthy respect for how the tool should be used. For instance, chainsaws are best used upon trees and wood. And yes, Leatherface is really the evil one, but don't we all jerk a little at the thought of a misused chainsaw? Most implementations I have seen of views are evil....................

I can't wait to read your post on views and direct colleagues to it!.

Vignesh Kannan
2008-02-18
re: Why Views are evil.
Using Views in your query doesn't make a trip to the D/B Server instead the View results are stored in the Cache.Hence Complex Queries such as Report Queries tend to run much faster when making joins with views than using tables. For small tables there s literally the time advantage s not really felt,but if the table s too large the difference s really felt.
Happy coding :)

jeff m
2008-02-20
re: Why Views are evil.
I tried to contact Vignesh Kannan via the email left along with the post to get clarification. Also, I did some research. I am unable to validate the comment left by Vignesh other than to assume what was meant was "Indexed Views" as opposed to Views. I believe that angle was sufficiently covered in the blog post. Views, to my knowledge, do not have any special caching powers over any other MS SQL Server object.