When working with variable length character columns (varchar/nvarchar) in SQL Server, it’s important to understand how trailing spaces or whitespace are handled. This article aims to shed light on this topic and provide clarity on the behavior of SQL Server.
First, let’s discuss ANSI_PADDING. ANSI_PADDING is a session/connection setting in SQL Server that determines whether SQL Server handles variable length character data according to the ANSI standard. By default, SSMS, .NET applications, and ODBC connections have ANSI_PADDING turned on. It’s crucial to remember that the ANSI_PADDING setting at column creation is what matters.
In the ANSI standard, trailing blanks are stored in varchar/nvarchar columns. To better understand how SQL Server deals with trailing spaces/whitespace, let’s take a look at the following chart:
| SQL Object/Operation | Result (ANSI Padding ON) | Result (ANSI Padding OFF) |
|---|---|---|
| Primary Key | Trim it | 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 |
| Group By | Trim it | Trim it |
| Order By | Trim it | Trim it |
The chart highlights the differences in behavior based on the ANSI_PADDING setting. It’s important to note that trailing blanks are not removed from varchar/nvarchar columns by default, which may differ from what some developers expect.
So, how does this affect development? Here are a couple of ways it has changed how I work:
- Use the RTRIM function when inserting data into varchar/nvarchar columns to reduce storage.
- Use the RTRIM function on the left side operators in concatenation.
It’s also important to consider how this behavior affects .NET and other languages. For example, when using a lookup component in SSIS, it’s essential to understand whether trailing blanks are considered in comparisons. Additionally, SQL Server does not treat special characters (such as tab, carriage return, and line feed) as blanks. This behavior applies to Primary Keys, Unique Indexes/Constraints, Order By, and Group By operations as well. When accepting data from sources where special characters are possible, it’s crucial to take this into account.
In conclusion, understanding ANSI PADDING and trailing whitespace in SQL Server is essential for developers working with variable length character columns. By being aware of the default behavior and making necessary adjustments, developers can ensure accurate data storage and comparisons.
For more information and code examples, please visit my blog at www.wiseman-wiseguy.blogspot.com.