SitePoint Sponsor |
|
User Tag List
Results 1 to 8 of 8
Thread: Stored Procedures VS Views
-
Jul 4, 2007, 14:11 #1
- 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
-
Jul 4, 2007, 14:20 #2
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- 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.Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Jul 4, 2007, 14:28 #3
- 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?
-
Jul 4, 2007, 19:56 #4
- Join Date
- May 2003
- Location
- Washington, DC
- Posts
- 10,653
- 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.
-
Jul 5, 2007, 06:29 #5
- 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??
-
Jul 5, 2007, 09:21 #6
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
exactly.
-
Jul 5, 2007, 11:38 #7
- 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.)??
-
Jul 5, 2007, 11:49 #8
- 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