SQLServerCentral Article

Understanding the Implications of ANSI SQL92 SET Options

,

Introduction

Have you ever experienced a T-SQL query, a stored procedure, view or a trigger

returning unexpected results? As you investigate, you get a suspicion that it

might have something to do with ANSI DEFAULTS?  ANSI DEFAULTS is probably the

last thing you would think of. In such cases, you are probably thinking that

either it’s a SQL Server bug or a bug in your code or simply the data in the

tables is bad. You would be surprised by the manner in which ANSI settings

impact how SQL Server stores data, how NULL comparisons are evaluated and how

T-SQL queries search and process data. Well, this article attempts to resolve

some of the mystery surrounding ANSI DEFAULTS options in SQL Server and how it

can impact your application behavior.

 

First, lets talk about what ANSI DEFAULTS options are all about. ANSI X3.135-1992

(also referred to as SQL-92 and ANSI SQL) is the industry standard for Database

Language SQL. T-SQL in Microsoft SQL Server 2000 provides the SET command to

manipulate ANSI DEFAULTS settings that specify some SQL-92 standard behavior.

Together, the SQL-92 standard SET options, including ANSI_NULLS,

ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNING, CURSOR_CLOSE_ON_COMMIT,

IMPLICIT_TRANSACTIONS AND QUOTED_IDENTIFIER, define the query-processing

environment of a SQL Server session. These options, however, do not include all

of the options required to conform to the SQL-92 standard.

The options can be manipulated by the T-SQL SET command and can have a paramount

affect on how SQL Server stores and retrieves data. For those interested in

digging deeper in to ANSI SQL specs I

would recommend checking out

http://web.ansi.org/public/std_info.html.

 

Often, developers and DBAs underestimate the impact of these settings on SELECT

statements, stored procedures, triggers and views. The impact can lead to

unexpected application behavior and hours of research and troubleshooting. The

troubleshooting task can become challenging since ANSI options are set at

several levels within Microsoft SQL Server 2000, as well as at the connection

level by the client application. For example,

the SQL Server ODBC driver and Microsoft OLE

DB Provider for SQL Server automatically set ANSI_DEFAULTS to ON. Similarly,

some ANSI options can be configured using ODBC data sources, ODBC connection

attributes, or OLE DB connection properties.

 

Further

in the article, I will explain how ANSI DEFAULTS settings work in SQL Server. In

addition, through problem scenarios, I will explain the behavior of two very

common ANSI options namely; ANSI PADDING and ANSI NULLS and demonstrate their

impact on T-SQL queries.  I will discuss some T-SQL functions and commands that

are available to identify and recover from such scenarios.

 

How does

Microsoft SQL Server manage ANSI SQL-92 SET options?

First it’s important to understand how SQL Server

handles SQL-92 standard SET options. In Microsoft SQL Server these SET options

are defined at three levels – server, database and session.

The database setting overwrites the server setting, and

the session setting overwrites the server and database setting. Let’s take a

look at these three levels independently and understand how ANSI settings are

handled at each of these levels. The sp_configure system stored procedure has

the ‘user options’ setting that allows manipulation of server-wide ANSI settings

and it works across databases.   For example, the following script sets

server-wide ANSI_NULL settings:

USE master

go

EXEC

sp_configure 'user options', '32'

go

RECONFIGURE

WITH OVERRIDE

go

The sp_dboption system stored procedure allows

manipulation of database settings and works at the database level only. For

example, the following script will cause all comparisons to a null value made in

the Northwind database to evaluate to unknown.

USE master

go

sp_dboption 'Northwind',

'ANSI nulls', TRUE

go

And finally, the SET statement allows manipulation of

ANSI settings at the session level. It overrides database option settings (set

by using sp_dboption). For example, the following statement will cause the

statement - “SELECT * from table_name WHERE column_name

= NULL to return zero rows even if there are null values in column_name.

           

SET

ANSI_NULLS ON

Having discussed how

ANSI_DEFAULTS SET options work in SQL Server 2000 now lets take a look at two

specific scenarios. Scenario 1 demonstrates ANSI PADDING behavior

and Scenario 2 explains the impact of ANSI NULLS.

Scenario 1 – ANSI_PADDING

