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

Ansi Options Part 1 - ANSI_PADDING

By Steve Jones,

ANSI Options Part 1 - ANSI_PADDING

Introduction

SQL Server conforms to a number of ANSI standards to varying degrees. The level of compliance or even whether a particular object behaves as per an ANSI Standard is usually governed by an option setting. In this series I plan to look at the various options that are available in SQL Server

Recently I started working (again) with Erwin and designed some tables for a new database. I generated the database from within Erwin and everything seemed normal until I went to edit a table in Enterprise Manager. Why I did this is another story, but I got a number of warnings about changing the ANSI_PADDING option for my tables. So I decided to investigate this behavior.

What is ANSI_PADDING

This is an option that helps to determine how CHAR, VARCHAR, BINARY, and VARBINARY columns deal with trailing blanks and how the storage of the items is performed when the size is less than the defined size.

At least that's what Books Online reports as the behavior. However examining the differences in behavior, it appears that CHAR and BINARY fields behave the same regardless of the setting when the columns are set to NOT NULL. In this case, the values are padded to the length of the field (blanks for CHAR and zeros for BINARY).

If the columns allow NULLs, however, the behavior can differ. If set to ON, then the fields are padded. If set to OFF, then the trailing values (spaces for CHAR and zeros for BINARY) are trimmed. This can be a big deal for some applications, so be aware of this.

For variable length columns the behavior is the same whether NULLs are allowed or not. As you might expect, ON will leave the values alone. Trailing blanks or zeros are ignored. If set to OFF, however, the trailing items (spaces for VARCHAR and zeros for VARBINARY) are trimmed. Again, a behavior that might cause some issues, so if you are losing trailing blanks you may want to check this setting.

A couple notes:
1. If the SET ANSI_DEFAULTS is ON, then ANSI_PADDING is set to ON.
2. If you are dealing with computed column indexes or indexed views, then this option must be set to ON.

Unicode

You may notice that the Unicode equivalents of the data types above are not mentioned. The reason is that these data types always follow the ANSI_PADDING ON behaviors, regardless of the setting. So if you use these data types, this does not apply to you.

What to do?

The default setting is ANSI_PADDING set to ON. And this is also what is recommended in Books Online. I've never bothered changing defaults, so it hasn't affected me, but some of the tools out there that you may be using may default to OFF, so beware. You might spend quite a bit of time chasing a bug because of this behavior..

Keep in mind this option can be set at the connection level, so be wary of making changes from the defaults.

References:

Conclusions

Nothing earth shattering in this article. Indeed, most of this information can be found in Books Online. However, it was an area that I have not really studied because I have not needed it, however it came to my attention during a design project and if I had not caught this early on, it might have caused me issues in the queries that look at data with trailing spaces.

I would be interested to hear if anyone actually sets this to OFF and the reasoning.

As always I welcome feedback on this article using the "Your Opinion" button below. Please also rate this article.

Steve Jones
©dkRanch.net June 2002
Return to Steve Jones Home

 

Total article views: 5791 | Views in the last 30 days: 1
 
Related Articles
FORUM

Removing trailing spaces in a table column

Removing trailing spaces in a table column

FORUM
ARTICLE

SQL Server Stored Procedures and SET options

SET options on stored Procedures work a bit different when compared with ad-hoc queries. These diffe...

SCRIPT

Trim Trailing Blanks in SQL Queries

Reformats the text output of queries to trim trailing blanks in wide varchar columns for easy copy-a...

ARTICLE

Ansi Options Part 2 - ANSI_NULLS

The behavior of SQL Server is influenced in many ways by the various settings and options available....

Tags
configuring    
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