SQL Server - SET ANSI_PADDING ON/OFF - Features, Examples and Equivalents

ANSI_PADDING option specifies how trailing spaces are handled in CHAR and VARCHAR columns, and trailing zeros in BINARY and VARBINARY columns.

Versions: Microsoft SQL Server 2008 R2

Related settings for ANSI_PADDING in SQL Server:

By default, ANSI_PADDING is ON, and CHAR columns are padded with blanks, while VARCHAR columns preserve trailing blanks only if they were inserted:

   CREATE TABLE t_pad1 
   ( 
     c1 CHAR(5), 
     c2 VARCHAR(5)
   );
 
   INSERT INTO t_pad1 VALUES ('A', 'A');
   INSERT INTO t_pad1 VALUES ('B  ', 'B  ');
 
   -- Output column values replacing space with 'b' letter
   SELECT REPLACE(c1, ' ', 'b'), REPLACE(c2, ' ', 'b') FROM t_pad1;
   -- Result:
   -- Abbbb    A
   -- Bbbbb    Bb

If ANSI_PADDING is set to OFF, trailing spaces are trimmed from both CHAR and VARCHAR columns (in new tables only):

   SET ANSI_PADDING OFF
 
    CREATE TABLE t_pad2 
   ( 
     c1 CHAR(5), 
     c2 VARCHAR(5)
   );
 
   INSERT INTO t_pad2 VALUES ('A', 'A');
   INSERT INTO t_pad2 VALUES ('B ', 'B ');
 
   -- Output column values replacing space with 'b' letter
   SELECT REPLACE(c1, ' ', 'b'), REPLACE(c2, ' ', 'b') FROM t_pad2;
   -- Result:
   -- A    A
   -- B    B  
 
   -- But for existing table t_pad1
   SELECT REPLACE(c1, ' ', 'b'), REPLACE(c2, ' ', 'b') FROM t_pad1;
   -- Result:
   -- Abbbb    A
   -- Bbbbb    Bb

Note that SET ANSI_PADDING OFF did not affect the existing table t_pad1, and it still preserves spaces.

Setting ANSI_PADDING to OFF trims spaces in the existing CHAR NOT NULL columns:

   SET ANSI_PADDING ON
 
   CREATE TABLE t_pad3 
   ( 
     c1 CHAR(5) NULL, 
     c2 CHAR(5) NOT NULL
   );
 
   INSERT INTO t_pad3 VALUES ('A', 'A');
   INSERT INTO t_pad1 VALUES ('B ', 'B ');
 
   -- CHAR columns are padded with spaces
   SELECT REPLACE(c1, ' ', 'b'), REPLACE(c2, ' ', 'b') FROM t_pad3;
   -- Result:
   -- Abbbb    Abbbb
   -- Bbbbb    Bbbbb
 
   SET ANSI_PADDING OFF
 
   -- Now existing CHAR NOT NULL columns are truncated
   SELECT REPLACE(c1, ' ', 'b'), REPLACE(c2, ' ', 'b') FROM t_pad3;
   -- Result:
   -- Abbbb    A
   -- Bbbbb    B

• SQL Server Reference
• SET ANSI_PADDING - SQL Server 2008 R2 Books Online