Dangerous ANSI options

ANSI options make SQL Server compatible with the SQL 92 ANSI standard. Here are a few tips to help you avoid ANSI pitfalls.

SQL Server 2000 has a multitude of options that let you tweak its behavior to your advantage. Some of these options,

however, might adversely affect your applications' performance. Knowing more about configuration options can help you avoid such pitfalls.

ANSI options make SQL Server compatible with the SQL 92 ANSI standard -- these options force Transact-SQL to behave much like ANSI SQL. ANSI settings, like any other connection settings, can be defined at the server level, database level or connection level. The Connections tab on the Server Properties dialog within Enterprise Manager lets you specify the value of these options on server level. The Options tab on the database properties dialog (also within Enterprise Manager) lets you configure the same options throughout your database. Finally, selecting Query/Current Connection Properties within Query Analyzer lets you set these options on the connection level. The transact-SQL SET statement allows you to change ANSI settings within stored procedure code.

SQL Server supports the following ANSI settings:

ANSI_NULLS –- specifies whether "< > NULL" is evaluated the same way as "IS NOT NULL"

ANSI_PADDING –- determines whether CHAR, VARCHAR, VARBINARY, NCHAR and NVARCHAR columns are padded with blank characters

ANSI_NULL_DFLT_ON -– specifies whether column is null-able if NOT NULL is not specified explicitly

ANSI_NULL_DFLT_OFF -- Alters the session's behavior to override default nullability of new columns when the ANSI null default option for the database is true.

ANSI_DEFAULTS -– controls all ANSI settings collectively

ANSI_WARNINGS –- determines whether warnings are issued when NULL values are eliminated from aggregate computations

You can check the value of each ANSI setting by executing the sp_dboption system procedure, as in the following:

EXEC sp_dboption pubs, 'ansi_nulls'

Result:

option_name Current_setting
ANSI nulls Off

These settings might look fairly simple and harmless at first glance. However, setting any of the ANSI options OFF within a stored procedure will cause your stored procedures to recompile during each execution. Note that if the option is set OFF on the database level, stored procedures will NOT recompile at each execution. Recompiles only occur if you set these options OFF within a stored procedure.

Recompiling stored procedures is not necessarily a bad thing. However, keep in mind that if a stored procedure is recompiled at each execution, SQL Server won't be able to take advantage of reusing the execution plan. This in turn means that a new plan must be generated for each execution –- SQL Server will have to do extra work and therefore queries will be slower.

If you'd like to double-check and ensure that these settings cause recompile events, just put a trace on a particular connection. Then create a stored procedure that sets any of the settings mentioned above to ON, as in the following:

USE pubs
GO
CREATE PROCEDURE test_ansi_nulls_on_in_pubs
AS
SET ANSI_NULLS OFF
SELECT au_lname, au_fname FROM authors
GO

When you set up the trace, be sure to capture the sp:recompile event under the stored procedures event class on the Profiler's Event tab. You will notice that the recompile event will occur each time you execute this procedure.

In conclusion, if performance matters to you, avoid turning ANSI options OFF within stored procedures.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.

This was first published in November 2002

Dig deeper on SQL Server and .NET development

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close