Click or drag to resize

SA0089 : The option has a not recommended value SET which will cause the stored procedure to be recompiled

The topic describes the SA0089 analysis rule.

Message

The option has a not recommended value SET which will cause the stored procedure to be recompiled

Description

This rule checks the stored procedures for use of SET statements setting certain options to not recommended value.

The following options are recommended to be ON:

  • ARITHABORT

  • ANSI_NULL_DFLT_ON

  • ANSI_DEFAULTS

  • ANSI_WARNINGS

  • ANSI_PADDING

  • CONCAT_NULL_YIELDS_NULL

The following option is recommended to be OFF:

  • NUMERIC_ROUNDABOUT OFF

If you change the value of any of the above options inside the body of a stored procedure, it will cause the procedure to be recompiled every time it runs. Changing these options triggers a recompilation, because the it may affect the query results.

A SET statement that sets one of the above options to a not recommended value is flagged by this rule.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Maintenance Rules

Additional Information
Example Test Script
 1CREATE PROCEDURE test_recompile AS
 2
 3SET QUOTED_IDENTIFIER OFF 
 4SET ANSI_NULLS OFF 
 5SET ARITHABORT OFF 
 6SET ANSI_NULL_DFLT_ON OFF
 7SET ANSI_DEFAULTS OFF
 8SET ANSI_WARNINGS OFF 
 9SET ANSI_PADDING OFF
10SET CONCAT_NULL_YIELDS_NULL OFF
11SET NUMERIC_ROUNDABORT ON 
12SET NOCOUNT ON 
13SET ROWCOUNT 100 
14SET XACT_ABORT ON 
15SET IMPLICIT_TRANSACTIONS ON
16SET ARITHIGNORE OFF 
17SET LOCK_TIMEOUT 1
18SET FMTONLY ON 
19SET NOEXEC ON
20SET PARSEONLY OFF
21
22SELECT au_lname, au_fname, au_id from authors
23WHERE au_lname like 'L%'

Analysis Results

 MessageLineColumn
1SA0089 : The SET ARITHABORT option to OFF will cause the stored procedure to be recompiled everytime it is execued.54
2SA0089 : The SET ANSI_NULL_DFLT_ON option to OFF will cause the stored procedure to be recompiled everytime it is execued.64
3SA0089 : The SET ANSI_DEFAULTS option to OFF will cause the stored procedure to be recompiled everytime it is execued.74
4SA0089 : The SET ANSI_WARNINGS option to OFF will cause the stored procedure to be recompiled everytime it is execued.84
5SA0089 : The SET ANSI_PADDING option to OFF will cause the stored procedure to be recompiled everytime it is execued.94
6SA0089 : The SET CONCAT_NULL_YIELDS_NULL option to OFF will cause the stored procedure to be recompiled everytime it is execued.104
See Also

Other Resources