Client applications often access data at the backend by utilizing some sort of

search criteria to return result sets. In most of these queries, when searching

a char or varchar data type, significant spaces padded at the end of the

characters can affect the final outcome of the query. The ANSI PADDING option

directly impacts how SQL Server stores char, binary, varchar and varbinary data.

In this scenario we will focus on character values only. Depending on whether

this option is set to ON or OFF at the time of creating the table, SQL Server

may or may not trim trailing blanks while storing the table’s values.

Script A demonstrates this behavior. It creates the table called Members

in four different ways with following combinations of ANSI_PADING and column

data types:

  1. ANSI_PADDING ON and columns defined as char

  2. With ANSI_PADDING OFF and columns defined as char

  3. With ANSI_PADDING ON and columns defined as varchar

  4. With ANSI_PADDING OFF and columns defined as varchar<

Two records are inserted in the Members table. Both

records are similar except that the column LastName in the first record has a

value of ‘Doe’ and in the second record has a value of ‘Doe  ‘ (padded with two

blank spaces). A SELECT statement is then run against the Members table using

search criteria on column LastName. For demonstration purposes I have used the

LIKE operator to implement the search as – “WHERE LastName LIKE ‘Doe %’ “(Doe

followed by a space and % sign which acts as a wild card).

The results of Script A are interesting.

From Results A, notice that the output returned by the SELECT

statement when the ANSI PADDING is OFF, irrespective of whether LastName is

defined as CHAR or VARCHAR datatype are the same (zero rows returned). What this

means is ‘Doe  ‘ (with padded spaces) gets stored as ‘Doe’ (no spaces padded).

Therefore, WHERE LIKE = ‘Doe %’ evaluates to zero rows found the in the table.

But when ANSI PADDING is ON the results are much different.  Two rows are

returned if LastName is defined as char and one row is returned if LastName is

defined as varchar.  Looking at Results A a bit closer, we can see

that when ANSI_PADDING is ON and the column is defined as a varchar column,

trailing blanks are not trimmed and therefore impact results returned by search

based on a padded string. In addition, it is important to note that the value

of ANSI_PADDING at the time of table creation will be in effect for the life of

the table.  Once the table has been created, setting this option to ON or OFF

will not alter how character values are stored.   So, you can see that its

important for DBAs and developers to clearly understand this behavior when

defining their schema and writing code to search through char, varchar, binary

and varbinary columns.

SCRIPT A

SET NOCOUNT ON

go

Print 'With

ANSI_PADDING ON and column type char'

SET ANSI_PADDING

ON

GO

CREATE TABLE

Members (Firstname char(20), LastName char(20))

GO

INSERT Members

values ('John', 'Doe')

INSERT Members

values ('John', 'Doe  ')

GO

SELECT FirstName,

LastName FROM Members WHERE LastName like 'Doe %'

SELECT 'LastName

column length' = datalength(lastname) FROM Members

GO

Drop table

Members

go

Print 'With

ANSI_PADDING OFF and column type char'

 

SET ANSI_PADDING

OFF

GO

CREATE TABLE

Members (Firstname char(20), LastName char(20))

GO

INSERT Members

values ('John', 'Doe')

INSERT Members

values ('John', 'Doe  ')

GO

SELECT FirstName,

LastName FROM Members WHERE LastName like 'Doe %'

SELECT 'LastName

column length' = datalength(lastname) FROM Members

GO

Drop table

Members

go

Print 'With

ANSI_PADDING ON and column type varchar'

 

SET ANSI_PADDING

ON

GO

CREATE TABLE

Members (Firstname varchar(20), LastName varchar(20))

GO

INSERT Members

values ('John', 'Doe')

INSERT Members

values ('John', 'Doe  ')

GO

SELECT FirstName,

LastName FROM Members WHERE LastName like 'Doe %'

SELECT 'LastName

column length' = datalength(lastname) FROM Members

GO

Drop table

Members

go

Print 'With

ANSI_PADDING OFF and column type varchar'

 

SET ANSI_PADDING

OFF

GO

CREATE TABLE

Members (Firstname varchar(20), LastName varchar(20))

GO

INSERT Members

values ('John', 'Doe')

INSERT Members

