What is ANSI_PADDING ?

ANSI_PADDING can be set ON or OFF. It controls the way 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.

Transact SQL Statement to set it ON
SET ANSI_PADDING ON

Transact SQL Statement to set it OFF

SET ANSI_PADDING OFF

ANSI_PADDING setting is ON :
char(n) and binary(n) - Pads value with trailing blanks for char columns and with trailing zeros for binary columns to the length of column.
varchar(n) or varbinary(n) - Trailing blanks in varchar are not trimmed . Trailing zeroes in varbinary are not trimmed. Does not pads trailing blanks or zeroes to the length of column.

ANSI_PADDING setting is OFF :

char(n) and binary(n) - Pads value with trailing blanks for char columns and with trailing zeros for binary columns to the length of column.

varchar(n) or varbinary(n) - Trailing blanks in varchar are trimmed . Trailing zeroes in varbinary are trimmed. Does not pads trailing blanks or zeroes to the length of column.