November 10, 2004 at 7:31 am
Is there any advantage of using a view over a non paramatised stored procedure?
November 10, 2004 at 9:05 am
It depends. Will the output from the view or stored procedure be used in other queries? If yes, then a view is the way to go. If no, then use a stored procedure. From a performance perspective, there will be no difference. Views are akin to macros.
--
Adam Machanic
whoisactive
November 11, 2004 at 2:58 am
Well, yes...
Effectively a view is giving you another table to work with, and is therefore loads more flexible than a stored procedure.
You can select specific columns, add "where" clauses or combine it with other tables/views...
For example you could do something like this (assuming that vwMyView joins a couple of tables to get employee details) -->
SELECT v1.FirstName + ' ' + v1.LastName AS 'FullName', v1.City, pay1.PayDate
FROM vwMyView v1
JOIN tblPayments pay1
ON v1.PersID = pay1.PersID
WHERE v1.LastName = 'Smith'
---<
Does that help?
Sam
November 12, 2004 at 1:06 pm
Sam: What do you mean by "loads more flexible than a stored procedure"? Coincidentally, I am having this same discussion with a colleague in the next cube. He makes views which are then used by Crystal Reports, I make stored procs which are then used by either web pages or other stored procs. He tells me that views are more efficient, but can't point to why.
I have read that, under the covers, SS implements a view the same way it implements a stored proc, i.e., it creates a query plan, keeps it handy for execution, etc. So then your employee example, where you want to list every employee with a given name, could also be implemented by a stored proc:
CREATE PROCEDURE usp_EmployeesList
@ParmEmployeeLastName varchar(30)
AS
SELECT e.FirstName + ' ' + e.LastName AS 'FullName', e.City, pay1.PayDate
FROM Employees AS e
JOIN tblPayments AS pay1
ON e.PersID = pay1.PersID
WHERE v1.LastName = @ParmEmployeeLastName
RETURN
So it seems to me that SS is doing the same amount of work, just in a different place; the join happens in the proc as opposed to the view, but the net effort on the part of SS is the same. What do you think?
November 12, 2004 at 1:39 pm
"I have read that, under the covers, SS implements a view the same way it implements a stored proc, i.e., it creates a query plan, keeps it handy for execution, etc"
That is not true; in SQL Server, views are essentially macros which are expanded by the query optimizer into derived tables. So this:
SELECT *
FROM MyTable
JOIN
(SELECT *
FROM AnotherTable) x ON MyTable.PK = x.PK
is equivalent, performance-wise, to:
CREATE VIEW myView
AS
SELECT *
FROM AnotherTable
...
SELECT *
FROM MyTable
JOIN
myView ON MyTable.PK = myView.PK
--
Adam Machanic
whoisactive
November 15, 2004 at 3:04 am
Hi
What I mean by "loads more flexible than a stored procedure" is that with a view you've effectively got another table that you can do selects, order, joins and anything else you want. With a stored procedure you decide before you programme it how the output is going to be used and it's not so easy to use it to deal with unexpected requirements.
Does that make sense?
Sam
November 15, 2004 at 7:51 am
Since "views are essentially macros which are expanded by the query optimizer into derived tables" without the stored execution plan of a stored procedure, you could get around this by creating a table-valued function, and wrapping it with a view; that way, the function will have a stored execution plan, and thus, so will the wrapper view.
Another difference between views and SP's is that you can index a view.
November 15, 2004 at 9:23 am
If you already have a table-valued function, why bother with a view?
A table-valued function is essentially a parameterized view... (or non-parameterized, if you don't bother using parameters!)
--
Adam Machanic
whoisactive
November 15, 2004 at 3:05 pm
Sam & Adam: OK, I see now that I was mistaken. I just now pulled out my copy of "Inside SQL Server 2000" and read the parts on views, indexes on views, and clustered indexes. So if you have an indexed view, when you update the underlying base tables, will you also (indirectly) be updating the view's clustered index?
November 15, 2004 at 3:09 pm
That's correct. Keep in mind that indexed views were really designed to assist with performance of aggregate-heavy queries (lots of SUM, AVG, etc). A lot of developers attempt to use them to speed non-aggregate queries (create "flattened" views of the data) and are disappointed to discover that they're not too helpful in those cases.
--
Adam Machanic
whoisactive
November 15, 2004 at 5:06 pm
So if you don't have any indices on your view, will QA use an index that is on the underlying tables? Provided the column being indexed is in the column list for your view.
November 19, 2004 at 3:28 pm
Hi All,
Views give a greater flexibility when running queries against it. It also means that if you have a very large dataset that you want to narrow it down to a few records using a where clause this is the best way to go.
Stored Procedure does not give this flexibility other than having predefined list of where clause options(as pointed out in another post).
From personal experience I have found that using SP is better than view as it is more faster in giving back the results of the query on large tables. This is because the records are cached the first time the SP runs and this cache is used to speed up the execution of the Stored Procedure.
Hope this helps
Abhinav Kushalnagar
kushalnagar.com
November 19, 2004 at 5:17 pm
I think you may want to chalk this up to what you plan to use for and personal preference.
If the basis of the view is something you may potentially reuse then make a view so you can write the once and reuse later. If it is not then just make a choice based on what you prefer. Either way the view once used will generate a execution plan that unless needs to be removed can still be there later just like an SPs is handled as long as you use the proper techniques (see "Transact-SQL Tips" in SQL BOL).
But then if you prefer to assign execute permissions to SPs and avoid Select rights then you might just want to create a view and use it in you SP. That is an adminstrative choice you have to make.
In this situation I know of no specific condition which either would be a bad chocie, it just depends on the usage, reusage and change in usage later.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply