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:
- Books Online - Search "Set ANSI_PADDING"
- Web version of BOL
- Using CHAR and VARCHAR data - Explanation of storage and how this is affected by the option.
- SET Options That Affect Results
- Effects of SQL-92 Options
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
