Added line is this color
Deleted line is this color
+ +<div> <strong>Pros & Cons of Using SELECT, Views, and Stored Procedures in +SQL Server<br></strong> </div> +<div>When I first started using SQL Server as a novice, I was initially +confused as to the differences between the SELECT statement, views, and stored +procedures. They all seemed to perform more or less the same task (retrieve +data), and I wanted to know the pros and cons of using each.<br> +Why would SQL Server offer three different options to retrieve data from +database? As a developer and new DBA, I took it upon myself to learn everything +I could about these options, why they may be required, and when they should be +used. This article is a result of my learning and experience, and explains the +differences between SELECT statements, views, and stored procedures for the DBA +or developer new to SQL Server. I hope you find this article useful.<br> +As you read this article, if you choose, you can cut and paste the code into +Query Analyzer I have provided in order to more fully understand and appreciate +the differences between the SELECT statement, views, and stored procedures. I +have divided this article into three parts to better explain this +information.<br> + <br> +Starting Notes<br> +To get us started on learning the differences between the SELECT statement, +views, and stored procedures, I need to mention the syscacheobjects system +table. It is used to store information about compiled objects and their +execution plans. The reason for this is because compiled SELECT statements, +views, and stored procedures are stored here, and I have used this table to +experiment and learn more about how these three different objects are stored +and used by SQL Server. If you are not familiar with this system table, you +might want to take a peek at it. It is stored in the master database, and can +be viewed with Enterprise Manager or Query Analyzer.<br> +If you choose to follow along with the examples in this article, you will want +to run the DBCC FREEPROCCACHE command before each run. This command clears the +syscacheobjects table of any current cached objects, and allows us to perform +more accurate tests.<br> +Now, let’s create a table and input a few rows in the table before we commence +at taking a look at the differences between the SELECT statement, views, and +stored procedures.<br> + <br> +Create Sample Table<br> +I assume you have a database you can use for this. If not, you will want to +create one at this time. Now, we need to create a table for our +experimentation.<br> +Create Table DummyTable1<br> +(<br> + EmpId Int,<br> + EmpName Varchar(8000)<br> +)<br> +Now, let’s add a few records in this table using this script:<br> +Insert Into DummyTable1 Values (1, Replicate ('a',20))<br> +GO<br> +Insert Into DummyTable1 Values (2, Replicate ('b',20))<br> +GO<br> +Insert Into DummyTable1 Values (3, Replicate ('c',20))<br> +GO<br> +Insert Into DummyTable1 Values (4, Replicate ('d',20))<br> +GO<br> +Insert Into DummyTable1 Values (5, Replicate ('e',20))<br> +GO<br> +Insert Into DummyTable1 Values (6, Replicate ('f',20))<br> +GO<br> +Insert Into DummyTable1 Values (7, Replicate ('g',20))<br> +GO<br> +Insert Into DummyTable1 Values (8, Replicate ('h',20))<br> +GO<br> +Insert Into DummyTable1 Values (9, Replicate ('i',20))<br> +GO<br> +Insert Into DummyTable1 Values (10, Replicate ('j',20))<br> +GO<br> + <br> +DummyTable1 has contains sufficient rows to experiment with the differences +between the SELECT statement, views, and stored procedures.<br> +Let us begin with the SELECT statement and see how it is different from views +and stored procedures.<br> + <br> +SELECT Statement<br> +Now, let’s view the contents of the table by EXECuting the following command in +Query Analyzer for our new table.<br> +SELECT EmpId, EmpName FROM DummyTable1<br> +GO<br> +EmpID EmpName<br> +1 aaaaaaaaaaaaaaaaaaaa<br> +2 bbbbbbbbbbbbbbbbbbbb<br> +3 cccccccccccccccccccc<br> +4 dddddddddddddddddddd<br> +5 eeeeeeeeeeeeeeeeeeee<br> +6 ffffffffffffffffffff<br> +7 gggggggggggggggggggg<br> +8 hhhhhhhhhhhhhhhhhhhh<br> +9 iiiiiiiiiiiiiiiiiiii<br> +10 jjjjjjjjjjjjjjjjjjjj<br> +As you would expect, the data we inserted earlier has been displayed.<br> +Now, let’s execute the following commands to clear the cache.<br> +DBCC FREEPROCCACHE<br> +GO<br> +Freeing the procedure cache prevents an ad-hoc SQL statement from being reused, +assuming that it is currently in the cache. This means that the next time we +run the same ad-hoc statement, that it must be newly recompiled.</div> +<div>Now, let’s execute the following commands to display the data and cache +information for the table we created and that is now stored in SQL Server’s +syscacheobjects system table.<br> +SELECT EmpId, EmpName FROM DummyTable1<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.syscacheobjects<br> +GO<br> +The result will display many columns, but we are only interested in four of +them, as shown below.<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 1 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Here’s what the information displayed means:<br> +Cacheobjtype: The type of object stored in the cache, which can include:<br> +• Compiled Plan<br> +• Executable Plan<br> +• Parse Tree<br> +• Cursor Parse Tree<br> +• Extended Stored Procedure<br> +We will be concentrating mainly on the Compiled Plan and the Executable Plan +cacheobjtype type objects.<br> +Refcounts: Number of other cache objects referencing this cache object. A count +of 1 is the base.<br> +Usecounts: Number of times this cache object has been used since inception.<br> +Sql: Text of the statement.<br> +Now, let’s execute the same SELECT statement:<br> +SELECT EmpId, EmpName FROM DummyTable1<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +The value of Usecounts has been incremented. SQL Server has used the same +compiled plan for the SELECT statement and incremented the Usecounts of the +executable plan. N number user will use the same compiled plan when we execute +the same SELECT statement.<br> +Now, let us add a ‘WHERE’ clause on the SELECT statement and see the result +from the master.dbo.Syscacheobjects.<br> +SELECT EmpId, EmpName FROM DummyTable1 WHERE EmpId = 5<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +SQL Server has not used the existing cache plan because of the change in the +SELECT statement. SQL Server will generate a new cache plan for the SELECT +statement along with old cache plan.<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1] WHERE [EmpId]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1] WHERE [EmpId]=@1<br> +Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Let us execute the same SELECT statement with a different empid and verify the +result.<br> +SELECT EmpId, EmpName FROM DummyTable1 WHERE EmpId = 3<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1] WHERE [EmpId]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1] WHERE [EmpId]=@1<br> +Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Though we have given different empid values, SQL Server has used the same +compiled plan for the SELECT statement, and incremented the Usecounts of the +executable plan.</div> +<div>SQL Server has used the same compiled plan for the SELECT statement and +incremented the Usecounts of the executable plan. Different user will execute +the SELECT statement with different empid values, but will use the same +compiled plan and increase the Usecounts value of the executable plan.<br> +Now, let’s execute the same statement with the username on the SELECT statement +and verify the results.<br> +SELECT EmpId, EmpName FROM dbo.DummyTable1 WHERE EmpId = 3<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1] WHERE [EmpId]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1] WHERE [EmpId]=@1<br> +Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[dbo].[DummyTable1] WHERE [EmpId]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[dbo].[DummyTable1] WHERE [EmpId]=@1<br> +Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Now, we have two more rows in the cache plan because we have used different +usernames in the SELECT statements. SQL Server will generate a new compiled and +execution plan for different users. 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.<br> +Let us execute the same SELECT statement with different empid and verify the +results.<br> +SELECT EmpId, EmpName FROM dbo.DummyTable1 WHERE EmpId = 3<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1] WHERE [EmpId]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1] WHERE [EmpId]=@1<br> +Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[dbo].[DummyTable1] WHERE [EmpId]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[dbo].[DummyTable1] WHERE [EmpId]=@1<br> +Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Now, let’s execute the same statement with the databasename and username on the +SELECT statement and view the results.<br> +SELECT EmpId, EmpName FROM vijay.dbo.DummyTable1 WHERE EmpId = 3<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1] WHERE [EmpId]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1] WHERE [EmpId]=@1<br> +Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[vijay].[dbo].[DummyTable1] WHERE [EmpId]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[vijay].[dbo].[DummyTable1] WHERE [EmpId]=@1<br> +Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[dbo].[DummyTable1] WHERE [EmpId]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[dbo].[DummyTable1] WHERE [EmpId]=@1<br> +Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +As a DBA or developer, we can minimize the creation of compilation plans if we +add the databasename, username, and WHERE condition for a SELECT statement. +Don’t change the combination of the SELECT statement to allow creating a new +execution plan unless it is really required. If you do, it will create a new +execution plan on the SELECT statement. We can minimize the execution plan to +upgrade the system’s performance.</div> +<div>Now, let’s execute the following commands to clear the cache before the +stored procedure experiment.<br> +DBCC FREEPROCCACHE<br> +GO<br> + <br> +Stored Procedures<br> +We will create a stored procedure with one parameter, and see how it is differs +from the SELECT statement and views.<br> +CREATE PROC spDummyTable1 (@EmpID Int) AS<br> +SELECT EmpID, EmpName FROM DummyTable1 WHERE EmpID = @EmpID<br> +Now, let’s execute the following commands to display the data and cache +information for the spDummyTable1 we created.<br> +EXEC spDummyTable1 1<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 1 spDummyTable1<br> +Compiled Plan 2 1 spDummyTable1<br> +SQL Server displays the compiled and executable plan for the spDummyTable1 +stored procedure.<br> +Let us execute the same statement again and see the cache details.<br> +EXEC spDummyTable1 1<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 2 spDummyTable1<br> +Compiled Plan 2 1 spDummyTable1<br> +The value of Usecounts has been incremented. SQL Server has used the same +compiled plan for the SELECT statement and incremented the Usecounts of the +executable plan. N number user will use the same compiled plan when we execute +the same stored procedure.<br> +Let’s execute the same stored procedure with a different empid parameter value +and view the cache details.<br> +EXEC spDummyTable1 3<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 3 spDummyTable1<br> +Compiled Plan 2 1 spDummyTable1<br> +The value of Usecounts has been incremented. Though, we have given different +empid value, SQL Server has used the same compiled plan for the stored +procedure and incremented the Usecounts of the executable plan.<br> +Now, let us execute the same stored procedure with the username and see the +cache details.<br> +EXEC dbo.spDummyTable1 5<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 4 spDummyTable1<br> +Compiled Plan 2 1 spDummyTable1<br> +No difference at all. SQL Server has used the same compiled plan for the stored +procedure and incremented the Usecounts of the executable plan.<br> +Let’s execute the same stored procedure from different user. I have created a +new user, called ‘user1,’ and given ‘Exec’ permission for spDummyTable1 stored +procedure. I have opened new Query Analyzer and connected using UID : user1; +PWD : user1. I have EXECuted the following command.<br> +EXEC dbo.spDummyTable1 3<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Different users will execute the stored procedure with different or same empid +value and will use the same compiled plan and increase the Usecounts value of +the executable plan.<br> +Now, let’s execute the same stored procedure with the databasename and username +and see the cache details.<br> +EXEC vijay.dbo.spDummyTable1 7<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 5 spDummyTable1<br> +Compiled Plan 2 1 spDummyTable1<br> +No difference at all. SQL Server has used the same compiled plan for the stored +procedure and incremented the Usecounts of the executable plan.</div> +<div>Overall, stored procedure will compile once and use the same compiled plan +and increment the Usecounts value of the executable plan. A stored procedure is +pre-loaded in memory for faster execution. It will really augment your system +on performance base. This really shows us the importance of the stored +procedure as compared with the SELECT statement and views.<br> +Now, let’s execute the following commands to clear the cache before the +upcoming view experiment.<br> +DBCC FREEPROCCACHE<br> +GO<br> + <br> +Views<br> +We will create a view and how it is differs from theSELECT statement and stored +procedures.<br> +CREATE VIEW vwDummyTable1 AS<br> +SELECT EmpID, EmpName FROM DummyTable1<br> +GO<br> +Now, let’s execute the following commands to display the data and cache +information for the vwDummyTable1 we created.<br> +SELECT EmpId, EmpName from vwDummyTable1<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 1 ()SELECT EmpID, EmpName FROM [vwDummyTable1]<br> +Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]<br> +SQL Server has displayed the compiled and executable plan for the spDummyTable1 +stored procedure.<br> +Let’s now execute the same statement again and view the cache details.<br> +SELECT EmpId, EmpName from vwDummyTable1<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]<br> +Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]<br> +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. N number user will use the same compiled plan +when we execute the same SELECT statement on view.<br> +Now, let’s add a WHERE clauses on the view SELECT statement and view the +results from the master.dbo.Syscacheobjects.<br> +SELECT EmpId, EmpName from vwDummyTable1 WHERE EmpID = 4<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +SQL Server has not used the existing cache plan because of change in the view’s +SELECT statement. SQL Server generates a new cache plan for the SELECT +statement on view along with old cache plan.<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 1 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] +WHERE [EmpID]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE +[EmpID]=@1<br> +Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]<br> +Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]<br> +Let’s execute the same view SELECT statement with different empid and view the +results.<br> +SELECT EmpId, EmpName FROM vwDummyTable1 WHERE EmpID = 8<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1] WHERE [EmpId]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1] WHERE [EmpId]=@1<br> +Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[DummyTable1]<br> +Though we have given different empid value, SQL Server has used the same +compiled plan for the SELECT statement on the view and incremented the +Usecounts of the executable plan.</div> +<div>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<br> +Now, let’s execute the same statement on the view with the username on the +SELECT statement and verify the results.<br> +SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 8<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Though we have given different empid value, SQL Server has used same cache +compiled plan and increased the Usecounts value on the Execution plan.<br> +Now, let us execute the same statement with the username on the SELECT +statement and verify the results.<br> +SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 8<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[dbo].[vwDummyTable1] WHERE [EmpID]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[dbo].[vwDummyTable1] WHERE [EmpID]=@1<br> +Executable Plan 1 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] +WHERE [EmpID]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE +[EmpID]=@1<br> +Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]<br> +Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]<br> +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<br> +Let’s execute the same SELECT statement on view with different empid and verify +the result.<br> +SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 6<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[dbo].[vwDummyTable1] WHERE [EmpID]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[dbo].[vwDummyTable1] WHERE [EmpID]=@1<br> +Executable Plan 1 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] +WHERE [EmpID]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE +[EmpID]=@1<br> +Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]<br> +Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]<br> +Now, let’s execute the same statement with the databasename and username on the +view SELECT statement and verify the results.<br> +SELECT EmpId, EmpName from vijay.dbo.vwDummyTable1 WHERE EmpID = 10<br> +GO<br> +SELECT cacheobjtype, refcounts, usecounts, sql FROM +master.dbo.Syscacheobjects<br> +GO<br> +Cacheobjtype Refcounts Usecounts Sql<br> +Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[dbo].[vwDummyTable1] WHERE [EmpID]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[dbo].[vwDummyTable1] WHERE [EmpID]=@1<br> +Executable Plan 1 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] +WHERE [EmpID]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE +[EmpID]=@1<br> +Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[vijay].[dbo].[vwDummyTable1] WHERE [EmpID]=@1<br> +Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM +[vijay].[dbo].[vwDummyTable1] WHERE [EmpID]=@1<br> +Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]<br> +Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]<br> +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.</div>