Sql-server – SQL Server – force NO recompile stored procedure

sql serversql-server-2005stored-procedures

I would like to know if exists some command to force NO RECOMPILE of a stored procedure.
I have a procedure that take about 5 minutes to executed. But when I run directly in the Query windows it takes just few seconds. This sp have a temporary table.

My question is: Is there any way to force a stored procedure to avoid recompilation???

Note: I am using SQL Server 2005.

Best Answer

As was pointed out in the comments, this is almost certainly nothing to do with plan recompilation. If I had to hazard a guess, this is due a bad query plan being caused by parameter sniffing.

Assume that you have an ecommerce website where we can get different sales. We're going to have a lot more addresses in California than I will in Alaska, right? The physical operations that SQL Server is going to perform to read a lot of data (summarized sales in California) is going to be very different than they query to read a little bit of data (summarized sales in Alaska). Sometimes the cached plans are great for only one set of parameters and are horrible for all others. This is often referred to as parameter sniffing.

There's a fantastic article about Parameter Sniffing available on Simple Talk's website. So you can avoid reading that, you don't have too many options apart from specifying OPTION (RECOMPILE) at the statement level, specifying WITH RECOMPILE at the procedure level, or copying the procedure's parameters into local variables and using those to run your parameterized query.

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Best Answer

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Sql-server – How to check if a column exists in a SQL Server table

Related Topic