SQL-Server Performance: What is faster, a stored p

Question

What is faster in SQL Server 2005/2008, a Stored Procedure or a View?

EDIT: As many of you pointed out, I am being too vague. Let me attempt to be a little more specific.
I wanted to know the performance difference for a particular query in a View, versus the exact same query inside a stored procedure. (I still appreciate all of the answers that point out their different capabilities)

Answer 1

In short, based on my experience in some complex queries, Stored procedure gives better performance than function.

But you cannot use results of stored procedure in select or join queries.

If you don't want to use the result set in another query, better to use SP.

And rest of the details and differences are mentioned by people in this forum and elsewhere.

Answer 2

A couple other considerations: While performance between an SP and a view are essentially the same (given they are performing the exact same select), the SP gives you more flexibility for that same query.

• The SP will support ordering the result set; i.e., including an ORDER BY statement. You cannot do so in a view.
• The SP is fully compiled and requires only an exec to invoke it. The view still requires a SELECT * FROM view to invoke it; i.e., a select on the compiled select in the view.

Answer 3

Stored procedures and views are different and have different purposes. I look at views as canned queries. I look at stored procedures as code modules.

For example let's say you have a table called tblEmployees with these two columns (among others): DateOfBirth and MaleFemale.

A view called viewEmployeesMale which filters out only male employees can be very useful. A view called viewEmployeesFemale is also very useful. Both of these views are self describing and very intuitive.

Now, lets say you need to produce a list all male employees between the ages of 25 and 30. I would tend to create a stored procedure to produce this result. While it most certainly could be built as a view, in my opinion a stored procedure is better suited for dealing with this. Date manipulation especially where nulls are a factor can become very tricky.