Pros & Cons of Using SELECT, Views, and Stored Procedures in SQL Server

The value of Usecounts has been incremented. SQL Server has used the same compiled plan for the SELECT statement on the view and incremented the Usecounts of the executable plan. Different users will execute the SELECT statement on view with different empid value will use the same compiled plan and only increase the Usecounts value of the executable plan

Now, let’s execute the same statement on the view with the username on the SELECT statement and verify the results.

Now, we have two more rows in the cache plan because we have used different usernames in the SELECT statement. SQL Server generates a new compiled and Execution plan for different user. The same user will execute the SELECT statement more than one time will use the same compiled plan and only increase the Usecounts value of the executable plan

Let’s execute the same SELECT statement on view with different empid and verify the result.

We have seen that we can minimize the creation of compilation plans if we add the database name, username, and WHERE condition for a SELECT statement or on a view. If you do change these, then your code has to be recompiled, hurting your system’s performance. So what is the difference between a SELECT statement and a view in regards to performance?. Is there any difference at all? No, the compiled and executable plans are same for both SELECT statement and view. But one difference is that a view is stored (physically) in a database, while SELECT statements are not. The advantage of a view is that it, in some cases, allows the easier administration of object permissions. For example, you create a view and provide a SELECT permission for certain set of users, but not others, that only allows them to see certain columns in a table, but not all. In most cases, if you don’t have this need from a security perspective, then the use of views in unnecessary.

Biography

G. Vijayakumar has worked in client server and web application. He is currently working for Transworld, which is located in Bangalore, India, working on an e-banking prod