To all who are SQL savvy, this is old news, but I wanted to put this tidbit up for you to pass along, as a concise proof, to others that might subscribe to this performance myth. On forums and Stack Overflow, it seems I constantly see this misinformation about views: the commenter/poster will argue that a view is expensive because it has to be evaluated "before" other code that uses the view. It may be natural for some devs, who are used to OO or procedural code that is not automatically run-time cost-optimized, to assume that a view would have to be processed first, and then the results fed to the enclosing query logic -- but if you face a performance issue, it's important to understand how this works.
This is a fundamental misconception, and can be disproven pretty easily. In fact, view code is typically "expanded" into enclosing queries that use the view, just like a derived table, and the whole query gets optimized as one unit. Indexed views aside, a view is most like a stored derived table definition, with a name. If a derived table could be optimized in the context of an outer query, then a view typically can too.
Proof
-- Make a table with some test data, big enough for illustration purposes
CREATE TABLE dbo.BigHairyTable (
IndexedColumn int PRIMARY KEY CLUSTERED,
NonIndexedColumn int
);
WITH Units ( nbr ) AS (
SELECT 0
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9 )
INSERT dbo.BigHairyTable ( IndexedColumn, NonIndexedColumn )
SELECT
u1000000.nbr * 1000000
+ u100000.nbr * 100000
+ u10000.nbr * 10000
+ U1000.nbr * 1000
+ u100.nbr * 100
+ u10.nbr * 10
+ u1.nbr
+ 1 AS IndexedColumn,
checksum( newid() ) as NonIndexedColumn
FROM Units u1,
Units u10,
Units u100,
Units u1000,
Units u10000,
Units u100000,
Units u1000000
-- http://www.projectdmx.com/tsql/tblnumbers.aspx (Anith Larson?)
GO
Next, run and examine execution plans for a "cheap" and an "expensive" query (hence the non-indexed column in the sample data):
-- Compare the execution plans for searching on the indexed vs. non-indexed columns
SELECT * FROM dbo.BigHairyTable
WHERE IndexedColumn BETWEEN 1000 AND 1099
SELECT * FROM dbo.BigHairyTable
WHERE NonIndexedColumn BETWEEN 500000000 AND 500050000
GO
Clearly, the first is cheap because it can seek a few rows from the index (cost = 0.00328 on my laptop) and the second is expensive because it cannot. I get a parallel clustered index scan of the whole table, cost = 25.5, or something around 8000 times more expensive, for the second query.
Next, let's wrap a view around that expensive query:
-- Make a view that is "expensive" because it accesses by the non-indexed column
CREATE VIEW dbo.ExpensiveView AS
SELECT IndexedColumn, NonIndexedColumn
FROM dbo.BigHairyTable
WHERE NonIndexedColumn BETWEEN 500000000 AND 500050000
GO
Here's the clutch: if the view has to be evaluated before an enclosing query, then these two queries should both be very expensive:
-- Compare the cost of these queries:
SELECT * FROM dbo.ExpensiveView
SELECT * FROM dbo.ExpensiveView
WHERE IndexedColumn BETWEEN 1000 AND 1099
GO
But, they are not. The second query is very cheap. It, in fact, has about the same execution plan and cost as our very first sample, picking rows using the clustered index. If the view had to be evaluated "first," then this performance difference could not be true.
What really happens is, approximately, this:
-- This is what is "really" happening (approximately)
SELECT * FROM --dbo.ExpensiveView
( SELECT IndexedColumn, NonIndexedColumn
FROM dbo.BigHairyTable
WHERE NonIndexedColumn BETWEEN 500000000 AND 500050000
) as ExpensiveView
WHERE IndexedColumn BETWEEN 1000 AND 1099
-- The derived table factors out of this query according to optimization rules,
-- and this query is logically equivalent
SELECT IndexedColumn, NonIndexedColumn
FROM dbo.BigHairyTable
WHERE NonIndexedColumn BETWEEN 500000000 AND 500050000
AND IndexedColumn BETWEEN 1000 AND 1099
GO
Exceptions
"So, smart guy," you say, "how come that doesn't work with MY view? Mmm?"
OK, so immediately someone will come back with an example where a view remains expensive to execute, and seems to do painful row-by-row operations. This can happen because of the specific content of the view, but not because of the mere fact that a view exists. The optimizer's rules can only rewrite queries that are precisely, logically, algebraically equivalent (and only a subset of those). So it is possible to create a view that has, for example, a function buried inside that operates on a value from a column, where the optimizer cannot factor that operation out of the enclosing query logic, and ends up performing the operation on every row. But, you can test this yourself, easily, by doing the expansion shown above: copy the query text from the view, paste it into your "outer" query as a derived table, and look at execution plans to see where the hang-up is.
It's also possible that a whole pile of nested views won't optimize fully because the optimizer "gives up." Query optimization is expensive, and the search for the best possible query does get pruned in some ways by the optimizer, so that we don't wait all day for query optimization. At some point the system has to get down to business and find your data.
A heap of complex nested views can, then, present a performance problem because they won't optimize fully. Just know, as you troubleshoot that scenario, that at least it's not because views execute "first."