Regarding Indexes And the Recompile option

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

1. Is it necessary for a stored procedure to be recompiled after creating an index for a row column that is accessed by the procedure?

2.I have a column that has only two values with one value being accessed often. Should I create an index for it?

-- Anonymous, June 26, 1999

Answers

John,

1. Yes. From SQL Server (6.5) Books Online: Title: sp_recompile System Stored Procedure "The queries used by stored procedures and triggers are optimized only once, when they are compiled. As you add indexes or make other changes to your database that affect its statistics, your compiled stored procedures and triggers may lose efficiency. By recompiling the stored procedures and triggers that act on a table, you can reoptimize the queries for greatest efficiency."

2. Sure. Why not?

Hope this helps,

Eric

-- Anonymous, June 29, 1999


Moderation questions? read the FAQ