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!