Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Understanding the Implications of ANSI SQL92 SET Options

By Raj Gill,

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

 

Total article views: 9866 | Views in the last 30 days: 4
 
Related Articles
FORUM

how to devied fullname into firstname and lastname

how to devied fullname into firstname and lastname

FORUM

select multiple wild cards...

i have a need to return multiple query results dealing with a lastname column.

FORUM

Not Selecting All Columns in select query

Not Selecting All Columns in select query

FORUM

Unknown Member & Null Processing

How to handle physical unknown member where some attributes are NULL

FORUM

Conditional Selection of Column

Need to be able to select one column over another depending on the value contained in one of them.

Tags
administration    
database design    
miscellaneous    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones