question

rohitchoudhury01 avatar image
rohitchoudhury01 asked

Creating a temporary index in a SQL Server Stored Procedure

I have a requirement to create an index on a table in sql server for optimising a particular SP. Is that okay if I create the index in the SP, do my work and in the end drop the index so that the other SPs are not effected by my index? Is there any harm in dropping the index after use( creating a temporary index)?? The table contains 3 million rows.
sql-serverstored-proceduresindex-performance
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Shawn_Melton avatar image
Shawn_Melton answered
Creating indexes on tables is the sole method of improving most stored procedures. I would not really worry about other stored procedures using it or not. You create a "covering index" to help optimize one specific query, which is your requirement. I don't see a reason to drop that index just so other stored procedures will not be effected. Do you know if the effect is a negative one? If so, then I would look into why it is and fix that over having to drop/create an index on that table. I mean 3 million is not excessively large but can be considerable IO overhead to drop and create an index constantly; depending on how many times the procedure is run. My main concern would be the blocking that would occur from creating the index.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.