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

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

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

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.

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

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.