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

       Cumulative Update 8 for SQL Server 2014 SP2

       Cumulative Update 7 for SQL Server 2014 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

Latest cumulative update for SQL Server 2016

Latest cumulative update for SQL Server 2014

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.

Tarvitsetko lisäohjeita?

Kehitä taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Microsoft Insider-käyttäjille

Oliko näistä tiedoista hyötyä?

Mikä vaikutti kokemukseesi?

Kiitos palautteestasi.

×