Post reply

index on view

  • July 8, 2011 at 6:50 am

    #243948

    [font="Times New Roman"]

    Hi,

    If i want to create a index on a view then why it is mandatory to create clustered index first?

    Regards,

    Anil K

    [/font]

  • July 8, 2011 at 7:16 am

    #1350338

    a view is just a stored SQL statement. it gets run or rerun on demand whenever you select from it.

    if you think about how indexes work, you can't put an index on a SQL statement itself, until you put a clustered index on it; (you might remember, the clustered index is actual data stored in a physical order )when that happens, the data is actually stored in memory pages...it's truely materialized just like a real table.

    the SQL Engine then updates that views data pages whenever the underlying table.

    once you have that clustered index in place, other, normal indexes can be additionally created, which all eventually use the clustered index to find the data.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 8, 2011 at 8:45 am

    #1350389

    Agree with Lowell.

    The following steps required to create an indexed view are critical to the successful implementation of the view:

    Verify the setting of ANSI_NULLS is correct for all existing tables that will be referenced in the view.

    Verify ANSI_NULLS is set correctly for the current session as shown in the table in “Using SET Options to Obtain Consistent Results” before creating any new tables.

    Verify ANSI_NULLS and QUOTED_IDENTIFIER are set correctly for the current session as shown in the table in “Using SET Options to Obtain Consistent Results” before creating the view.

    Verify the view definition is deterministic.

    Create the view using the WITH SCHEMABINDING option.

    Verify your session's SET options are set correctly as shown in the table in “Using SET Options to Obtain Consistent Results” before creating the unique clustered index on the view.

    Create the unique clustered index on the view.

    Thanks

    Imran

  • July 8, 2011 at 9:10 am

    #1350405

    Be aware that indexed views are good, but if you use them, you are increasing the load on your database. This view has to be maintained whenever you change data in the underlying tables, so if these are transactional tables, this can slow down the updates on that table(s)

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 9, 2011 at 11:00 pm

    #1350789

    [font="Times New Roman"]

    Thanks a lot guys :-):-)

    Regards,

    Anil K

    [/font]

Viewing 5 posts - 1 through 4 (of 4 total)

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