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.

Syntax SET ANSI_PADDING ON | OFF
Value ON CHAR(n) Padded with spaces to n, and n spaces retrieved
VARCHAR(n) Trailing spaces are stored and retrieved if data contains them
Value OFF CHAR(n) padded with spaces to n, but no spaces retrieved
VARCHAR(n) Trailing spaces truncated
Default ON
Notes Affects new tables only, does not affect the existing tables
ANSI_PADDING is always ON for VARCHAR(max), NCHAR, NVARCHAR and TEXT
SET ANSI_DEFAULTS ON/OFF automatically sets ANSI_PADDING ON/OFF
Get Current Execute SELECT SESSIONPROPERTY('ANSI_PADDING') or DBCC USEROPTIONS

Versions: Microsoft SQL Server 2008 R2

Related Settings in SQL Server

Related settings for ANSI_PADDING in SQL Server:

ANSI_DEFAULTS Sets a group of ANSI/ISO SQL related settings
ANSI_NULLS Specifies whether =, <> and != can be used to check for NULL

SET ANSI_PADDING Details

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.

ANSI_PADDING and Existing CHAR(n) NOT NULL Columns

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

Resources