January 2008 - Posts

Utterly amazing; we opened registrations at 7.45pm and as of now 22 hours later we have 317 places booked.

We have worked with the venue today based on the volume of registrations and have increased our potential numbers to a maximum of 420.

So, if you've not already booked and intend coming then do it NOW.

http://www.sqlserverfaq.com/sqlbits

For the press release: http://www.sqlserverfaq.com/sqlbits/sqlbits2008registrations.html

Tony.

 

Registration is now open for SQLBits 2008 March event.

Come and join us at the Lakeside Conference Centre in Birmingham for SQLBits (the sql); believe me the sessions are way better than the punch line! We have kept costs low with help from our sponsors, so low in fact it's free to attend again.

20 technical Sessions broken up into 4 tracks covering all areas a SQL Pro is interested in and on top of that a recruitment panel and new product demonstrations - something for everybody - Development, DBA, Business Intelligence and of course SQL Server 2008. Check out the confirmed sessions.

Technical Sessions end at 5pm but the day continues first with a Recruitment panel and New production demos and then into an informal networking and social evening at our Group By drinks reception - a great opportunity to relax and chat about our holidays, hobbies, ok - get real - more SQL!

Register quickly; we ran out of places in 8 days at the October 2007 event.

Register Me For SQLBits 2008

Technical Session List:

SQL Server 2008
Making more of SSIS in 2008
Reporting in SQL Server 2008
SQL Server 2008 � Declarative Management Framework concepts & database problem solutions with policy management.
Where do you live? Intro to spatial indexing
SQL Server 2008 TSQL Enhancements

Business Intelligence
Using Analysis Services as a data source for Reporting Services reports
Extending SSIS with custom Data Flow components
Master Data Management (MDM)

DBA

SQL Server High Availability and Disaster Recovery Overview
What Are You Waiting For?
I/O! I/O! It�s off to disk we go...
Introduction to SQL 2008 Performance Data Collector
"SELECT TOP(@x) Name FROM MyFavouriteDMVs ORDER BY MyRanking"
Administering SQL Server 2008 and 2005 with Windows PowerShell

Developer
Transient data in SQL Server
Getting to grips with Service Broker.
2005 SQLCMD Scripting
Understanding LINQ in .NET Framework V3.5 and Beyond
Development Life Cycle using Visual Studio Team Edition for DB Professionals
Making the leap into Advanced SQL

Regards
SQL Bits Organising Committee
.

A collegue raised a good question today, why does using BETWEEN '2007-01-01' and '2007-12-31 23:59:59' return rows that are in the year 2008 and using BETWEEN '2007-01-01' and '2007-12-31 23:59:29' not?

The accuracy of smalldatetime is to the minute so when specifying seconds SQL Server will round up or round down to the nearest minute...

print cast(  '2007-12-31 23:59:29'  as datetime )

print cast(  '2007-12-31 23:59:29'  as smalldatetime )

 

Dec 31 2007 11:59PM

Dec 31 2007 11:59PM

 

print cast(  '2007-12-31 23:59:30'  as datetime )

print cast(  '2007-12-31 23:59:30'  as smalldatetime )


Dec 31 2007 11:59PM

Jan  1 2008 12:00AM

