Symptoms
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.
Cause
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.
More Information
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.
Resolution
This issue is fixed in the following cumulative updates for SQL Server:
Cumulative Update 2 for SQL Server 2017
Cumulative Update 9 for SQL Server 2016
Cumulative Update 6 for SQL Server 2016 SP1
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Latest cumulative update for SQL Server 2017
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section
References
Learn about the terminologythat Microsoft uses to describe software updates.
