Microsoft KB Archive/154886

= INF: Behavior of ANSI_PADDING =

Article ID: 154886

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q154886



SUMMARY
This article discusses the behavior of the SET ANSI_PADDING option available with SQL Server version 6.5 and later.



MORE INFORMATION
One of the new SET options introduced with SQL Server 6.5 is ANSI_PADDING. The "What's New in SQL Server 6.5" guide mentions that when ANSI_PADDING is on, Varchar values are padded with blanks and Varbinary values are padded with nulls. Note that this does not mean that all variable columns start behaving like fixed length columns by padding all values entered into the column. It means that if a value is entered in a variable column with trailing blanks or nulls, the trailing blanks or nulls are not automatically removed.

The running of the following script in ISQL/w or Query Analyzer illustrates the behavior of ANSI_PADDING. It builds a table with Char, Varchar and Varbinary columns and inserts values both with and without trailing blanks. The script does this twice, once with ANSI_PADDING on and once with it off, to demonstrate that the trailing blanks are inserted into all columns for the first row when the option is on, and are not inserted when the option is off. It also illustrates that the option does not cause the Varchar columns to be padded out to their full length. It only prevents the truncation of trailing blanks supplied by the user.

USE pubs GO

SET ANSI_PADDING ON GO

PRINT 'Testing with ANSI_PADDING ON' GO

CREATE TABLE t1 (charcol char(16) NULL, varcharcol varchar(16) NULL, varbinarycol varbinary(8)) GO

INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee) INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00) GO

SELECT 'CHAR'='>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',varbinarycol FROM t1 GO

SET ANSI_PADDING OFF GO

PRINT 'Testing with ANSI_PADDING OFF' GO

CREATE TABLE t2 (charcol char(16) NULL, varcharcol varchar(16) NULL, varbinarycol varbinary(8)) GO

INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee) INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00) GO

SELECT 'CHAR'='>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<', varbinarycol FROM t2 GO

DROP TABLE t1 GO

DROP TABLE t2 GO

Keywords: kbinfo kbnetwork KB154886

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.