If you use DATEPART( second on the casted date you will see that on casting to smalldatetime you will get 0.

smalldatetime is often used when you are storing only the date portion or you are not interesting in seconds, this is to save space more than anything else.

When you query columns that are definied as smalldatetime for instance....

select * from trades where trade_date between '20070101' and '2007-12-31T23:59:59'

You need to make sure you don't specify the seconds otherwise SQL Server will round your input so the above query will actually become...

select * from trades where trade_date between '20070101' and '2008-01-01T00:00:00'

It's probably safer to use >= and < for instance...

select * from trades where trade_date >= '20070101' trade_date < '2008-01-01'

Another aspect - make sure you specify date literals without the hypens! If somebody changes your default langauge to British then '2007-10-01' will actually become 1st January 2007 and not 10th October 2007.

Want a calendar table? Here’s the one I tend to use and an example at the bottom of how to summarise using it.

It can be expanded if you are interested in working / non working days; it’s basically just dropping down at day level and have a column to indicate a working / non working day.

Another method I use when summarising to month level is SUM( CASE <exp> ) but that’s another article that no doubt will show up on here in the coming weeks J.

There is one glaring problem with this and it’s because of SQL Server’s datetime precision, i.e. it’s only precise to 300th millisecond so the end of my day is 23:59:59.997.

{Modified on 21st Jan 2008 to include ISO week and SQL Server week numbers (starting 1 jan).

create table calendar (

    calendar_id             int not null identity constraint pk_calendar primary key clustered,

    period_start            datetime    not null,

    period_end              as cast( left( convert( varchar(60),

                 dateadd( day, -1, case period_type

                                        when 'WY' then dateadd( week, 1, period_start )

                                        when 'WI' then dateadd( week, 1, period_start )

                                        when 'M' then dateadd( month, 1, period_start )

                                        when 'Q' then dateadd( month, 3, period_start )

                                        when 'H' then dateadd( month, 6, period_start )

                                        when 'Y' then dateadd( year, 1, period_start )

                                   end ), 121 ), 10 ) + 'T23:59:59.997' as datetime ),  

    period_name             varchar(50) not null,

    period_type             varchar(2)  not null    check( period_type in ( 'WY', 'WI', 'M', 'Q', 'H', 'Y' ) ),

        constraint uk_calendar_period_start_type unique( period_start, period_type ),

        constraint uk_calendar_period_name unique( period_name, period_type )

)

go

/***

    period_type

        WY - Week in Year according to SQL Server; WI - Week in Year according to ISO

        M  - Month

        Q  - Quarter

        H  - Half

        Y  - Year

 

***/

 

 

--  Populate the calendar (month based stuff first; that is periods are aligned with start of month)

declare @start_date datetime

declare @date datetime

set @start_date = '20060101'

set @date = @start_date

 

set nocount on

 

while @date <= dateadd( year, 1, current_timestamp )

begin

    insert calendar( period_start, period_name, period_type )

        values( @date, left( datename( month, @date ), 3 ) + '-' + datename( year, @date ), 'M' )

 

    if datepart( month, @date ) in ( 1, 4, 7, 10 )

        insert calendar( period_start, period_name, period_type )

            values( @date, 'Q' + case datepart( month, @date )

                                    when 1 then '1'

                                    when 4 then '2'

                                    when 7 then '3'

                                    when 10 then '4'

                                 end + '-' + datename( year, @date ), 'Q' )

 

    if datepart( month, @date ) in ( 1, 7 )

        insert calendar( period_start, period_name, period_type )

            values( @date, 'H' + case datepart( month, @date )

                                    when 1 then '1'

                                    when 7 then '2'

                                 end + '-' + datename( year, @date ), 'H' )

 

    if datepart( month, @date ) = 1

        insert calendar( period_start, period_name, period_type )

            values( @date, 'Y' + datename( year, @date ), 'Y' )

           

    set @date = dateadd( month, 1, @date )

 

end

go

 

 

--  First we need something to give us our ISO week number

create function dbo.fn_iso_week_number(

    @date datetime )

returns int

as

begin

/***

 

    The code here is from the article http://www.sqlservercentral.com/articles/Advanced+Querying/onthetrailoftheisoweek/1675/ by Christoffer Hedgate 20 Jan 2005.

    Note, change 01-04 in Christoffer's article because dates need to be passed as YYYYMMDD in SQL Server to avoid any inconsistency.

 

****/

 

    return( CASE

 

     -- Exception where @date is part of week 52 (or 53) of the previous year

     WHEN @date < CASE (DATEPART(dw, CAST(YEAR(@date) AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

      WHEN 1 THEN CAST(YEAR(@date) AS CHAR(4)) + '0104'

      WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      ELSE DATEADD(d, -6, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      END

     THEN

      (DATEDIFF(d,

       CASE (DATEPART(dw, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

       WHEN 1 THEN CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104'

       WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

       WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

       WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

       WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

       WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

       ELSE DATEADD(d, -6, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

       END,

       @date

      ) / 7) + 1

 

     -- Exception where @date is part of week 1 of the following year

     WHEN @date >= CASE (DATEPART(dw, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

      WHEN 1 THEN CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104'

      WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

      WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

      WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

      WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

      WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

      ELSE DATEADD(d, -6, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

      END

     THEN 1

 

     ELSE

      -- Calculate the ISO week number for all dates that are not part of the exceptions above

      (DATEDIFF(d,

      CASE (DATEPART(dw, CAST(YEAR(@date) AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

      WHEN 1 THEN CAST(YEAR(@date) AS CHAR(4)) + '0104'

      WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      ELSE DATEADD(d, -6, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      END,

      @date

      ) / 7) + 1

     END

    )

end
go

 

--  Insert Week in Year and ISO Week rows

declare @start_date datetime

declare @date datetime

set @start_date = '20060101'

set @date = @start_date

 

set nocount on

 

while @date <= dateadd( year, 1, current_timestamp )

begin

    insert calendar( period_start, period_name, period_type )

        values( @date, datename( year, dateadd( year, case when datepart( month, @date ) = 1 and dbo.fn_iso_week_number( @date ) > 50 then -1 else 0 end, @date ) ) + '-W' + right( '0' + cast( dbo.fn_iso_week_number( @date ) as varchar(2) ), 2 ), 'WI' )

 

    insert calendar( period_start, period_name, period_type )

        values( @date, datename( year, @date ) + '-W' + right( '0' + cast( datepart( week, @date ) as varchar(2) ), 2 ), 'WY' )

 

    set @date = dateadd( week, 1, @date )

 

end

go

 

select *

from calendar

order by period_type, period_start

go

 

--  Show months with no orders...

--  Example use

declare @orders table (

    order_date  datetime    not null,

    order_count int         not null

)

 

insert @orders ( order_date, order_count ) values( '20080105', 1 )

insert @orders ( order_date, order_count ) values( '20080106', 1 )

insert @orders ( order_date, order_count ) values( '20080107', 1 )

 

insert @orders ( order_date, order_count ) values( '20080405', 1 )

insert @orders ( order_date, order_count ) values( '20080406', 1 )

 

--  Sum by month...

select c.period_start,

       total_orders = sum( o.order_count )

from calendar c

    inner join @orders o on o.order_date between c.period_start and c.period_end

where period_type = 'M'

  and period_start between '20080101' and '20080601'

group by c.period_start

 

--  Sum by month and show months that have no orders...

select c.period_start,

       total_orders = sum( o.order_count )

from calendar c

    left outer join @orders o on o.order_date between c.period_start and c.period_end

where period_type = 'M'

  and period_start between '20080101' and '20080601'

group by c.period_start

 

I'm a member of the Harpenden Lions club having recently been inducted, this is a great opportunity for me to get out in the community and help and meet other people, if you are interested in that type of thing and live around Harpenden give me a shout and come to our meetings and get involved.

Anyway to the story; last night the club went for dinner at the House of Commons with Peter Lilley MP as our host; amazing place - absolutely fantastic.

I took a couple of friends along one of which is a long time friend Trevor Dwyer who some will know from his SQL Server MVP days, as you could guess there is a bit of an age difference between club members and me and Trevor so we where the babies of the group, also we where the only two people who forgot to wear a tie! Whoops! That was the first funny thing; the second was Trevor wanting a smoke - because he couldn't go out of the commons he had to be escorted to an area by a guy with a machine gun and accompanied while he finished ***; mind you that said it's probably not such a bad idea :).

After the meal and dinner speech Peter Lilley took us on a tour and actually into the chamber itself which is absolutely great - the wood work etc... very strange being so close to the dispatch boxes - a bit weird seeing it on TV today. The great hall which survived the great fire of London is very impressive too.

A very humbling experience that I would highly recommend anybody if you get the chance.

Already reserved my place; Rafal is very well know in the Data Mining and BI space and this is a great oportunity to see him present.

This is a one day seminar in London, 8:50 -> 16:15 on 3rd April.

Event Overview

Most of us have heard of the magic of Data Mining. No doubt you know that it is part of Business Intelligence platform, offered by Microsoft SQL Server 2008 and 2005, and Microsoft Office System 2007 amongst others. We all know of companies that made fortunes by having carefully extracted intelligence from mountains of data–with Data Mining. Let us demystify this technology in our seminar. In four easy-to-understand yet packed with practical information sessions you will learn about what Data Mining and Business Intelligence can do for you, how to deploy and manage it, how to use it, and how to make it available to other parts of your IT environment. While in the past it may have taken a university degree in Statistics to make use of Data Mining, Microsoft has taken the technology to a new level, making it accessible to all IT Professionals and, with your help, to all of your users. We promise that after attending this seminar Data Mining will no longer seem like black magic to you. Perhaps, we may even help you embark on a new path in your career towards becoming someone akin to a Keeper of Enterprise Intelligence. Let us share our enthusiasm with you.

For more information, the agenda and registration: http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032363487&Culture=en-GB

Ever wondered or even knew about the WITH CHECK OPTION on the CREATE VIEW clause? I’m going to show how the WITH CHECK OPTION works, show how, like the view it’s nothing but an inline substitution of SQL rather than anything special about the View. I will look at alternatives specifically Triggers.

Example Code

use tempdb

go

 

create table Customers (

    id  int not null identity constraint pk_Customers primary key clustered,

 

    customer_name   varchar(100) not null,

 

    county varchar(50) not null,

    status char(1) not null

)

go

 

insert Customers ( customer_name, county ) values( 'Trevor Dwyer', 'Hertfordshire' )

insert Customers ( customer_name, county ) values( 'Tony Rogerson', 'Hertfordshire' )

insert Customers ( customer_name, county ) values( 'Simon Sabin', 'Hertfordshire' )

insert Customers ( customer_name, county ) values( 'Mark Allison', 'Bedfordshire' )

insert Customers ( customer_name, county ) values( 'Peter Blackburn', 'Cambridgeshire' )

go

 

create view vw_Customers_Hertfordshire

    with schemabinding

as

    select id,

           customer_name,

           status,

           County

    from dbo.Customers

    where county = 'Hertfordshire'

    with check option

go

The WITH CHECK OPTION only works when you update, insert or delete using the view directly (this is a clue on how it’s been implemented).

An example call would be:

update vw_Customers_Hertfordshire

    set status = 'M'

The purpose of WITH CHECK OPTION is to make sure that your statement causing the data change does not result in any of the rows no longer appearing in the View when the view is referenced.

So, if we try and change the county from ‘Hertfordshire’ to ‘Cumbria’ for instance an error will result...

update vw_Customers_Hertfordshire

    set county = 'Cumbria'

go

Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

Note that if we just update the base table directly then we get no error...

update Customers

    set county = 'Cumbria'

where county = 'Hertfordshire'

go

Works fine and updates the 3 rows.

From a referential integrity stance what is the point of that? It gives absolutely no data protection at all. I honestly cannot see the point of this option, probably the reason why I’ve never used it or come across anybody using it. Good practice is to use stored procedures and any specific Referential Integrity should be done using CHECK constraints, FOREIGN KEYs or TRIGGERS.

Anyway back to the point of the post; how does this work – is the view doing anything other than being expanded into the main query (just like an inline macro) before execution. Is the view do anything?

Here’s the execution plan of the UPDATE statement on its own (the equivalent statement for the View excluding the CHECK OPTION logic)...

update Customers

    set status = 'M'

where county = 'Hertfordshire'

go

  |--Clustered Index Update(OBJECT:([tempdb].[dbo].[Customers].[pk_Customers]), SET:([tempdb].[dbo].[Customers].[status] = RaiseIfNull(CONVERT_IMPLICIT(char(1),[@1],0))))

       |--Compute Scalar(DEFINE:([ConstExpr1008]=CONVERT_IMPLICIT(char(1),[@1],0)))

            |--Top(ROWCOUNT est 0)

                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Customers].[pk_Customers]), WHERE:([tempdb].[dbo].[Customers].[county]=[@2]) ORDERED)

 

update vw_Customers_Hertfordshire

    set status = 'M'

go

  |--Assert(WHERE:(CASE WHEN [Expr1013] IS NULL THEN (0) ELSE NULL END))

       |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tempdb].[dbo].[Customers].[county]), DEFINE:([Expr1013] = [PROBE VALUE]))

            |--Clustered Index Update(OBJECT:([tempdb].[dbo].[Customers].[pk_Customers]), SET:([tempdb].[dbo].[Customers].[status] = [Expr1003]))

            |    |--Compute Scalar(DEFINE:([Expr1003]='M'))

            |         |--Top(ROWCOUNT est 0)

            |              |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Customers].[pk_Customers]), WHERE:([tempdb].[dbo].[Customers].[county]='Hertfordshire') ORDERED)

            |--Filter(WHERE:(STARTUP EXPR([tempdb].[dbo].[Customers].[county]='Hertfordshire')))

                 |--Constant Scan

I’ve colour coded the above query plan to distinguish between the update logic (in purple) and the with check option logic (in dark red).

As you can see there is no longer a concept of ‘the view’, in fact there is no longer any reference to it; the view and the check option have both been expanded into the main query.

The CHECK OPTION is a simple LEFT OUTER JOIN between the newly updated but yet to be committed value and the constant value held in the View – a nice trick but requires re-querying the data which leads to more IO and CPU.

This opens up a few more areas to cover and as this is already becoming a large article I’ll leave it for another day and entry; in that entry I’ll look more into how the WITH CHECK OPTION performs and works internally, I’ll give some scalability comparisons between other techniques – triggers (before and after) and constraints.

 

Most days I have battles with celko usually over use of IDENTITY, the latest and greatest his unacceptance that views are nothing more than inline expansion of the view metadata but more and more it's this particular formatting of dates, I've told him over and over again this problem exists yet in his reply I get called a Barbarian using nothing but local dialect or his latest rambling below...

"Well, as soon as the village of Cheapshire conquers the ISO Empire and
changes the Standards, I will do that.  And I will convert my liters
to firkins and pay my bills with shillings and old pence, too!  Long
live Queen Victoria!  LOL!"

Even though the date format to fix this problem is still ISO standard, I guess the guy has a level of ignorance and arrogance that won't allow him to accept he's wrong and thus modify his examples, after all he's been posting that format for a couple of decades now.

If I get to a point in my career where I stop listening will somebody please tell me as that is the point where you stop learning, your knowledge stops expanding and your experience becomes limited.

The Problem

When is 2007-04-10 the 4th October? When the login is using the language British!

Take a look at this inconsistency ->

set language British

select cast( '2007-04-10' as datetime )
-----------------------
2007-10-04 00:00:00.000

set language us_english

select cast( '2007-04-10' as datetime )
-----------------------
2007-04-10 00:00:00.000

go

The Fix

Fixing this problem is easy, you have two formats that are still ISO standard, those are YYYY-MM-DDTHH:MM:SS and YYYYMMDD.

set language British

select cast( '20070410' as datetime )
-----------------------
2007-04-10 00:00:00.000

set language us_english

select cast( '20070410' as datetime )
-----------------------
2007-04-10 00:00:00.000

go

Additional

Aparently according to celko I've to read the ISO standard because there is only provision for using dashes; anyway here it is for further reading and yes the standard makes provision for dashes (deemed for human readability) and without dashes (deemed I guess for machine readability):

http://www.iso.org/iso/support/faqs/faqs_widely_used_standards/widely_used_standards_other/date_and_time_format.htm#how-it-works

"Representations can be in one of two formats: a basic format that has a minimal number of characters and an extended format that adds characters to enhance human readability. For example, the third of January 2003 can be represented as either 20030103 or 2003-01-03."

Yes, I find it shocking too that somebody who literally rams the standards down our throats on the public forums should be so out of date on this issue.

It will be interesting to see if he actually changes his examples now (we live in hope....).

 

We've had 38 sessions submitted from the SQL Community - great going guys! The topics look really great and should make this coming conference on 1st March in Birmingham a real hoot!

Voting will close in a couple of weeks time so please, do it today - vote for your favourite 10 sessions -> http://www.sqlbits.com/information/PublicSessions.aspx.

 

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.

Using Views in the real world

Please oh please think what you are doing when using Views and for god sake do not nest them!

My bread and butter comes from consultancy, I do remote support by the hour or on site visits for companies having performance problems or just need advice on SQL Server.

Consider that once I’ve identified what statement is causing the performance problem I then need to investigate optimisation, if that statement is just SELECT x,y,z FROM YourMassiveBlah where YourMassiveBlah is a multi-table, multi-view View I need to expand the who thing just to see what is going on and what bits I can optimise to take advantage of things like derived tables, temporary table, Common Table Expressions or other tricks of the trade, to put a figure on it - would you rather me spend a billable hour on cutting and pasting to reconstruct the query that is being executed because you used Views or me spend a billable hour on tuning.

Another common sense tick is to prefix or suffix View names so they are easily identified without any digging, my preference is to use vw_<object>.

I’ll look at the WITH CHECK OPTION on the View shortly (see http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/01/15/with-check-option-on-create-view-what-is-it-how-s-it-work-what-other-options-are-available-for-referntial-integrity-ri-part-1.aspx) and in depth and will compare various mechanisms for keeping data consistency i.e. using Views WITH CHECK OPTION, CHECK constraints and TRIGGERS.