locked
Creating clustered index on a view RRS feed

  • Question

  • All,

    I am getting following error when creating clustered index on the view. How can i overcome.?


    Msg 10127, Level 16, State 1, Line 2
    Cannot create index on view "db.dbo.vwApp" because it contains one or more subqueries. Consider changing the view to use only joins instead of subqueries. Alternatively, consider not indexing this view.

    Script:

    CREATE TABLE APPUSERS(WSID INT IDENTITY(1,1),DOCID INT ,RID INT)

    INSERT INTO APPUSERS VALUES(10,15)

    INSERT INTO APPUSERS VALUES(20,25)

    INSERT INTO APPUSERS VALUES(30,45)

    INSERT INTO APPUSERS VALUES(40,55)

    INSERT INTO APPUSERS VALUES(50,25)

    INSERT INTO APPUSERS VALUES(10,55)

    INSERT INTO APPUSERS VALUES(20,15)


    INSERT INTO APPUSERS VALUES(200,300)

    INSERT INTO APPUSERS VALUES(500,600)


    INSERT INTO APPUSERS VALUES(200,600)



    SELECT * FROM APPUSERS ORDER BY WSID




    CREATE VIEW vwApp WITH SCHEMABINDING
    AS
     SELECT     A.WSID FROM DBO.APPUSERS A 
      WHERE EXISTS(SELECT B.WSID FROM DBO.APPUSERS B WHERE (A.DOCID = B.DOCID OR A.RID = B.RID) AND A.WSID!=B.WSID)

    go
    CREATE UNIQUE CLUSTERED INDEX UX_CIV ON [vwApp](WSID)

          
    Saturday, April 9, 2016 11:21 PM

Answers

  • It's not possible. See the limitations section for creating indexed views.
    • Proposed as answer by Olaf HelperMVP Sunday, April 10, 2016 9:04 AM
    • Marked as answer by GKS001 Sunday, April 10, 2016 2:07 PM
    Saturday, April 9, 2016 11:44 PM
  • Subqueries are one of the many restrictions that preclude indexing a view.  The EXISTS subquery is the culprit here.  See https://msdn.microsoft.com/en-us/library/ms191432.aspx for the complete list.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Olaf HelperMVP Sunday, April 10, 2016 9:04 AM
    • Marked as answer by GKS001 Sunday, April 10, 2016 2:07 PM
    Sunday, April 10, 2016 4:16 AM
  • If i create a view as follows and not create clustered index will the view data be persisted  or computed on the fly


    View are predefined queries and they don't persist any data.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by GKS001 Sunday, April 10, 2016 2:07 PM
    Sunday, April 10, 2016 1:55 PM
  • The optimizer often does a poor work when OR is involved. I don't know the tables, but it is not impossible that this will run faster:

    SELECT    A.WSID
    FROM      DBO.APPUSERS A
    WHERE     EXISTS(SELECT B.WSID
                     FROM DBO.APPUSERS B
                     WHERE A.DOCID = B.DOCID
                       AND A.WSID!=B.WSID)
    UNION
    SELECT    A.WSID
    FROM      DBO.APPUSERS A
    WHERE     EXISTS(SELECT B.WSID
                     FROM   DBO.APPUSERS B
                     WHERE  A.RID = B.RID
                       AND  A.WSID!=B.WSID)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by GKS001 Sunday, April 10, 2016 6:06 PM
    Sunday, April 10, 2016 5:21 PM

All replies

  • It's not possible. See the limitations section for creating indexed views.
    • Proposed as answer by Olaf HelperMVP Sunday, April 10, 2016 9:04 AM
    • Marked as answer by GKS001 Sunday, April 10, 2016 2:07 PM
    Saturday, April 9, 2016 11:44 PM
  • Subqueries are one of the many restrictions that preclude indexing a view.  The EXISTS subquery is the culprit here.  See https://msdn.microsoft.com/en-us/library/ms191432.aspx for the complete list.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Olaf HelperMVP Sunday, April 10, 2016 9:04 AM
    • Marked as answer by GKS001 Sunday, April 10, 2016 2:07 PM
    Sunday, April 10, 2016 4:16 AM
  • can I ask why you need a indexed view in this case? You're not doing any aggregations here. Are you just trying to learn Indexed view?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, April 10, 2016 6:20 AM
  • Thanks All

    I am trying to solve a practical problem by detecting if two users are viewing same document (DOCID) or  RID. I need  a faster query,  hence i was looking for indexed view.

    If i create a view as follows and not create clustered index will the view data be persisted  or computed on the fly

     

    CREATE VIEW vwApp WITH SCHEMABINDING
    AS
     SELECT     A.WSID FROM DBO.APPUSERS A 
      WHERE EXISTS(SELECT B.WSID FROM DBO.APPUSERS B WHERE (A.DOCID = B.DOCID OR A.RID = B.RID) AND A.WSID!=B.WSID)


    • Edited by GKS001 Sunday, April 10, 2016 1:50 PM
    Sunday, April 10, 2016 12:01 PM
  • If i create a view as follows and not create clustered index will the view data be persisted  or computed on the fly


    View are predefined queries and they don't persist any data.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by GKS001 Sunday, April 10, 2016 2:07 PM
    Sunday, April 10, 2016 1:55 PM
  • The optimizer often does a poor work when OR is involved. I don't know the tables, but it is not impossible that this will run faster:

    SELECT    A.WSID
    FROM      DBO.APPUSERS A
    WHERE     EXISTS(SELECT B.WSID
                     FROM DBO.APPUSERS B
                     WHERE A.DOCID = B.DOCID
                       AND A.WSID!=B.WSID)
    UNION
    SELECT    A.WSID
    FROM      DBO.APPUSERS A
    WHERE     EXISTS(SELECT B.WSID
                     FROM   DBO.APPUSERS B
                     WHERE  A.RID = B.RID
                       AND  A.WSID!=B.WSID)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by GKS001 Sunday, April 10, 2016 6:06 PM
    Sunday, April 10, 2016 5:21 PM