FIX: Inconsistent behavior for returning trailing blanks at the end of CHAR and BINARY data in SQL Server

Applies to: SQL Server 2016 DeveloperSQL Server 2016 EnterpriseSQL Server 2016 Enterprise Core

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.

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 terminology that Microsoft uses to describe software updates.