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.

Continues…

Pages: 1 2 3 4 5 6




Related Articles :

  • No Related Articles Found

3 Responses to “Pros & Cons of Using SELECT, Views, and Stored Procedures in SQL Server”

  1. hi,

    I ran the select statement and i got the desired result but when i run the select on syscacheobjects i am seeing only parse tree and compiled plan but i am not able to see the executable plan.

    please reply with valuable suggestions

  2. I am using sql server 2008? i think we can see cache object executable plan in sql 2000 but not in 2005/20008
    am i on the right track?

  3. Hi Vijay,

    Thanks for Good article on Select,View & SP.

    I Need Some Clarification
    I’m using Sql Server 2008

    A). When i Execute Same SQL statement with one same user but diffrent empid,
    then it produce diffrent Compiled Plan.

    B). the above is same for VIEW also.

    C). But for SP uses same Compiled Plan for diffrent empid input.

    I think this is correct behaviour in Sql server 2008.

    What you say regarding SELECT & VIEW is OPPOSITE

    Please Explain

    Regards,
    Anil Vanjre

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |