SQL Server Performance

indexed view, disallowed

Discussion in 'T-SQL Performance Tuning for Developers' started by mtmingus, Sep 11, 2003.

  1. mtmingus New Member

    I have a simple view based on some union'ed select's:

    create view v_myView
    with schemabinding
    as
    (
    select id1, id2, id3 from tbl1 union all
    select id1, id2, id3 from tbl2 union all
    ...
    select id1, id2, id3 from tbln
    )

    id2 and id3 are calculated by using COALESCE, charindex, and substring.
    Are those functions DISALLOWED CONSTRUCTS? Becasue I couldn't create
    index on column id1 and the error msg is:

    one or more disallowed constructs
  2. bambola New Member

    It's the UNION that is causing you the problem. You cannot use UNION on indexed views...

    Bambola.
  3. gaurav_bindlish New Member

    zackhu, did you execute your code outside view defination? Does it work there?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  4. mtmingus New Member

    I created the index like that
    then went to EM to create the index,
    that's when I received the msg.
    I even tried to use some user defined functions for id2 and id3
    (with schemabinding) doesn't work either.
    If union all does not work in indexed view, what is the purpose
    of indexed view. There are a lot of views are constructed from
    union selects.


    quote:Originally posted by gaurav_bindlish

    zackhu, did you execute your code outside view defination? Does it work there?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

  5. bambola New Member

    when you create a view with schemabinding you should specify the owner name. in case of dbo that would be

    create view v_myView
    with schemabinding
    as
    (
    select id1, id2, id3 from dbo.tbl1 union all
    select id1, id2, id3 from dbo.tbl2 union all
    ...
    select id1, id2, id3 from dbo.tbln
    )

    Gaurav, you can create a "noraml" view with schemabinding and union. you cannot an index view... so I assumed we were talking indexed view...

    Bambola.
  6. mtmingus New Member

    So basically if I have UNION ALL stmts
    in my view creatation, I cannot build an index
    on it?


    quote:Originally posted by bambola

    when you create a view with schemabinding you should specify the owner name. in case of dbo that would be

    create view v_myView
    with schemabinding
    as
    (
    select id1, id2, id3 from dbo.tbl1 union all
    select id1, id2, id3 from dbo.tbl2 union all
    ...
    select id1, id2, id3 from dbo.tbln
    )

    Gaurav, you can create a "noraml" view with schemabinding and union. you cannot an index view... so I assumed we were talking indexed view...

    Bambola.
  7. gaurav_bindlish New Member

    Corrected my mistake...

    Follow Bambola's Suggestion and try creating index from Query analyzer. Enterprise manager behaves weird when it comes to views...

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  8. bambola New Member

    That's correct. UNION is one of the things you cannot use in an indexed view. There are others. You can find them all in BOL under indexed views.

    Bambola.
  9. Chappy New Member

    > Enterprise manager behaves weird when it comes to views...

    I dont know how true this is, but I heard that on non-enterprise editions, enterprise manager does not allow creation of indexed views, but you can create them via query analyser without problem.

    Maybe someone can confirm this?
  10. bambola New Member

    I created one on personal edition using EM.

    Bambola.
  11. gaurav_bindlish New Member

    Babbola, SQL Server Personal Edition is the same as Enterprise edition with the exception of a EULA which prevents the personal edition to be used for deployement on production system.

    I think Stanard Edition will be a better version to check.

    Chappy, for looking at weird behavior of EM, see article Partitioning Tips InstantDoc #37889
    athttp://www.sqlmag.com. This is an excellent article

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  12. robertlmills New Member

    How do you index a view when you have used UNION ALL? If you can't do it using UNION ALL, how do you create a view without using UNION ALL so that it can be indexed?

    Here is my sample code that isn't working...

    --Set the options to support indexed views.
    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
    GO

    --Create the view.
    CREATE VIEW vw1
    WITH SCHEMABINDING
    AS
    SELECT
    [col1],
    [col2],
    [col3],
    [col4],
    [col5],
    [col6],
    [col7]
    FROM dbo.tbl1
    UNION ALL
    SELECT
    [col1],
    [col2],
    [col3],
    [col4],
    [col5],
    [col6],
    [col7]
    FROM dbo.tbl2
    UNION ALL
    SELECT
    [col1],
    [col2],
    [col3],
    [col4],
    [col5],
    [col6],
    [col7]
    FROM dbo.tbl3
    UNION ALL
    SELECT
    [col1],
    [col2],
    [col3],
    [col4],
    [col5],
    [col6],
    [col7]
    FROM dbo.tbl4
    GO

    --Create index on the view.
    CREATE UNIQUE CLUSTERED INDEX idx1 ON vw1 (
    [col1],
    [col2],
    [col3],
    [col4],
    [col5],
    [col6],
    [col7]
    )
    GO

    I can create an index if I create a view using only one table (i.e. don't use the UNION ALL statement).
  13. Negative New Member

    The problem is just as Bambola stated, you cannot index a view if you are using any kind of UNION in it. You can create the view and use it normally, but you simply cannot index it. Having appropriate indexes and constraints on the underlying tables will help with performance, but that is all you will be able to do. If you really want to index this data set, you will need to put it all in 1 table.
  14. robertlmills New Member

    I guess that what I'm looking for then would be a replacement for using "UNION ALL" to join all of my tables, but I don't know of one.

    The data being used grows at 200,000+ records a day so a single table is just not the most attractive idea, but maintaining multiple indexes on multiple tables isn't either.

    It would be soooo nice to actually be able to create a meaningful view (not just one of a single table) and be able to index it.
  15. Negative New Member

    Have you tested performance using a regular view without an index? It may perform well enough without an indexed view. I do not think there is a way to change your view so it does not use UNION statements, and still returns the same results.
  16. mtmingus New Member

    I have a problem with the option
    'QUOTED_IDENTIFIER'
    I set it to on inside my proc, but still I got an error:
    Server: Msg 1935, Level 16, State 1, Line 1
    Cannot create index. Object 'v_ViewName' was created with the following SET options off: 'QUOTED_IDENTIFIER'.


    CREATE PROCEDURE createView
    @Name varchar(100)
    AS
    declare @sqlStr nvarchar(2000)
    --here a view named v_ViewName is created
    --next, try to add an index on it:
    SET QUOTED_IDENTIFIER ON

    SET @sqlStr = N'CREATE UNIQUE CLUSTERED INDEX ' +
    N' [PK_' + @Name + '] ON ' + @Name +
    N'([name1], [name2], [name3])'

    EXECUTE dbo.sp_executesql @sqlStr
    GO

    exec [thisdb].[dbo].[createView] 'thisname'
  17. Luis Martin Moderator

    May be this help:

    The session creating the indexed view must have the following options on: ARITHABORT, CONCAT_NULL_YEILDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNING. This setting can be made in SQL Server Enterprise Manager.

    from BOL


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  18. mtmingus New Member

    Thanks but it doesn't make the result any different.
    I have the SET QUOTED_IDENTIFIER ON inside the proc.
    Apparently SQL doesn't take it.

    quote:Originally posted by LuisMartin

    May be this help:

    The session creating the indexed view must have the following options on: ARITHABORT, CONCAT_NULL_YEILDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNING. This setting can be made in SQL Server Enterprise Manager.

    from BOL


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell

Share This Page