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.