PRB: ALTER COLUMN Sets TrimTrailingBlanks Property to NO (296559)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q296559

SYMPTOMS

When altering an existing varchar column on a table, no matter what the setting is for the ANSI PADDING option at the time of alteration, the TrimTrailingBlanks property for the altered column is always set to NO.

This behavior may potentially break applications that are sensitive to padded spaces.

CAUSE

This behavior is by design. ANSI NULL defaults and ANSI PADDING are always set to ON for an ALTER COLUMN statement. For more information, refer to the "ALTER TABLE (T-SQL)" topic in SQL Server Books Online.

WORKAROUND

Re-create the table and transfer existing data.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Run the following script:
    set ansi_padding off
go
create table one (a int, b varchar(50) not null, c varchar(10))
go


  2. Run the sp_help one stored procedure and it returns:
    NullableTrimTrailingBlanks
    aYes(n/a)
    bNoYes
    cYesYes


  3. Run the following script to alter the nullability of column b. 
    alter table one alter column b varchar(50) null


  4. Run the sp_help stored procedure one. Note that even though you only wanted to change the nullability, the TrimTrailingBlanks property also changed.
    NullableTrimTrailingBlanks
    aYes(n/a)
    bYesNo - has been changed.
    cYesYes
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbprb KB296559
©2003 Microsoft Corporation. All rights reserved.