Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212

    Unanswered: Views vs Store Procedures

    The result set of non-indexed views is not stored permanently in the database.

    Index Views do I guess , rite? Any reference where this is mentioned?

    So should my views all be indexed? And it seems store procedures are able to serve my purpose.

    With a view I get select columns from various joint tables. From Store procedure I can also select various joint tables .

    So which is better ? Views or Store Procedures?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Straight from The Holy Book (BOL)
    Creating an Indexed View
    Views are also known as virtual tables because the result set returned by the view has the same general form as a table with columns and rows, and views can be referenced the same way as tables in SQL statements. The result set of a standard view is not stored permanently in the database. Each time a query references the view, Microsoft® SQL Server™ 2000 dynamically merges the logic needed to build the view result set into the logic needed to build the complete query result set from the data in the base tables. The process of building the view results is called materializing the view. For more information, see View Resolution.

    For a standard view, the overhead of dynamically building the result set for each query that references a view can be substantial for views that involve complex processing of large numbers of rows, such as aggregating large amounts of data, or joining many rows. If such views are frequently referenced in queries, you can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the view is executed and the result set is stored in the database in the same way a table with a clustered index is stored. For more information about the structure used to store clustered indexes, see Clustered Indexes.
    Well, i prefer stored procedures myself . Though there was this guy at MS Teched who was completely mad about Indexed views and all ... I guess its a matter of choice

  3. #3
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    I've read that article before
    Its an introduction to indexed views and not a comparison.

    Still did not point out which is better , which is the best practice.
    Still open for any wise opinions......
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Direct access to the data through tables in a stored procedure...

    first time every time...

    views are to protect users from object changes...

    just like INFORMATION_SCHEMA...

    When are they ever going to settle on a catalog?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    The result set of non-indexed views is not stored permanently in the database.

    Index Views do I guess , rite? Any reference where this is mentioned?
    That was the reference

  6. #6
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Indexed views physically store data, which means perfomance gain when joining multiple tables. You need to comply to a lot of rules when you create one though, the main one being that you need the Enterprise version of SQL Server. And you can always use a stored procedure to access the view.
    Johan

  7. #7
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    Yeah, I know , the creation of index views are boud to alot of rules.....with the schemabinding thing....and the 2 part object naming.

    So it seems the general consencus of the members here go for store procedures . Okay then, thanks for your thoughts.

    Anyone has anymore things to share?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  8. #8
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    Originally posted by Patrick Chua
    So it seems the general consencus of the members here go for store procedures . Okay then, thanks for your thoughts.

    Anyone has anymore things to share?
    I'm not sure that 3/47243 could really be called a general consencus.

  9. #9
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    So what is u'r opinion on the subject mincer?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Personally I never both with views. I have never found the need to.

    I find that stored procs and nicely indexed tables tend to do everything that I need...

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The Kaiser is Korrect. Procedures are preferable to views. Heck, even if I used an indexed view I would reference it through a stored procedure, and stored procedures are able to take advantage of indexes on views even if the views aren't used in the procedure!

    Indexing views will give you a performance boost, but it will only be noticable on extremely large data-sets. Plus, remember that since the view is now a stored object, it requires additional disk space as well as processing time when it's underlying tables undergo inserts, updates, and deletes.

    Consider an indexed view when you have a non-complex join of two or more large tables with relatively static data. And once again, you don't have to reference the view explicitly for the optimizer to use it in your procedures.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •