Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.
SET ANSI_PADDING {ON | OFF}
Columns defined with char, varchar, binary, and varbinary data types have a defined size.
This setting affects only the definition of new columns. After the column is created, Microsoft® SQL Server™ stores the values based on the setting when the column was created. Existing columns are not affected by a later change to this setting.
Warning It is recommended that ANSI_PADDING always be set to ON.
This table shows the effects of the SET ANSI_PADDING setting when values are inserted into columns with char, varchar, binary, and varbinary data types.
Setting |
char(n) NOT NULL or binary(n) NOT NULL | char(n) NULL or binary(n) NULL |
varchar(n) or varbinary(n) |
|---|---|---|---|
| ON | Pad original value (with trailing blanks for char columns and with trailing zeros for binary columns) to the length of the column. | Follows same rules as for char(n) or binary(n) NOT NULL when SET ANSI_PADDING is ON. | Trailing blanks in character values inserted into varchar columns are not trimmed. Trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column. |
| OFF | Pad original value (with trailing blanks for char columns and with trailing zeros for binary columns) to the length of the column. | Follows same rules as for varchar or varbinary when SET ANSI_PADDING is OFF. | Trailing blanks in character values inserted into a varchar column are trimmed. Trailing zeros in binary values inserted into a varbinary column are trimmed. |
Note When padded, char columns are padded with blanks, and binary columns are padded with zeros. When trimmed, char columns have the trailing blanks trimmed, and binary columns have the trailing zeros trimmed.
The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set ANSI_PADDING to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties set in the application before connecting. SET ANSI_PADDING defaults to OFF for connections from DB-Library applications.
nchar, nvarchar, and ntext columns always display the SET ANSI_PADDING ON behavior, regardless of the current setting of SET ANSI_PADDING.
When SET ANSI_DEFAULTS is ON, SET ANSI_PADDING is enabled.
The setting of SET ANSI_PADDING is set at execute or run time and not at parse time.
SET ANSI_PADDING permissions default to all users.
This example demonstrates how the setting affects each of these data types.
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)
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)
SELECT 'CHAR'='>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
GO
DROP TABLE t1
DROP TABLE t2
GO
| CREATE TABLE | SET ANSI_DEFAULTS |
| INSERT | SET |