Tony Rogerson's ramblings on SQL Server

What are views? Do you think they hold any optimisation properties from being precompiled?

What is a View?

A View is simply an alias name given to a query that can be used in other queries; logically (and it only is logically) you can visualise it as a virtual table, but from an optimisation perspective they are not virtual tables – this is extremely important to remember when designing queries that you want to scale and perform well in a real environment.

How does SQL Server use the View?

The View is stored in the system catalogue as meta data, there is no execution plan associated with a view, it is never compiled.

When you use your View in a query SQL Server gets the View meta data and expands it into the point in your query where your View is used. Once this expansion has taken place there is no longer a concept of an individual view, SQL Server has simply copied and pasted your View definition to the point of reference in the query you are executing. The optimiser then creates a single execution plan for that combined query.

Background reading: http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

Example

create table blah (

    c1  int not null primary key clustered,

    c2  int not null

)

go

create table blah_detail (

    cd1  int not null primary key clustered,

    cd2  int not null references blah( c1 )

)

go

insert blah ( c1, c2 ) values( 1, 1 )

insert blah ( c1, c2 ) values( 2, 1 )

insert blah ( c1, c2 ) values( 3, 1 )

go

insert blah_detail ( cd1, cd2 ) values( 1, 1 )

insert blah_detail ( cd1, cd2 ) values( 2, 1 )

insert blah_detail ( cd1, cd2 ) values( 3, 2 )

go

create view dbo.vw_blah

    with schemabinding

as

    select c1, c2

    from dbo.blah b

        inner join dbo.blah_detail bd on bd.cd2 = b.c1

    where c2 % 2 = 1 -- odd number

go

Let’s look at two queries, the first uses the View:

update dbo.vw_blah

    set c2 = 5

where c1 = 1

Look at the Execution Plan:

  |--Clustered Index Update(OBJECT:([tempdb].[dbo].[blah].[PK__blah__15BCB523]), SET:([tempdb].[dbo].[blah].[c2] = [Expr1011]))

       |--Compute Scalar(DEFINE:([Expr1011]=(5)))

            |--Stream Aggregate(DEFINE:([ b].[c1]=ANY([tempdb].[dbo].[blah].[c1] as [ b].[c1])))

                 |--Nested Loops(Inner Join)

                      |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[blah].[PK__blah__15BCB523] AS [ b]), SEEK:([ b].[c1]=(1)),  WHERE:([tempdb].[dbo].[blah].[c2] as [ b].[c2]%(2)=(1)) ORDERED FORWARD)

                      |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[blah_detail].[PK__blah_detail__5C934F3C] AS [ bd]), WHERE:([tempdb].[dbo].[blah_detail].[cd2] as [ bd].[cd2]=(1)))

There is no mention of the view vw_blah anywhere in this Estimated execution plan. The View has been expanded into the main query and then optimised.

Let’s look at another query:

update dbo.blah

    set c2 = 5

from dbo.blah b

    inner join dbo.blah_detail bd on bd.cd2 = b.c1

where c2 % 2 = 1 -- odd number

  and c1 = 1

  |--Clustered Index Update(OBJECT:([tempdb].[dbo].[blah].[PK__blah__15BCB523]), SET:([tempdb].[dbo].[blah].[c2] = [Expr1004]))

       |--Compute Scalar(DEFINE:([Expr1004]=(5)))

            |--Stream Aggregate(DEFINE:([ b].[c1]=ANY([tempdb].[dbo].[blah].[c1] as [ b].[c1])))

                 |--Nested Loops(Inner Join)

                      |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[blah].[PK__blah__15BCB523] AS [ b]), SEEK:([ b].[c1]=(1)),  WHERE:([tempdb].[dbo].[blah].[c2] as [ b].[c2]%(2)=(1)) ORDERED FORWARD)

                      |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[blah_detail].[PK__blah_detail__5C934F3C] AS [ bd]), WHERE:([tempdb].[dbo].[blah_detail].[cd2] as [ bd].[cd2]=(1)))

Notice how the query is exactly the same as the one using the View?

Views are nothing but inline macros, the macro part being the View Expansion into the main query; you will not see the term inline macro, in Books Online and in talking about Views we use the term Expansion.

There is one optimisation benefit you can gain with a View and that is indexing it; but, in reality you aren’t actually gaining a performance because of the View you are gaining a performance benefit because the index is filtered, it only contains the data that the View filters to thus there is less data to consume. Think of Index Views as summary tables – separate physical tables in their own right.