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?”

Solarwinds

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)

Solarwinds
, ,
Previous Post
SQLAuthority News – SQL Health Check and SQL Seminars
Next Post
SQLAuthority News – Monthly Roundup of SQLAuthority Blog Posts

Related Posts

7 Comments. Leave new

  • Aashish Ranjan
    July 23, 2011 7:35 pm

    I am getting this issue, is there any way so that i can fix the issue

    Reply
    • I am trying to create the same scenario. list of all views (views wihin a view) in one report and show one’s that are no longer/have not been accessed and also list the field names

      Reply
  • Instead of creating nesting View, just bring over the code of the inner view to the outer view

    Could u explain this in more detail as to what should be done with an example

    Reply
  • Very Clear, specific and its awesome….

    Reply
  • Vikrant More
    June 5, 2013 3:24 pm

    can we say that its an known issue.

    Reply
  • i am also getting same issue.
    Can we say that this is a known issue ?

    Reply
  • agentfire2ntfire
    July 3, 2019 7:55 pm

    you cant BRING OVER THE CODE, the nested query is not supported, outer joins are not supported, applies are not supported, SUM divided by SUM is not supported, ALMOST NOTHING USEFUL is suppoted. any other DBout there?

    Reply

Leave a Reply

Menu