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.
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.
16 People are following this question.
