Post reply

What is the meaning of view is not schema bound?

  • September 10, 2012 at 4:06 am

    #261705

    Dear All,

    I have created following view named FDIP_ITEMS :-

    CREATE VIEW FDIP_ITEM AS

    SELECT DISTINCT A.ROWID,A.A_CODE,A.I_CODE,'P02' PHP,'NOS' UNIT FROM PHP_STOCK A WHERE PEND_QTY>0

    UNION ALL

    SELECT DISTINCT ROWID,A_CODE,I_CODE,'' PHP,'NOS' UNIT FROM FDIP_RMSTK

    It is taking 2 minutes to out result so i want to create index on a_code and i_code columns. But when i am creating index through SQL M.Studio it is showing following error:-

    Cannot create index on view 'FDIP_ITEM' because the view is not schema bound. (Microsoft SQL Server, Error: 1939)

    Tell me how to overcome this problem/error.

    Please tell me where i am doing wrong and it is okey to create index on this view.

    Regards

    Neel

  • September 10, 2012 at 4:38 am

    #1535603

    Read up on the requirements for indexing a view, there are a lot of them.

    This particular one (as Books Online would have shown) say that the view was not created using WITH SCHEMABINDING. However you won't be able to index that anyway because DISTINCT is not allowed in an indexed view, neither is UNION.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply