Article ID: 296559 - Last Review: October 16, 2003 - Revision: 3.2

PRB: ALTER COLUMN Sets TrimTrailingBlanks Property to NO

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

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.

Back to the top

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.

Back to the top

WORKAROUND

Re-create the table and transfer existing data.

Back to the top

MORE INFORMATION

Steps to Reproduce Behavior

Run the following script:

set ansi_padding off
go
create table one (a int, b varchar(50) not null, c varchar(10))
go

Run the sp_help one stored procedure and it returns:
Collapse this tableExpand this table
Nullable TrimTrailingBlanks
a Yes (n/a)
b No Yes
c Yes Yes
Run the following script to alter the nullability of column b.
alter table one alter column b varchar(50) null
Run the sp_help stored procedure one. Note that even though you only wanted to change the nullability, the TrimTrailingBlanks property also changed.
Collapse this tableExpand this table
Nullable TrimTrailingBlanks
a Yes (n/a)
b Yes No - has been changed.
c Yes Yes