BUG: ORDER BY Queries May Truncate Trailing Spaces of Columns

ID: Q188329

The information in this article applies to:

• Microsoft SQL Server version 6.5

SYMPTOMS

If all the following conditions are true, a query that contains an ORDER BY clause will truncate the trailing blanks of the result columns:

• A table is created with ANSI_PADDING enabled.
• The table has rows inserted with trailing blanks.
• A worktable is used by SQL Server to manage the ORDER BY clause.
• The query contains a column list instead of '*' (all columns).
• The client running the SELECT statement does not have ANSI_PADDING currently enabled.

WORKAROUND

Use any of the following methods to work around this problem:

• Enable ANSI_PADDING from all clients.

  -or-

• Use SELECT * (all columns in created order) instead of a SELECT statement with a column list on queries that use the ORDER BY clause.

  -or-

• Create an index to support the ORDER BY clause instead of having SQL Server use a worktable.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

This is not really an issue for ODBC-based clients because the setting ANSI_PADDING is enabled by default.

Additional query words: prodsql work table

Keywords          : kbbug6.50 
Version           : WINNT:6.5
Platform          : winnt
Issue type        : kbbug
Solution Type     : kbpending

Last Reviewed: July 3, 1998