values ('John', 'Doe  ')

GO

SELECT FirstName,

LastName FROM Members WHERE LastName like 'Doe %'

SELECT 'LastName

column length' = datalength(lastname) FROM Members

 

GO

Drop table

Members

Go

 

 

RESULTS A

 

 

With

ANSI_PADDING ON and column type char

FirstName           

LastName             

-------------------- --------------------

John                 Doe                

John                 Doe                

 

LastName

column length

----------------------

20

20

 

With

ANSI_PADDING OFF and column type char

FirstName          

 LastName            

-------------------- --------------------

 

LastName

column length

----------------------

3

3

 

With

ANSI_PADDING ON and column type varchar

FirstName           

LastName            

-------------------- --------------------

John

                Doe 

 

LastName

column length

----------------------

3

5

 

With

ANSI_PADDING OFF and column type varchar

FirstName           

LastName            

-------------------- ------------- -------

 

LastName

column length

----------------------

3

3

Another important aspect of the behavior demonstrated by

Script A is that ANSI PADDING affects how SQL Server internally

stores the LastName values. The behavior is determined by the DATALENGTH

function, which returns the number of bytes for a given expression. DATALENGTH

returns a value of 20 for both rows when ANSI_PADDING is ON and LastName is

defined as char. Whereas, it returns 3 and 5 for the two rows when ANSI_PADDING

is ON and LastName is defined as varchar. This is not within the scope of this

article but its worth noting.

How to recover from the affects of ANSI_PADDING?

Regardless of the ANSI_PADDING setting (whether its ON or OFF), essentially, you

need to be aware of two time frames – creation of the

tables and execution of the SET statement. It is the combination of the two that

determine how the values will be stored in the table and how search conditions

involving padded strings will be evaluated. To know the value of the

ANSI_PADDING setting at the time of execution of the CREATE TABLE statement you

can use the COLUMNPROPERTY function.

 

SELECT

COLUMNPROPERTY (OBJECT_ID (‘Members’), ‘LastName’, ‘UsesAnsiTrim’)

 

A

returned value of 1 means ANSI_PADDING was ON when the table was created and a

value of 0 means it was OFF.

 

Since most Transact-SQL SET statements are interpreted

during execution it may be important to identify the exact SET options that the

user connection inherits. DBCC USEROPTIONS can be used to determine the SET

options inherited by a session.

DBCC USEROPTIONS

For

example, DBCC USERIPTIONS can tell us what ANSI SET options are in affect for a

particular SQL server user session. Below is a sample output of this command:

Set

Option                              

Value                                         

-----------------------                 -----------------        

textsize                       

           64512

language                                 us_english

dateformat                   

           mdy

datefirst                       

           7

quoted_identifier                     SET

arithabort                    

            SET

nocount                 

                 SET

ansi_null_dflt_on                     SET

ansi_defaults                

         SET

ansi_warnings              

         SET

ansi_padding               

           SET

ansi_nulls                    

           SET

concat_null_yields_null           SET

Developers commonly use the RTRIM function to shield their application against

problems arising from padded strings. Script AA below demonstrates

how the use of RTRIM in Scenario-I can resolve the search problem.

SCRIPT AA

SET NOCOUNT ON

go

Print 'With

ANSI_PADDING ON and column type char'

SET ANSI_PADDING

ON

GO

CREATE TABLE

Members (Firstname char(20), LastName char(20))

GO

INSERT Members

values ('John', 'Doe')

INSERT Members

values ('John', 'Doe  ')

GO

declare @string

char (5)

select @string

='Doe '

SELECT FirstName,

LastName FROM Members WHERE LastName like RTRIM(@string)+'%'

SELECT 'LastName

column length' = datalength(lastname) FROM Members

GO

Drop table

Members

go

Print 'With

ANSI_PADDING OFF and column type char'

 

SET ANSI_PADDING

OFF

GO

CREATE TABLE

Members (Firstname char(20), LastName char(20))

GO

INSERT Members

values ('John', 'Doe')

INSERT Members

values ('John', 'Doe  ')

GO

declare @string

char (5)

select @string

='Doe '

SELECT FirstName,

LastName FROM Members WHERE LastName like RTRIM(@string)+'%'

