When it comes to accessing and manipulating data in SQL Server, there are various options available. Two commonly used methods are views and stored procedures. While both serve different purposes, there are misconceptions about their performance and accessibility. In this article, we will evaluate the performance and accessibility constraints of views and stored procedures.

Performance Evaluation

Let’s start by analyzing the execution time and logical reads for fetching a large number of records. In our test, we fetched 160,000 records from both a view and a stored procedure. Surprisingly, both the view and the stored procedure took a similar amount of time, with the view taking 2152 milliseconds and the stored procedure taking 2187 milliseconds. Additionally, both methods consumed the same number of logical reads (1830). This finding debunks the misconception that only stored procedure plans can be cached and that views cannot make use of indexes.

Next, we evaluated the performance for fetching a single record. Again, both the view and the stored procedure performed equally well, taking only 1 millisecond and consuming 3 logical reads. This demonstrates that both views and stored procedures are equipped to use indexes of underlying tables.

Data Accessibility

Now let’s compare the data accessibility features of views and stored procedures. Views do not provide an option to return a customized result set, whereas stored procedures can be customized to handle various requirements within a single procedure. Views cannot be parameterized, but stored procedures can be. Stored procedures also have the ability to return multiple result sets and can return different results based on parameters. On the other hand, views have control over the final data, allowing them to be used in SELECT commands and joined with other views or tables.

It’s important to note that improper usage of views, such as non-sargable conditions, can negatively impact server performance. Stored procedures offer more control over their usage and can be used in various ways, including being part of SELECT statements or storing the result set in a temporary table for later use.

Performance in Cross-Database Queries

We also evaluated the performance of views and stored procedures in cross-database queries. In our test, we joined tables from three databases to fetch 1.23 million records. Both the view and the stored procedure took 15 seconds and consumed 169,093 logical IO to return the records. This demonstrates that both methods perform equally well in cross-database queries.

Final Thoughts

Based on our evaluation, we can conclude the following:

1. Both views and stored procedures perform well and are intelligent enough to make use of available indexes.
2. Views are often used to provide specific columns or rows to users, acting as a guard. Stored procedures, on the other hand, are used for business data processing needs.
3. While views may lack control and can be misused, stored procedures offer more flexibility with features like parameterization and programming constructs for data processing.

It’s important to consider the specific requirements of your application when choosing between views and stored procedures. Both have their strengths and can be utilized effectively depending on the scenario.

We hope this article has provided you with a better understanding of the performance and accessibility constraints of views and stored procedures in SQL Server.