CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Akon
Akon

Reputation: 272

Re compile stored procedure

I have added non-clustered index to some tables,

Do I need to re-start, or recompile all of the stored procedure to get benefit of the new indexes.

And how to do so?

Upvotes: 1

Views: 260

Answers (3)

ARA
ARA

Reputation: 1316

you need to recompile your stored procedures (triggers, functions), but you you don't need to do it one by one, you can use sp_recompile 'TableName' and SQL will recompile all stored procedures, triggers that references your table and get benefits of your new index (and you won't miss one).

Followup: i leave my post -it is still the BOL answer- but the correct answer seems to be : No you don't (see other post)

Upvotes: -1

SteveB
SteveB

Reputation: 1514

Yes. You will need to re-compile your stored procedures to take advantage of new indexes.

There is a full explanation here

Upvotes: -1

Christos
Christos

Reputation: 53958

No you don't need to. When the stored procedure will try to fetch data from you tables the query optimizer will see that there is an index to some of your tables and it will build the corresponding execution plan, which then it will be passed to execution engine, in order to be executed.

Upvotes: 3

Related Questions