SELECT 'LastName

column length' = datalength(lastname) FROM Members

GO

Drop table

Members

go

Print 'With

ANSI_PADDING ON and column type varchar'

 

SET ANSI_PADDING

ON

GO

CREATE TABLE

Members (Firstname varchar(20), LastName varchar(20))

GO

INSERT Members

values ('John', 'Doe')

INSERT Members

values ('John', 'Doe  ')

GO

declare @string

char (5)

select @string

='Doe '

SELECT FirstName,

LastName FROM Members WHERE LastName like RTRIM(@string)+'%'

SELECT 'LastName

column length' = datalength(lastname) FROM Members

GO

Drop table

Members

go

Print 'With

ANSI_PADDING OFF and column type varchar'

 

SET ANSI_PADDING

OFF

GO

CREATE TABLE

Members (Firstname varchar(20), LastName varchar(20))

GO

INSERT Members

values ('John', 'Doe')

INSERT Members

values ('John', 'Doe  ')

GO

declare @string

char (5)

select @string

='Doe '

SELECT FirstName,

LastName FROM Members WHERE LastName like RTRIM(@string)+'%'

SELECT 'LastName

column length' = datalength(lastname) FROM Members

GO

Drop table

Members

Go

 

Scenario II – ANSI NULLS

ANSI_NULLS SET option controls comparisons against NULL values. Similar to

ANSI_PADDING, ANSI_NULLS can affect results returned by a SELECT statement with

a WHERE condition on NULL values. When this option is set ON NULLs evaluate to

unknown. It is often necessary when implementing business logic to do this type

of comparison and if ANSI_NULLS is not set appropriately then it can lead to

unexpected results and affect your application’s behavior. Script B

demonstrates this scenario. It inserts two rows in the Members table, one with

LastName ‘Doe’ and second with LastName having a value of NULL. When ANSI_NULLS

is set to ON, the SELECT statement returns zero rows since the search condition

‘WHERE LastName=NULL’ evaluates to unknown. When ANSI_NULLS is set to OFF, it

returns one row.

SCRIPT B

SET NOCOUNT ON

go

Print 'With

ANSI_NULLS ON '

SET ANSI_NULLS

ON

GO

CREATE TABLE

Members (Firstname char(20), LastName char(20))

GO

INSERT Members

values ('John', 'Doe')

INSERT Members

values ('John', NULL)

GO

SELECT FirstName,

LastName FROM Members WHERE LastName=NULL

GO

Drop table

Members

go

Print 'With

ANSI_NULLS OFF '

 

SET ANSI_NULLS

OFF

GO

CREATE TABLE

Members (Firstname char(20), LastName char(20))

GO

INSERT Members

values ('John', 'Doe')

INSERT Members

values ('John', NULL)

GO

SELECT FirstName,

LastName FROM Members WHERE LastName=NULL

GO

Drop table

Members

Go

  

RESULTS B

                

With ANSI_NULLS ON

FirstName           

LastName            

-------------------- --------------------

         

With

ANSI_NULLS OFF

FirstName           

LastName            

-------------------- --------------------

John                 NULL

 

How to recover from the affects of ANSI_NULL?

In order to avoid the unwanted implications of ANSI_NULL setting on your

application, it is best to use the IS NULL or IS NOT NULL clause to test for a

NULL value. Script BB demonstrates how the IS NULL clause resolves

the problem shown in Scenario II.

SCRIPT BB

SET NOCOUNT ON

go

Print 'With

ANSI_NULLS ON '

SET ANSI_NULLS

ON

GO

CREATE TABLE

Members (Firstname char(20), LastName char(20))

GO

INSERT Members

values ('John', 'Doe')

INSERT Members

values ('John', NULL)

GO

SELECT FirstName,

LastName FROM Members WHERE LastName IS NULL

GO

Drop table

Members

go

Print 'With

ANSI_NULLS OFF '

 

SET ANSI_NULLS

OFF

GO

CREATE TABLE

Members (Firstname char(20), LastName char(20))

GO

INSERT Members

values ('John', 'Doe')

INSERT Members

values ('John', NULL)

GO

SELECT FirstName,

LastName FROM Members WHERE LastName IS NULL

