INF: Behavior of ANSI_PADDING

Last reviewed: April 9, 1997
Article ID: Q154886

The information in this article applies to:
  • Microsoft SQL Server, version 6.5

SUMMARY

This article discusses the behavior of the SET ANSI_PADDING option introduced with SQL Server 6.5.

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 will be padded with blanks and Varbinary values will be 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 illustrates the behavior of ANSI_PADDING. It builds a table with Colb as a Varchar column 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 Colb 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
   drop table Padded
   go
   SET ANSI_PADDING ON
   go
   print 'COLB WITH ANSI_PADDING ON:'
   go
   create table Padded
   (cola  char(15) NOT NULL,
   colb  varchar(15))
   go
   insert into Padded values ('One','Trailing    ')
   insert into Padded values ('two','No Trailing')
   go
   select  cola,
   LenCola = datalength(cola),
   DispCola = '#' + cola + '#',
   colb,
   LenColb = datalength(colb),
   DispColb = '#' + colb + '#'
   from Padded
   go
   SET ANSI_PADDING OFF
   go
   drop table Padded
   go
   print 'COLB WITH ANSI_PADDING OFF:'
   go
   create table Padded
   (cola   char(15) NOT NULL,
   colb  varchar(15))
   go
   insert into Padded values ('One','Trailing    ')
   insert into Padded values ('two','No Trailing')
   go
   select  cola,
   LenCola = datalength(cola),
   DispCola = '#' + cola + '#',
   colb,
   LenColb = datalength(colb),
   DispColb = '#' + colb + '#'
   from Padded
   go


Additional query words:
Keywords : kbnetwork SSrvProg
Version : 2.65.0201 6.5
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 9, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.