SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

Note: I have updated the title based on feedback of Davide Mauri (Solid Quality Mentors). Thank you for your help.

Let’s see another reason why I do not like Views. Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, we will have to modify them first. This means any query that does not need this column will start having the column’s data additionally. This will lead to added network traffic as well as it will reduce the performance of the part where the View is used. This further leads to a conclusion: it may not be good idea to alter the View in order to add an additional column if the View happens to be used at multiple places. An alternative solution would be adding the column outside the View. However, this solution can be very expensive.

In today’s limitation of the View, we will see how adding an additional column outside the view can be very expensive, whereas the same situation does not happen with regular T-SQL query.

Let us first create a View using a sample database called AdventureWorks.

USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1] GO
-- Create View on sample tables
CREATE VIEW vw_ViewLimit1
AS
SELECT
[SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber] ,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount] ,[LineTotal],[ReferenceOrderID] FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
GO

Now let us compare the performance of the view with same SELECT statement used in the view.

/* Now compare the execution plan of the view and same definition of T-SQL statement */
-- Filter View with WHERE condition
SELECT *
FROM vw_ViewLimit1
WHERE SalesOrderDetailID > 111111
GO
-- Regular SELECT statement with WHERE condition
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber] ,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount] ,[LineTotal],[ReferenceOrderID] FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO

You will notice that the performance of the View and the SELECT statement is the same since the query cost of both statements is 50%.

SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2 ViewLimit_2

Let us assume that we have to retrieve one more row from the table used in the View. As explained in the first paragraph, altering the View by adding a column to that View may actually lead to unnecessary data for the query using the View, but not looking for that column. The natural alternative solution to this is to use JOIN and add the column to the Views. In the case of T-SQL, we would not have to do the same since we will just go ahead and add the column to the statement.

/* Now let us try to retrieve the column which is not in View */
/* When you compare the performance you will notice View is more expensive*/
-- View with extra column
SELECT v1.*
,
th.[Quantity] FROM vw_ViewLimit1 v1
INNER JOIN Production.TransactionHistory th ON v1.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO
-- Select statement with extra column
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber] ,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount] ,[LineTotal],[ReferenceOrderID] ,th.[Quantity] FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO
-- Clean up
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1] GO

We can now check the performance of both queries using the execution plan.  This will clearly show that a regular T-SQL statement with an additional column is truly less expensive than View that is retrieving an additional column using JOIN.

SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2 ViewLimit_2_1

Click on the image to enlarge it.

Again, if you are not using the View at multiple places, I suggest that you modify it just fine because doing so will not cause you to lose any performance.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL View
Previous Post
SQL SERVER – Does Order of Column in WHERE Clause Matter?
Next Post
SQL SERVER – Getting Started with Execution Plans

Related Posts

10 Comments. Leave new

  • Nice article sir….thank you…i did not know that before…..

    Reply
  • Marko Parkkola
    August 26, 2010 2:32 pm

    I’ve tried to phrase this comments couple of times and I still isn’t exactly what I wanted to say so I’ll just post it and ask if you don’t understand something :)

    Views and especially schemabound views has one upside which is ironically quite the opposite what’s this article about.

    When I create schemabound view it’ll force me to make sure that:

    a) I’ve thought about the situation that understand what I’m doing
    b) Later on, if I need to alter table schema schemabinding makes sure that I can’t do things that’ll break lots of things up

    So if I suddenly realize that I need to add some column to my query, which is not in the view, I need to think that is this what I really want and need. Is this something one time only or is it something that is used a lot?

    If former I’ll just make ad-hoc query and be done with it.

    But if latter I’ll add the column to the view. DB then goes and creates execution plans and statistics for the changed view and possibly optimizes execution plan and speeds up the query.

    Reply
  • It may add overhead to add the column outside the view, but using the view consumes far less processing time. I just rewrote a stored procedure that was taking 27 minutes to execute. I created a schema-bound view, which I joined to another view in the sproc. The query now executes in 6 minutes.

    Reply
  • how declare varaible in store processure

    Reply
  • Interesting article. I’m glad I found as I was about to make somewhat of a similar change.

    I have a view consisting of 12 unions and all 12 unions join to customer table. I thought if I take the customer join outside the view and do 1 join to customer table the performance should be much better. Thinking logically joining once should be better than joining 12 times?

    What do you guys think?

    Reply
  • Two questions:

    1) Why are you using SELECT * … ? As an SQL Authority you should know that not explicitly specifying what columns you need will lead to brittle and potentially underperformant queries.

    2) Assuming that you explicitly select the columns you need eg. SELECT [SalesOrderID],[SalesOrderDetailID], … (see question 1) what is the performance impact of adding an additional column to the view?

    Best regards,
    Andreas

    Reply
    • Yeah! right thanks… better focus the content, we will publish a new article about the code of conduct and tsql etiquette & ethics.

      Regards,
      David Smith

      Reply
  • Thank you, that saved me some time. How much of your time have you spent blogging SQL Server? You must be an online legend at it!

    Thanks again, Mick.

    Reply
  • Your new website refresh is nice but in this article (and I presume others?) the “click to enlarge” is not working for images.

    Reply

Leave a Reply