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