Creating Index within Stored Procedure

Introduction

One of my friends asking a question is it better to create Index within Stored Procedure depends on the columns used in stored procedure to improve the Join performance and Drop the stored procedure after getting the desired output and just before the ending of Stored Procedure.

Technical Answer

Yes we can create Index within the Stored Procedure

Now we have to think what we answer it

Technically it is possible what my friend is mentioned in the scenario. But performance wise it’s again a problematic solution. That’s means to solve something we again create another problem. To understand it properly let’s take an example of such kind of Stored Procedure.

IF OBJECT_ID(N'[dbo].[sproc_StudentDetails]', N'P')IS NOT NULL

   DROP PROCEDURE [dbo].[sproc_StudentDetails];

GO

CREATE PROCEDURE [dbo].[sproc_StudentDetails]

     (

         @p_StdClass   INT

      )

AS

BEGIN

      -- Creating Non Clustered Index on IDNO

      CREATE NONCLUSTERED INDEX IX_NC_IDNO_StdDtl

              ON tbl_StudentDetails(IDNO);

       CREATE NONCLUSTERED INDEX IX_NC_IDNO_StdMarks

              ON tbl_StudentMarks(IDNO);

      -- Making JOIN on IDNO for Performance Reason

       SELECT a.Roll, a.StdName, b.Marks1, b.Marks2

       FROM   tbl_StudentDetails AS a

              INNER JOIN tbl_StudentMarks AS b ON a.IDNO = b.IDNO;

      -- Droping the Index

       DROP INDEX IX_NC_IDNO_StdDtl ON tbl_StudentDetails;

       DROP INDEX IX_NC_IDNO_StdMarks ON tbl_StudentMarks;

END

GO

Here in this example if we look carefully the No clustered Index is created and after successful joining it again drops.

It is technically perfect.

So what the Problem is

SELECT a.Roll, a.StdName, b.Marks1, b.Marks2

       FROM   tbl_StudentDetails AS a

              INNER JOIN tbl_StudentMarks AS b ON a.IDNO = b.IDNO;

The performance of the JOIN condition is increased due to non clustered index. So we think that we got it.

NO it is NOT.  We have to understand something before implemented it.

When the Index Created the Index table and the Statistical table both are updated, so making index within stored procedure again takes a long time to create index.

By the index creation we solve the Join performance but Index creation time is again a pain faction and slowdown the performance of Stored procedure.

Hope you like it.

Posted by: JOYDEEP DAS