Should I use a view, a stored procedure, or a user-defined function?
A pretty common question is when to use views, stored procedures, and user-defined functions. More importantly, what are the differences between them, and what advantages do they each have that make them the best choice in certain scenarios?
Well, as with many technologies, the answer really is: it depends. I know, you're groaning, because you hate that answer. But hopefully, by showing you the pros and cons of each element, you can decide for yourself which to use in *your* situation--and you can back that up with your own performance tests and whatever other criteria you want to measure.
Stored procedures accept parameters, and are the preferred method of both manipulating data and simply returning data. They are compiled when first run, and the query plans are stored and cached by SQL Server's optimizer, and those cached plans are swapped out depending on frequency of usage. Generally, a stored procedure will perform faster than an ad hoc query, but there are certain cases (e.g. when a bad plan is cached) that this is not the case.
It is, for the most part, difficult to share data between stored procedures within SQL Server. As an example, you cannot say:
Erland Sommarskog has devoted a lengthy article to the treatment of this topic: How to share data between stored procedu....
By simplifying complex schemas or queries, I mean several things. One could simply be to return the *relevant* columns from a very wide table. You might have an inventory table with 200 columns, detailing a product's every last detail, from weight, size and color to origin country, manufacture date and shipping carrier. However, the database developer trying to write a report for the salespeople, trying to determine which product was their best seller last month, could use a view that only referenced the ProductID and ProductName columns (and join that against the Orders/OrderDetails tables).
Another is to simulate JOINs to reduce the complexity of other queries. For example, let's say you have the following query, that is used in several places:
Well, if you were to create a view like this:
Now your above query can look like this:
Essentially, the optimizer swaps out your reference to dbo.RIMA with the actual code from the view, and they can all be optimized together. And it sure is easier to look at.
Now, that's a pretty trivial example; I'm sure you can come up with much more convoluted and real examples where simply cutting down on the number of lines of code in a query can speed development time greatly. It can also be easier to explain to junior database developers that to get a result of x, you query the view m, instead of having to explain (or provide direct access to) the joins required against the main tables.
Which brings me to my point about security. Let's say you have a table called Employees, and it has columns like salary and SSN. Well, you probably don't want everyone with access to the database to be able to see those columns, so you can create a view called EmployeeInfo which neglects to select those columns, and deny access to the base table to the users (either by placing them in specific roles/groups and denying access that way, or issuing explicit DENY statements to each user). This way, users don't even have to know that there is a table with more information behind the scenes.
A view does not accept parameters, which is definitely a strange concept for users migrating from Microsoft Access. To restrict the rows returned by a view, you use a WHERE clause in the query that is accessing the view, not in the view itself. Table-valued functions, described further down, are more like parameterized views; in fact, they were called parameterized views during development, according to Hal Berenson.
Like a subquery or inline query, a view is not ordered. There is a kludgy workaround where users implement something like this:
But this can cause problems and, AFAIK, is still not guaranteed to be in the order you define (even in this case, the order will be dictated by the outer query that selects from this view). If you want the results of your view to be in a specific order, then like the WHERE clause above, you add an ORDER BY in the query that is calling the view, not in the view itself.
I said earlier that views do not store the data, only a definition of the query. This is true except for the case of indexed views, in which the data IS materialized to disk and a query plan that uses the clustered index can be cached. Books Online has plenty of reading material on indexed views: Designing an Indexed View and Creating an Indexed View.
However, UDFs do have their place in the SQL Server world. Note that there are three kinds of user-defined functions: scalar functions, inline table-valued functions, and multi-statement table-valued functions.
Now, having said all that, there is a difference between client->server interaction, and interaction within SQL Server itself. All client->server interactions with SQL Server, whether they be from an application or an end user in Query Analyzer, should be through stored procedures. Processes outside of SQL Server should not have direct access to tables, views or functions. So my basic ground rule is, for anything client-facing, build a stored procedure. Whether that stored procedure accesses tables, views or UDFs behind the scenes, rests entirely on the points above that are relevant to your environment, and the testing you perform in order to yield which implementation works best.
Note that due to time constraints and the availability of warm bodies, my ideal scenario is not always possible--but it can always be a goal on the radar screen.
Related ArticlesHow do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?