SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11

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

When I wrote the article about SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2, I had received a comment that said:

“If joining column is expensive to the view, why can’t I create a view over the view and create an index on it?”

The answer is simple: It’s actually another limitation of the View.

You cannot create an Index on a nested View situation. The following example where we can demonstrate the issue is attached below. In this example, there is already one view, and another view is created on the top of the view. When attempting to create an index on the outer view, it would not be allowed and would throw an error.

Let us see the example:

USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1] GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit2]'))
DROP VIEW [dbo].[vw_ViewLimit2] GO
-- Create View on sample tables
CREATE VIEW vw_ViewLimit1
WITH SCHEMABINDING
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
-- Another View created on the view using the same view cread earlier
CREATE VIEW vw_ViewLimit2
WITH SCHEMABINDING
AS
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber] ,[OrderQty],v1.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount] ,[LineTotal],v1.[ReferenceOrderID] ,th.[Quantity] FROM dbo.vw_ViewLimit1 v1
INNER JOIN Production.TransactionHistory th ON v1.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO
-- Following statement will fail as view contains another view
CREATE UNIQUE CLUSTERED INDEX [IX_vw_ViewLimit1] ON [dbo].[vw_ViewLimit2] (
[ProductID] ASC
)
GO
/*
Msg 1937, Level 16, State 1, Line 1
Cannot create index on view 'AdventureWorks.dbo.vw_ViewLimit2' because it references another view 'dbo.vw_ViewLimit1'. Consider expanding referenced view's definition by hand in indexed view definition.
*/


So here it is — one more limitation. The reason for this is that another view over a view is difficult to maintain. The workaround is very simple as explained in the error messages itself. Instead of creating nesting View, just bring over the code of the inner view to the outer view. After this, it will work just fine, letting you create an index (considering the earlier 10 limitations). How many of you know this issue?

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