Skip to content

Latest commit

 

History

History
148 lines (110 loc) · 7.12 KB

set-ansi-padding-transact-sql.md

File metadata and controls

148 lines (110 loc) · 7.12 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
SET ANSI_PADDING (Transact-SQL)
SET ANSI_PADDING controls the way the column stores values shorter than the defined size of the column.
WilliamDAssafMSFT
wiassaf
randolphwest
07/08/2024
sql
t-sql
reference
ANSI_PADDING_TSQL
ANSI_PADDING
SET_ANSI_PADDING_TSQL
SET ANSI_PADDING
data types [SQL Server], short values
ANSI_PADDING option
short values [SQL Server]
SET ANSI_PADDING statement
trailing blanks
TSQL
>=aps-pdw-2016 || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

SET ANSI_PADDING (Transact-SQL)

[!INCLUDE sql-asdbmi-asa-pdw-fabricse-fabricdw]

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.

Note

SET ANSI_PADDING OFF, and the ANSI_PADDING OFF database option, are deprecated. In [!INCLUDE _ss2017] and later versions, and [!INCLUDE ssazure-sqldb] and [!INCLUDE ssazuremi-md], ANSI_PADDING is always set to ON. Deprecated features shouldn't be used in new applications. For more information, see Deprecated Database Engine features in SQL Server 2017.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

Syntax for [!INCLUDE ssnoversion-md.md], [!INCLUDE sssodfull-md.md], [!INCLUDE fabric].

SET ANSI_PADDING { ON | OFF }

Syntax for [!INCLUDE ssazuresynapse-md.md] and [!INCLUDE sspdw-md.md].

SET ANSI_PADDING ON

Remarks

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, [!INCLUDE ssNoVersion] stores the values based on the setting when the column was created. Existing columns aren't affected by a later change to this setting.

Note

ANSI_PADDING should always be set to ON.

The following 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 aren't trimmed. Trailing zeros in binary values inserted into varbinary columns aren't trimmed. Values aren't 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.

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.

ANSI_PADDING must be ON when you're creating or changing indexes on computed columns or indexed views. For more information about required SET option settings with indexed views and indexes on computed columns, see Considerations when you use the SET statements.

The default for SET ANSI_PADDING is ON. The [!INCLUDE ssNoVersion] Native Client ODBC driver and [!INCLUDE ssNoVersion] Native Client OLE DB Provider for [!INCLUDE ssNoVersion] 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. The default for SET ANSI_PADDING is OFF for connections from DB-Library applications.

The SET ANSI_PADDING setting doesn't affect the nchar, nvarchar, ntext, text, image, varbinary(max), varchar(max), and nvarchar(max) data types. They always display the SET ANSI_PADDING ON behavior. This means trailing spaces and zeros aren't trimmed.

When ANSI_DEFAULTS is ON, ANSI_PADDING is enabled.

The setting of ANSI_PADDING is defined at execute or run time and not at parse time.

To view the current setting for this setting, run the following query.

DECLARE @ANSI_PADDING VARCHAR(3) = 'OFF';
IF ( (16 & @@OPTIONS) = 16 ) SET @ANSI_PADDING = 'ON';
SELECT @ANSI_PADDING AS ANSI_PADDING;

Permissions

Requires membership in the public role.

Examples

The following example shows how the setting affects each of these data types.

Set ANSI_PADDING to ON and test.

PRINT 'Testing with ANSI_PADDING ON'
SET 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

Now set ANSI_PADDING to OFF and test.

PRINT 'Testing with ANSI_PADDING OFF';
SET 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;

Related content