Assume that you have a Microsoft SQL Server table that has one or more indexes built on it. When you query against the table, SQL Server may perform inconsistent data padding for CHAR and BINARY columns. The inconsistent behavior for returning trailing blanks at the end of the CHAR and BINARY data columns depends on the following conditions:
- The settings of the ANSI_PADDING when columns are created in the SQL Server table.
- The decision to perform either table scan or index seek on the columns.
In certain scenarios, SQL Server may perform an optimization that allows it to bypass many operations for faster data delivery. The issue occurs because SQL Server does not recognize that the optimization cannot be applied for the above scenario.
This behavior of SQL Server does not comply with the behavior that's documented in the following article: Using Char and Varchar data.
If ANSI_PADDING is ON when a char NULL column is created, it behaves the same as a char NOT NULL column: values are right-padded to the size of the column. If ANSI_PADDING is OFF when a char NULL column is created, it behaves like a varchar column with ANSI_PADDING set OFF: trailing blanks are truncated.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section