SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stored Procedures VS Views

    Hi:

    can somebody please explain the difference?

    I searched on internet but i couldn't quite get what they were trying to explain.

    Thanks
    ---------------------------
    Errors = Improved Programming.
    My Site

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    A stored procedure is a program. It can be many, many lines long with lots of logic. It can return one or more result sets, or a single value, or nothing at all. With Microsoft SQL Server, you can even use the .NET CLR from within a stored procedure on the database.

    A view is a single stored SELECT query. It is similar to a temporary table you can only read from, not insert or update, but you can use it from within other SELECT queries. Views simplify queries, provide stable interfaces to applications for read-only data, and make easier access control since they have their own permissions like real tables.

  3. #3
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.

    Now, Let's say i have a Select query (maybe simple or complex. i understand INSERT etc. is not in scope of view). i can do the same query in stored procedure OR in view.

    if you were me, which facts would make you choose SP or View?
    ---------------------------
    Errors = Improved Programming.
    My Site

  4. #4
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,649
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    You can INSERT or UPDATE views under some circumstances, but that is a side point, and a generally bad idea.

    As for SELECTs, it depends on a few factors. If it is very complex, a SP might be better as they tend to be more performant. Same on if you want multiple result sets. Views are handy if you want to deal with the selected data like a table--like use it in JOINs with other tables or expose it directly to client apps like Excel or Access.

  5. #5
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AH thanks.

    So, basically it sort of creates a temporary table from the select and i can use this view as a table of sorts, right??
    ---------------------------
    Errors = Improved Programming.
    My Site

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    exactly.

  7. #7
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wonderful. thanks


    oh BTW, does using view , for a query, rather than procedure has any impact (positive or negative) on the resources (CPU, speed etc.)??
    ---------------------------
    Errors = Improved Programming.
    My Site

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    by simple virtue of it being a view? no. since a view is at it's heart a query, it could be written in multiple ways, each with different performance characteristics.


Bookmarks

Posting Permissions

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