My workplace uses a large number of stored procedures and views in our data management and as a consequence, I have had to learn to use and even build more of them as I develop applications for the company. Although we use them on a daily basis, stored procedures and views were a bit of a mystery to me and I had a few questions:
What does a view do?
What does a stored procedure do?
When should I use a view and when should I use a stored procedure?
Is there a difference in performance?
I was given a brief answer when I asked my manager these questions, but I wasn't satisfied and needed to dig deeper. This post is a result of that digging and hopefully it will provide some insight to other developers of similar experience levels so we can all learn to use best practices in not only our code, but also in how we manage our data.
What does a view do?
Much as its name would suggest, a view is simply a "window" where you can observe a specific segment of your data. Oftentimes a developer will create a view when they are constantly running a query and want to automate or make their process more simplified.
Displaying data from a view
A view can be thought of as a virtual table and works much the same as any other table in your database when it comes to querying data. Using a SELECT statement, you can query all or query a specific portion of the view using WHERE clauses:
SELECT * FROM vAccounts
WHERE accountId = '523'
You may also join on both views and tables:
SELECT * FROM vAccounts vAcc
INNER JOIN PhoneNumbers pho ON pho.accountId = vAcc.accountId
What does a stored procedure do?
A stored procedure is an encapsulation of logic that can be called from an application (or run in SQL Management Studio). They are often used to perform common INSERT, DELETE, and UPDATE statements on data after a parameter has been passed in by an external application. They will often contain various loops, variables, and calls to other stored procedures, tables, and views.
Displaying data from a stored procedure
Unlike views, stored procedures require an EXECUTE statement and a passed in parameter value to be tested and run:
EXECUTE spAccount 'James';
The returned result of the above would be a virtual table with some kind of association to the name James (we'd need to see the stored procedure to know for sure).
When should I use a view and when should I use a stored procedure?
Most simply, a view is used when only a SELECT statement is needed. Views should be used to store commonly-used JOIN queries and specific columns to build virtual tables of an exact set of data we want to see. Stored procedures hold the more complex logic, such as INSERT, DELETE, and UPDATE statements to automate large SQL workflows.
Is there a difference in performance?
In tests done by Grant Fritchey Scary DBA - Stored Procedures Are Not Faster Than Views, it was determined that, contrary to popular belief, the performance of SQL views and SQL stored procedures is fundamentally identical although they are "fundamentally different objects." Fritchey ran a few thousand executions of a view, a stored procedure w/ view, and a stored procedure, each with 8 logical reads, to retrieve the same data set. His results showed a trivial 10 microsecond (5%) difference which he states could be a disparity on I/O, CPU, or something else.
The only major difference Fritchey could find was in compile time - stored procedures have a much faster compile time than views and this is where the advantage lies for anyone who is seeking a boost in performance.
Sources:
Microsoft - Lesson 1: Creating Database Objects
bytes.com - "Views vs Stored Procedures: What's the difference?"
stackoverflow.com - "What is the difference between a stored procedure and a view?"
If you'd like to catch up with me on social media, come find me over on Twitter or LinkedIn and say hello!
Top comments (5)
Great comparison! It's important to note that nesting views (referencing a view within a view) will almost certainly cause performance issues. I would also note that views are best used to obfuscate data from a source table when other data in that table is needed. Think of a report writer who needs access to employee name and title, but shouldn't have access to ssn or salary. You can create a view with only the columns you need and grant access to the view. In general, I wouldn't recommend using views simply for commonly joined tables. Developers should always write out all of their code or risk unintended changes to the view or nesting of views, IMHO. I would only recommend using views to obfuscate data or to present a simplified version of a query to a report writer who isn't privy to the schema of the database or particularly proficient in SQL.
This is not true. A stored procedure could have a flaw and be vulnerable to SQL injection. That's very easy to do.
This is assuming that the stored procedure takes a VARCHAR or NVARCHAR parameter(s) AND uses these params to build a dynamic query. I think @JaredKarney meant that when you are using a stored procedure you are not building a dynamic sql statement. It's when you use dynamic sql statements that you are vulnerable to sql injection.
I've updated the sections where I mentioned SQL Injection, as that is not something I am very familiar with. Thank you for the correction!
I rarely create views because when joining table you might have to join with table hints, or the view has more then you need. On top of this you can't use parameters.
You can create a table function that is like a SP but you call it like a table or view. And as a extra bonus you can use a cross/outer apply with a table function with a parameter and use a column from the table being connected to.
But if you have to create a SP and need to join the data you can always prefix a insert into infront of the exec command.
I would recommend when possible use with SCHEMABINDING to help with having a change brake your view or SP