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.....

/**==============================================================================================================================================================================================
--#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
==============================================================================================================================================================================================**/