Not able to create index on schema binding view

Question

Not able to create index on below schema binding view.It is created from another view (v_prod_manu_sub).It is showing below error message:

Cannot create index on view "dbo.V_PROD_MANU" because it references derived table "X" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.

How to change this below query for index creation ?

ALTER VIEW [dbo].[V_PROD_MANU] WITH SCHEMABINDING AS
SELECT X.PRODUCT, CAST(RIGHT(TEXT_CODE,LEN(F_TEXT_CODE)-1) AS VARCHAR(30)) AS TEXT_CODE,
    CAST(SUBSTRING(RIGHT(PHRASE,LEN(F_PHRASES)-1),9,LEN(F_PHRASE)-3) AS varchar(700)) AS PHRASE
    FROM (
    SELECT V1.PRODUCT,
    (SELECT ',' + V2.TEXT_CODE FROM dbo.V_PROD_MANU_SUB V2 WHERE V1.PRODUCT = V2.PRODUCT ORDER BY V2.F_COUNTER  FOR XML PATH('')) AS TEXT_CODE,
    (SELECT ' |par|par ' + V3.F_PHRASE FROM dbo.V_PROD_MANU_SUB V3 WHERE V1.PRODUCT = V3.PRODUCT ORDER BY V3.F_COUNTER FOR XML PATH('')) AS PHRASE
FROM dbo.V_PROD_MANU_SUB  V1 GROUP BY V1.PRODUCT)X

OUTPUT:

Product         TEXT_CODE                PHRASE 
00-021      MANU0043,MANU0050     Inc |par  Pharmaceuticals Group |par  235 East 5nd Street |par usa |par 1-800-123-000

You want to maintain string aggregation in an indexed view? Why? — Aaron Bertrand, Apr 02 '19 at 11:24
There are many restrictions regarding indexes on views, including no subqueries. Peruse the [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views). — Dan Guzman, Apr 02 '19 at 11:36
Please read https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem — Aaron Bertrand, Apr 05 '19 at 14:25

Aaron Bertrand · Accepted Answer · 2022-02-04T04:41:58.357

Typically people use STUFF() to remove a leading comma, instead of these messy converts and LEN() calculations. For example:

SELECT V1.PRODUCT,
    TEXT_CODE = STUFF
    (
      (
        (SELECT ',' + V2.TEXT_CODE 
           FROM dbo.V_PROD_MANU_SUB AS V2 
           WHERE V1.PRODUCT = V2.PRODUCT 
           ORDER BY V2.F_COUNTER  
           FOR XML PATH(''), 
           TYPE).value('./text()[1]','nvarchar(max)')
      ),
    1,1,N'')
FROM dbo.V_PROD_MANU_SUB AS V1 
GROUP BY V1.PRODUCT;

-- much easier in SQL Server 2017 with STRING_AGG()

But that doesn't seem to have anything to do with why you need to materialize the comma-separated list in the first place, whether it has a leading comma or not.

Indexed views are often a form of premature optimization. Essentially you're saying, "the cost of querying this data will be far greater than the cost of maintaining it." Do you know that? How? What is your workload balance (read:write)? How slow is the query now? How often does it run? How long do updates take?

If you do know that, you will have better luck materializing it to your own table, manually, through a trigger. An indexed view is quite likely going to be a dead end for a variety of reasons.

yes.i created above stuff query.it is working fine.But not able to create index on this above view.Because this view contains sub query. without sub query is it possible to achieve above logic? — Ram, Apr 05 '19 at 13:11
@Ram No, I cannot think of a way that lets you avoid a subquery, unless you materialize the data first as I suggested at the end of my answer. In SQL Server 2017 you could use `STRING_AGG()` which avoids the subquery but you still can't index such a view. I still think this is a weird question to be asking. You are asking "How can I avoid a subquery in this view so I can create an index on it?" and instead you should be asking about the actual problem you think that index would solve. There just might be solutions other than an indexed view in the first place. — Aaron Bertrand, Apr 05 '19 at 13:52

Not able to create index on schema binding view

1 Answers1