Ansi padding statement in SQL Server

The ANSI PADDING statement in SQL Server controls how trailing spaces are handled when inserting or updating values in VARCHAR or VARBINARY column or CHAR or BINARY.

Syntex

SET ANSI_PADDING ON/OFF

By default, this is ON.

When it is on:

Ø  Varchar/nvarchar:  Trailing space are not trimmed when inserted.

Ø  Char/nchar: Pads with spaces to fill the default length.

Ø  Binary: Pads with zeros to fill the default length. 

SET ANSI_PADDING OFF - trims the trailing blanks in the string values when they are inserted into a column. This is not recommended behavior. The ANSI_ PADDING setting affects only the newly created columns; we will create new table each time the ANSI_ PADDING option setting is changed. We will start with setting the ANSI_ PADDING to ON.

Now here we will see the example.

Creating a demo table and inserted some records.

SET ANSI_PADDING ON;  CREATE TABLE ANSI_PADDING_ON_DEMO (     Nullable_char char(15) NULL,     NotNull_char char(15) NOT NULL,     Nullable_varbinary varbinary(15) NULL,     Nullable_varchar varchar(15) NULL,     Nullable_Nvarchar nvarchar(15) NULL       )  INSERT INTO ANSI_PADDING_ON_DEMO VALUES ('Bagesh','Bagesh',0x00ee,'Bagesh','Bagesh'), ('Bagesh  ','Bagesh  ',0x00ee00,'Bagesh  ','Bagesh  ');

Table created successfully.

Now check the length of these columns.

SELECT DATALENGTH(Nullable_char) AS Nullable_char,        DATALENGTH(NotNull_char) AS NotNull_char,        DATALENGTH(Nullable_varbinary) AS Nullable_varbinary,        DATALENGTH(Nullable_varchar) AS Nullable_varchar,        DATALENGTH(Nullable_Nvarchar) AS Nullable_Nvarchar FROM ANSI_PADDING_ON_DEMO;

See the example of  ANSI_PADDING OFF.

SET ANSI_PADDING OFF;  CREATE TABLE ANSI_PADDING_OFF_DEMO (     Nullable_char char(15) NULL,     NotNull_char char(15) NOT NULL,     Nullable_varbinary varbinary(15) NULL,     Nullable_varchar varchar(15) NULL,     Nullable_Nvarchar nvarchar(15) NULL       )  INSERT INTO ANSI_PADDING_OFF_DEMO VALUES ('Bagesh','Bagesh',0x00ee,'Bagesh','Bagesh'), ('Bagesh  ','Bagesh  ',0x00ee00,'Bagesh  ','Bagesh  ');  SELECT DATALENGTH(Nullable_char) AS Nullable_char,        DATALENGTH(NotNull_char) AS NotNull_char,        DATALENGTH(Nullable_varbinary) AS Nullable_varbinary,        DATALENGTH(Nullable_varchar) AS Nullable_varchar,        DATALENGTH(Nullable_Nvarchar) AS Nullable_Nvarchar FROM ANSI_PADDING_OFF_DEMO;

See the data

See the length

SELECT DATALENGTH(Nullable_char) AS Nullable_char,        DATALENGTH(NotNull_char) AS NotNull_char,        DATALENGTH(Nullable_varbinary) AS Nullable_varbinary,        DATALENGTH(Nullable_varchar) AS Nullable_varchar,        DATALENGTH(Nullable_Nvarchar) AS Nullable_Nvarchar FROM ANSI_PADDING_OFF_DEMO;

See the difference between them