Pros & Cons of Using SELECT, Views, and Stored Procedures in
SQL Server
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.
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.
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.
Starting Notes
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.
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.
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.
Create Sample Table
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.
Create Table DummyTable1
(
EmpId Int,
EmpName Varchar(8000)
)
Now, let’s add a few records in this table using this script:
Insert Into DummyTable1 Values (1, Replicate ('a',20))
GO
Insert Into DummyTable1 Values (2, Replicate ('b',20))
GO
Insert Into DummyTable1 Values (3, Replicate ('c',20))
GO
Insert Into DummyTable1 Values (4, Replicate ('d',20))
GO
Insert Into DummyTable1 Values (5, Replicate ('e',20))
GO
Insert Into DummyTable1 Values (6, Replicate ('f',20))
GO
Insert Into DummyTable1 Values (7, Replicate ('g',20))
GO
Insert Into DummyTable1 Values (8, Replicate ('h',20))
GO
Insert Into DummyTable1 Values (9, Replicate ('i',20))
GO
Insert Into DummyTable1 Values (10, Replicate ('j',20))
GO
DummyTable1 has contains sufficient rows to experiment with the differences between the SELECT statement, views, and stored procedures.
Let us begin with the SELECT statement and see how it is different from views and stored procedures.
SELECT Statement
Now, let’s view the contents of the table by EXECuting the following command in Query Analyzer for our new table.
SELECT EmpId, EmpName FROM DummyTable1
GO
EmpID EmpName
1 aaaaaaaaaaaaaaaaaaaa
2 bbbbbbbbbbbbbbbbbbbb
3 cccccccccccccccccccc
4 dddddddddddddddddddd
5 eeeeeeeeeeeeeeeeeeee
6 ffffffffffffffffffff
7 gggggggggggggggggggg
8 hhhhhhhhhhhhhhhhhhhh
9 iiiiiiiiiiiiiiiiiiii
10 jjjjjjjjjjjjjjjjjjjj
As you would expect, the data we inserted earlier has been displayed.
Now, let’s execute the following commands to clear the cache.
DBCC FREEPROCCACHE
GO
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.
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.
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.
Starting Notes
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.
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.
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.
Create Sample Table
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.
Create Table DummyTable1
(
EmpId Int,
EmpName Varchar(8000)
)
Now, let’s add a few records in this table using this script:
Insert Into DummyTable1 Values (1, Replicate ('a',20))
GO
Insert Into DummyTable1 Values (2, Replicate ('b',20))
GO
Insert Into DummyTable1 Values (3, Replicate ('c',20))
GO
Insert Into DummyTable1 Values (4, Replicate ('d',20))
GO
Insert Into DummyTable1 Values (5, Replicate ('e',20))
GO
Insert Into DummyTable1 Values (6, Replicate ('f',20))
GO
Insert Into DummyTable1 Values (7, Replicate ('g',20))
GO
Insert Into DummyTable1 Values (8, Replicate ('h',20))
GO
Insert Into DummyTable1 Values (9, Replicate ('i',20))
GO
Insert Into DummyTable1 Values (10, Replicate ('j',20))
GO
DummyTable1 has contains sufficient rows to experiment with the differences between the SELECT statement, views, and stored procedures.
Let us begin with the SELECT statement and see how it is different from views and stored procedures.
SELECT Statement
Now, let’s view the contents of the table by EXECuting the following command in Query Analyzer for our new table.
SELECT EmpId, EmpName FROM DummyTable1
GO
EmpID EmpName
1 aaaaaaaaaaaaaaaaaaaa
2 bbbbbbbbbbbbbbbbbbbb
3 cccccccccccccccccccc
4 dddddddddddddddddddd
5 eeeeeeeeeeeeeeeeeeee
6 ffffffffffffffffffff
7 gggggggggggggggggggg
8 hhhhhhhhhhhhhhhhhhhh
9 iiiiiiiiiiiiiiiiiiii
10 jjjjjjjjjjjjjjjjjjjj
As you would expect, the data we inserted earlier has been displayed.
Now, let’s execute the following commands to clear the cache.
DBCC FREEPROCCACHE
GO
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.
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.
SELECT EmpId, EmpName FROM DummyTable1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.syscacheobjects
GO
The result will display many columns, but we are only interested in four of them, as shown below.
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Here’s what the information displayed means:
Cacheobjtype: The type of object stored in the cache, which can include:
• Compiled Plan
• Executable Plan
• Parse Tree
• Cursor Parse Tree
• Extended Stored Procedure
We will be concentrating mainly on the Compiled Plan and the Executable Plan cacheobjtype type objects.
Refcounts: Number of other cache objects referencing this cache object. A count of 1 is the base.
Usecounts: Number of times this cache object has been used since inception.
Sql: Text of the statement.
Now, let’s execute the same SELECT statement:
SELECT EmpId, EmpName FROM DummyTable1
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
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.
Now, let us add a ‘WHERE’ clause on the SELECT statement and see the result from the master.dbo.Syscacheobjects.
SELECT EmpId, EmpName FROM DummyTable1 WHERE EmpId = 5
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
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.
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Let us execute the same SELECT statement with a different empid and verify the result.
SELECT EmpId, EmpName FROM DummyTable1 WHERE EmpId = 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
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.
SELECT EmpId, EmpName FROM DummyTable1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.syscacheobjects
GO
The result will display many columns, but we are only interested in four of them, as shown below.
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Here’s what the information displayed means:
Cacheobjtype: The type of object stored in the cache, which can include:
• Compiled Plan
• Executable Plan
• Parse Tree
• Cursor Parse Tree
• Extended Stored Procedure
We will be concentrating mainly on the Compiled Plan and the Executable Plan cacheobjtype type objects.
Refcounts: Number of other cache objects referencing this cache object. A count of 1 is the base.
Usecounts: Number of times this cache object has been used since inception.
Sql: Text of the statement.
Now, let’s execute the same SELECT statement:
SELECT EmpId, EmpName FROM DummyTable1
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
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.
Now, let us add a ‘WHERE’ clause on the SELECT statement and see the result from the master.dbo.Syscacheobjects.
SELECT EmpId, EmpName FROM DummyTable1 WHERE EmpId = 5
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
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.
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Let us execute the same SELECT statement with a different empid and verify the result.
SELECT EmpId, EmpName FROM DummyTable1 WHERE EmpId = 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
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.
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.
Now, let’s execute the same statement with the username on the SELECT statement and verify the results.
SELECT EmpId, EmpName FROM dbo.DummyTable1 WHERE EmpId = 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
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.
Let us execute the same SELECT statement with different empid and verify the results.
SELECT EmpId, EmpName FROM dbo.DummyTable1 WHERE EmpId = 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Now, let’s execute the same statement with the databasename and username on the SELECT statement and view the results.
SELECT EmpId, EmpName FROM vijay.dbo.DummyTable1 WHERE EmpId = 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [vijay].[dbo].[DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [vijay].[dbo].[DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
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.
Now, let’s execute the same statement with the username on the SELECT statement and verify the results.
SELECT EmpId, EmpName FROM dbo.DummyTable1 WHERE EmpId = 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
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.
Let us execute the same SELECT statement with different empid and verify the results.
SELECT EmpId, EmpName FROM dbo.DummyTable1 WHERE EmpId = 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Now, let’s execute the same statement with the databasename and username on the SELECT statement and view the results.
SELECT EmpId, EmpName FROM vijay.dbo.DummyTable1 WHERE EmpId = 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [vijay].[dbo].[DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [vijay].[dbo].[DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
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.
Now, let’s execute the following commands to clear the cache before the
stored procedure experiment.
DBCC FREEPROCCACHE
GO
Stored Procedures
We will create a stored procedure with one parameter, and see how it is differs from the SELECT statement and views.
CREATE PROC spDummyTable1 (@EmpID Int) AS
SELECT EmpID, EmpName FROM DummyTable1 WHERE EmpID = @EmpID
Now, let’s execute the following commands to display the data and cache information for the spDummyTable1 we created.
EXEC spDummyTable1 1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 spDummyTable1
Compiled Plan 2 1 spDummyTable1
SQL Server displays the compiled and executable plan for the spDummyTable1 stored procedure.
Let us execute the same statement again and see the cache details.
EXEC spDummyTable1 1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 spDummyTable1
Compiled Plan 2 1 spDummyTable1
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.
Let’s execute the same stored procedure with a different empid parameter value and view the cache details.
EXEC spDummyTable1 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 3 spDummyTable1
Compiled Plan 2 1 spDummyTable1
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.
Now, let us execute the same stored procedure with the username and see the cache details.
EXEC dbo.spDummyTable1 5
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 4 spDummyTable1
Compiled Plan 2 1 spDummyTable1
No difference at all. SQL Server has used the same compiled plan for the stored procedure and incremented the Usecounts of the executable plan.
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.
EXEC dbo.spDummyTable1 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
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.
Now, let’s execute the same stored procedure with the databasename and username and see the cache details.
EXEC vijay.dbo.spDummyTable1 7
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 5 spDummyTable1
Compiled Plan 2 1 spDummyTable1
No difference at all. SQL Server has used the same compiled plan for the stored procedure and incremented the Usecounts of the executable plan.
DBCC FREEPROCCACHE
GO
Stored Procedures
We will create a stored procedure with one parameter, and see how it is differs from the SELECT statement and views.
CREATE PROC spDummyTable1 (@EmpID Int) AS
SELECT EmpID, EmpName FROM DummyTable1 WHERE EmpID = @EmpID
Now, let’s execute the following commands to display the data and cache information for the spDummyTable1 we created.
EXEC spDummyTable1 1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 spDummyTable1
Compiled Plan 2 1 spDummyTable1
SQL Server displays the compiled and executable plan for the spDummyTable1 stored procedure.
Let us execute the same statement again and see the cache details.
EXEC spDummyTable1 1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 spDummyTable1
Compiled Plan 2 1 spDummyTable1
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.
Let’s execute the same stored procedure with a different empid parameter value and view the cache details.
EXEC spDummyTable1 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 3 spDummyTable1
Compiled Plan 2 1 spDummyTable1
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.
Now, let us execute the same stored procedure with the username and see the cache details.
EXEC dbo.spDummyTable1 5
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 4 spDummyTable1
Compiled Plan 2 1 spDummyTable1
No difference at all. SQL Server has used the same compiled plan for the stored procedure and incremented the Usecounts of the executable plan.
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.
EXEC dbo.spDummyTable1 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
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.
Now, let’s execute the same stored procedure with the databasename and username and see the cache details.
EXEC vijay.dbo.spDummyTable1 7
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 5 spDummyTable1
Compiled Plan 2 1 spDummyTable1
No difference at all. SQL Server has used the same compiled plan for the stored procedure and incremented the Usecounts of the executable plan.
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.
Now, let’s execute the following commands to clear the cache before the upcoming view experiment.
DBCC FREEPROCCACHE
GO
Views
We will create a view and how it is differs from theSELECT statement and stored procedures.
CREATE VIEW vwDummyTable1 AS
SELECT EmpID, EmpName FROM DummyTable1
GO
Now, let’s execute the following commands to display the data and cache information for the vwDummyTable1 we created.
SELECT EmpId, EmpName from vwDummyTable1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
SQL Server has displayed the compiled and executable plan for the spDummyTable1 stored procedure.
Let’s now execute the same statement again and view the cache details.
SELECT EmpId, EmpName from vwDummyTable1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
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.
Now, let’s add a WHERE clauses on the view SELECT statement and view the results from the master.dbo.Syscacheobjects.
SELECT EmpId, EmpName from vwDummyTable1 WHERE EmpID = 4
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
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.
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Let’s execute the same view SELECT statement with different empid and view the results.
SELECT EmpId, EmpName FROM vwDummyTable1 WHERE EmpID = 8
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
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.
Now, let’s execute the following commands to clear the cache before the upcoming view experiment.
DBCC FREEPROCCACHE
GO
Views
We will create a view and how it is differs from theSELECT statement and stored procedures.
CREATE VIEW vwDummyTable1 AS
SELECT EmpID, EmpName FROM DummyTable1
GO
Now, let’s execute the following commands to display the data and cache information for the vwDummyTable1 we created.
SELECT EmpId, EmpName from vwDummyTable1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
SQL Server has displayed the compiled and executable plan for the spDummyTable1 stored procedure.
Let’s now execute the same statement again and view the cache details.
SELECT EmpId, EmpName from vwDummyTable1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
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.
Now, let’s add a WHERE clauses on the view SELECT statement and view the results from the master.dbo.Syscacheobjects.
SELECT EmpId, EmpName from vwDummyTable1 WHERE EmpID = 4
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
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.
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Let’s execute the same view SELECT statement with different empid and view the results.
SELECT EmpId, EmpName FROM vwDummyTable1 WHERE EmpID = 8
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan 1 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan 2 2 ()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
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.
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.
SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 8
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Though we have given different empid value, SQL Server has used same cache compiled plan and increased the Usecounts value on the Execution plan.
Now, let us execute the same statement with the username on the SELECT statement and verify the results.
SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 8
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
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.
SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 6
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Now, let’s execute the same statement with the databasename and username on the view SELECT statement and verify the results.
SELECT EmpId, EmpName from vijay.dbo.vwDummyTable1 WHERE EmpID = 10
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [vijay].[dbo].[vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [vijay].[dbo].[vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
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.
Now, let’s execute the same statement on the view with the username on the SELECT statement and verify the results.
SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 8
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Though we have given different empid value, SQL Server has used same cache compiled plan and increased the Usecounts value on the Execution plan.
Now, let us execute the same statement with the username on the SELECT statement and verify the results.
SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 8
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
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.
SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 6
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Now, let’s execute the same statement with the databasename and username on the view SELECT statement and verify the results.
SELECT EmpId, EmpName from vijay.dbo.vwDummyTable1 WHERE EmpID = 10
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype Refcounts Usecounts Sql
Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 1 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [vijay].[dbo].[vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan 2 2 (@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [vijay].[dbo].[vwDummyTable1] WHERE [EmpID]=@1
Executable Plan 1 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan 2 2 ()SELECT EmpID, EmpName FROM [vwDummyTable1]
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.