GO

Drop table

Members

go

It is slightly more

complicated to resolve ANSI_NULL problems when dealing with stored procedures,

triggers and views as the ANSI_NULLS setting is saved when these objects are

created or altered and is enabled (used) upon their execution. In other words,

these objects inherit the ANSI settings of the user session that is used to

creates them.  Script C below demonstrates this behavior. It

creates the stored procedure, ‘test1’, first with ANSI_NULLS ON and then with

ANSI_NULLS OFF. Before the execution of ‘test1’ the ANSI_NULLS setting is

switched. So if it was created with ANSI_NULLS ON then it’s executed with

ANSI_NULLS OFF and vice versa. The procedure initializes a variable @var to NULL

and then does a simple NULL comparison of the variable as “IF @var = NULL”. In

this case the IF statement should always evaluate to TRUE. But looking at

Results C, you will notice that the execution of the stored procedure

‘test1’ seem to ignore the ANSI_NULL setting of the user session that issues the

execute statement. The stored procedure inherits the ANSI_NULL setting that

existed at object creation time, and ignores the current setting.  I have seen

many times where this behavior is cast off as a bug. This is expected behavior

within the SQL Server product and all DBAs and developers need to be familiar

with it. The same behavior applies to triggers and views as well.

SCRIPT C

Print 'Object created with

ANSI_NULLS ON '

SET ANSI_NULLS ON

GO

create proc test1 as

declare @var int

select @var= null

If @var= NUll

select ' it is null'

else select ' it is not null'

go

exec test1

set ansi_nulls off

go

exec test1

SELECT OBJECTPROPERTY (OBJECT_ID('test1'),

'ExecIsAnsiNullsOn')

go

drop proc test1

go

Print 'Object created with

ANSI_NULLS OFF '

SET ANSI_NULLS OFF

GO

create proc test1 as

declare @var int

select @var= null

If @var= NUll

select ' it is null'

else select ' it is not null'

go

exec test1

set ansi_nulls on

go

exec test1

SELECT OBJECTPROPERTY (OBJECT_ID('test1'),

'ExecIsAnsiNullsOn')

go

drop proc test1

go

 

 

RESULTS

C

 

 Object

created with ANSI_NULLS ON

---------------

 it is not

null

---------------

 it is not

null

-----------

1

Object

created with ANSI_NULLS OFF

-----------

 it is null

-----------

 it is null

-----------

0

 

Just like we saw in Scenario I, the

situation described by Script C warrants the need to know the

ANSI_NULLS settings at the time of object creation. Again, you can use the

OBJECTPROPERTY function to determine this.  For example:

SELECT

OBJECTPROPERTY (OBJECT_ID('procname'), 'ExecIsAnsiNullsOn')

In the above statement, a

returned value of 1 means ANSI NULL was ON when the stored procedure was created

and a value of 0 means it was OFF.       

Conclusion

As you can see from

Scenario I and II, it can be a bit puzzling to clearly understand odd query

behaviors, which are impacted by changes in ANSI SET options.  It is important

to understand how SQL Server implements the SET options. Since the SET options

can be turned on and off at any time by users and applications, the developers

and DBAs should test their code with the ANSI_DEFAULTS SET options turned both

ON and OFF. When set to ON, this option enables the following SQL-92 settings:

SET ANSI_NULLS

SET CURSOR_CLOSE_ON_COMMIT

SET ANSI_NULL_DFLT_ON

SET IMPLICIT_TRANSACTIONS

SET ANSI_PADDING

SET QUOTED_IDENTIFIER

SET ANSI_WARNINGS.

 

This ensures that the behavior of T-SQL scripts, stored procedures, triggers and

views can be verified and corrected regardless of what options a particular

connection may have turned on using the SET statement prior to invoking the

object. In my next article I will discuss scenarios on

ARITHABORT, ANSI_WARNING, IMPLICIT_TRANSACTIONS

AND QUOTED_IDENTIFIER options. We will discuss how these options can affect your

application behavior and ways to avoid spending hours tracking down the problem.

 

Additional Resources

http://web.ansi.org/public/std_info.html

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_03_35df.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_8qlu.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_6_015_0tf7.asp

 

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating