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

ANSI PADDING, Trailing Whitespace, and Variable Character Columns

By Jack Corbett, (first published: 2008/09/12)

ANSI PADDING, Trailing Whitespace, and Variable Length Character Columns

A few months ago I was involved in a couple of threads on SQLServerCentral regarding how SQL Server deals with trailing spaces/whitespace in variable length character columns (varchar/nvarchar). Here are the links to the 2 threads:


Help with Trim
String comparison with trailing spaces

Based on these 2 threads and my own prior ignorance regarding this, I thought compiling what I learned about it through them and other research would help others better understand how it works. Much of this is also on my blog (www.wiseman-wiseguy.blogspot.com).

What is ANSI_PADDING?

ANSI_PADDING is a session/connection setting in SQL Server that determines if SQL Server handles variable length character data (varchar/nvarchar) according to the ANSI standard. SSMS defaults to ANSI_PADDING on as do other .NET applications and ODBC connections. This is important to remember as Microsoft states that the ANSI_PADDING setting at column creation is what matters, keep this in mind when we run our tests. In the ANSI standard trailing blanks are stored in varchar/nvarchar columns. The following is a chart outlining how SQL Server deals with several objects and operations based on the ANSI_PADDING setting (thanks to Matt Miller in the String comparison with trailing spaces thread):

SQL Object/Operation Result (ANSI Padding ON) Result (ANSI Padding OFF)
Primary Key Trim it. Duplicate key error Trim it
Unique Index/Constraint Trim it. Duplicate key error Trim it
Comparison ("=", "!=") Trim it. 'a' = 'a ' Trim it
Comparison ("like using _") Don't trim it Trim it
Concatenation Don't trim it. 'a ' + 'a' = 'a a' Trim it 'a ' + 'a' = 'aa'
Storage Don't trim it 'a' = 1 byte (2 unicode)
'a ' = 2 bytes (4 unicode)
Trim it 'a' & 'a ' = 1 byte (2 unicode)
Group By Trim it Trim it
Order By Trim it Trim it

The RED rows show where there are differences in behavior.
Now what does this mean to you and me when it comes to development? I had to re-think how I expect varchar/nvarchar to act when dealing with trailing blanks since had I always thought that trailing blanks were removed from varchar/nvarchar and this is not the standard behavior. Here are the ways this has changed how I work:

  1. Use the RTRIM function when inserting data into varchar/nvarchar columns to reduce storage.
  2. Use the RTRIM function on left side operators in concatenation.
  3. How does this affect .NET and other languages? When I use a lookup component in SSIS does it use the trailing blanks so that 'a' != 'a ' or is it like SQL Server where 'a' = 'a '? I know comparison with SSIS and .NET is case-sensitive, but now I need to trim as well.

The second issue is that SQL Server does not treat what I call special characters (tab, carriage return, line feed) as blanks. So while 'a' = 'a ' (space), 'a' != 'a ' (special character). This behavior is also true when considering Primary Keys, Unique Indexes/Constraints, Order By and Group By. This also affects the trim functions as LTRIM and RTRIM trim blanks, so the special characters are not trimmed using them, so you need to take this into account when accepting data from sources where these are possible. In the Help with Trim thread this issue occurred when importing data from an ACCESS database that had special characters on the end and correcting it became an issue because when removing the special characters the user encountered unique constraint errors.


Attached are some tests that verify the behaviors explained. Please take the time to read the comments included in the code.

Resources:

ANSI_PADDING_TESTS.sql
Total article views: 23334 | Views in the last 30 days: 5
 
Related Articles
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...

FORUM

Identifying ASCII characters in NVARCHAR columns

Identifying ASCII characters in NVARCHAR columns

FORUM

MS SQL SERVER 2005 Trail Version Software

MS SQL SERVER 2005 Trail Version Software

FORUM

varchar versus nvarchar

Is it considered best practice to always use nvarchar, or are there some other considerations?

FORUM

Flat File Destination populated with bad characters/blank data fields

Flat File Destination populated with bad characters/blank data fields

Tags
design and theory    
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