Index on Views

A normal view does not have any physical representation of its data in SQL Server, It stores as a just metadata information pointing to the underlying tables. But if you created View with unique clustered index then SQL Server will physically materialize the view’s data.

Advantage of Indexed View

• Indexed views gives great performance benefits for queries to retrieve data.
• Indexed views can substantially reduce the amount of I/O required to return data
• SQL Server keeps the indexed view in sync with modifications against the underlying tables. You cannot request to synchronize the view’s contents on demand or on scheduled basis. An indexed view is just like a table index.

Rules for Creating Indexed View

• The Indexed View must have one unique and clustered.After creating a clustered index on a view, you can create additional non-clustered indexes like table.
• The view must be created with the SCHEMABINDING option.

If you want to optimize queries that select data from the employee and department for employees.you can go for Indexed View following code creates the employee indexed view based on a query that joins tbl_emp and tbl_dept.

CREATE VIEW dbo.employee SCHEMABINDING
AS
SELECT emp.emp_id, emp.emp_name ,dept.name
FROM tbl_emp emp
JOIN tbl_dept dept ON emp.dept_id = dept.dept_id

GO
CREATE UNIQUE CLUSTERED INDEX idx_uc_empid ON dbo.employee(emp_id)

SQL Server doesn’t regenerate the whole index whenever the underlying tables are modified. It maintains the index in a smarter manner. When you insert data, SQL Server identifies the affected row of the view and update in the Index.

Updating View Schema or Refreshing Views

After creating View, If underlying table schema has been changed will it reflect in the view ?
the answers is NO because SQL Server stores only metadata information describing the view, its columns,security and dependencies when you create View.

But SQL server provides another way to refresh view after updating underlying table schema,by using the sp_refreshview stored procedure we can refresh view.

The below example shows about refreshing view

CREATE TABLE dbo.emp(id int, emp_id int);
INSERT INTO dbo.T1(id , emp_id) VALUES(1, 2);
GO
CREATE VIEW dbo.view_test
AS
SELECT * FROM dbo.emp;
GO

In the above example SQL Server stored metadata information about the columns that existed at that point in time.

Run the following code to query the view

SELECT * FROM dbo.view_test;

You get the following output with both columns:

id	emp_id
1	2

Next, add a column to emp:
ALTER TABLE dbo.emp ADD emp_name varchar(100)

Now the schema change in emp was not reflected in the view’s metadata information. As far as SQL Server is concerned, the view still has just two columns. If you execute the previous
SELECT query again, you still get only two columns in the output:

id	emp_id
1	2

To refresh the view’s metadata information, run the sp_refreshview stored procedure
against view_test

EXEC sp_refreshview 'dbo.view_test'

Execute the previous SELECT query again, and you get the following output, which includes the new column

id	emp_id	emp_name
1	2	NULL

How Stored Procedure does Reuse of Execution Plans

Execution Plan
While Invoking Procedure SQL Server analyzes and optimizes the queries within the stored procedure and generates an execution plan. An execution plan is a collection of instructions to process the query. These
instructions include which order to access the tables in side the SP.
SQL Server generates multiple execution plans and will choose the one with the lowest cost out of the ones that it generated.

Reuse of Execution Plans
By Default Stored procedures can reuse a previously cached execution plan, thereby saving the resources involved
in generating a new execution plan.

Example for plan reuse. below code creates the get_order_info procedure

CREATE PROC get_order_info
@odate AS DATETIME
AS
SELECT orderid, custid, empid, orderdate /* KPFJ-4959-91D6-F1EC81F8428F */
FROM tbl_order
WHERE orderdate >= @odate

Turn on the STATISTICS IO option to get back I/O information for your session’s activity
SET STATISTICS IO ON

EXEC get_order_info '20080506'

Run the stored procedure for the first time, providing an input with high selectivity (that is, an input for which a small percentage of rows will be returned):

First time the stored procedure is invoked, SQL Server generated an execution plan for it based on the selective input value and cached that plan.

Advantage for Reuse of Execution Plans
Now that you have a plan stored in cache and invocations of the stored
procedure will reuse it. That’s fine if you keep invoking the stored procedure with a highly
selective input. You will enjoy the fact that the plan is reused, and SQL Server will not waste
resources on generating new plans. That’s especially important with systems that invoke
stored procedures very frequently.

Disadvantage for Reuse of Execution Plans

EXEC get_order_info '20120506'

When you invoke same SP 'get_order_info' again with low selectivity (that is, an input for which a high percentage of rows will be returned), In this case the SP reuse the same Execution plan which stored in cache
the problem is this Execution plan created for high selectivity input not for low so the plan efficiency will be reduced and performance too

How to check for Reuse of Execution Plans

The following query is used to find how many time get_order_info's execution plan used

SELECT cacheobjtype, objtype, usecounts, sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE '%sys%'
AND sql LIKE '%KPFJ-4959-91D6-F1EC81F8428F%';

Output:

cacheobjtype objtype usecounts sql
-------------- -------- ---------- ----------------------------
Compiled Plan Proc 2 CREATE PROC dbo.get_order_info...

Notice that one plan was found for the get_order_info procedure in cache, and that it was used
twice (usecounts = 2).

How to Recompile Stored Procedure and Avoid reusing Plans
The RECOMPILE option tells SQL Server to create a new execution plan every time it is invoked.
This option actually tells SQL Server not to bother to cache the plan, hence every invocation
of the procedure ends up creating a new plan because it won’t find an existing one. It is especially useful when the cost of the recompiles is lower than the extra cost associated with reusing suboptimal plans

Example for Recompile Stored Procedure. below code creates the get_order_info procedure

CREATE PROC get_order_info
@odate AS DATETIME
WITH RECOMPILE
AS
SELECT orderid, custid, empid, orderdate
FROM tbl_order
WHERE orderdate >= @odate

How to convert user defined Stored Procedures to System Stored Procedures in sql

How to convert user defined SP to System SP in sql

Converting user user define SP to system SP is very simple method but the question is what is special behavior of System SP .
We can convert system SP in three steps

1. Create a user define SP(which is going to convert)
2. Go to master data base
3. Use sp_MS_marksystemobject SP to convert your SP as System one
4. The SP name must have prefix 'sp_' which you are going to convert
   

EX:

USE master
EXEC sp_MS_marksystemobject sp_get_user_info