ANSI_Padding setting
For a varbinary column created with ANSI padding set OFF, values containing trailing zeros, such as 0x100 will have the trailing zeros truncated prior to the value being written to the column.  This certainly does change the value contained in the column.

This might be useful for a system which displays values in a left-aligned fashion with right-padding zeros added at display-time, thereby reducing storage requirements.  For instance, if your system knows all numbers stored in a column are 8 hex digits, left aligned, then 0x1 becomes 0x01000000 at run-time.  In my opinion, the confusion this would create for future developers would far outweigh the potential space savings. 

Take for example, the following:

USE tempdb;

SET ANSI_PADDING ON;
GO

IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    v varbinary(10) NOT NULL 
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest (v)
VALUES (0x100);

SELECT *
FROM dbo.PadTest;


Result:

╔════════╗
║   v    ║
╠════════╣
║ 0x0100 ║
║ 0x0001 ║
╚════════╝

SET ANSI_PADDING OFF;
GO

IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    v varbinary(10) NOT NULL 
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest (v)
VALUES (0x100)
    , (0x001);

SELECT *
FROM dbo.PadTest;


Result:

╔════════╗
║   v    ║
╠════════╣
║ 0x01   ║
║ 0x0001 ║
╚════════╝

The first table defined with ANSI padding enabled requires more storage for the first row than the second table defined with ANSI padding disabled.  As you can see, this does indeed change the values stored in the table since 0x01 &lt;&gt; 0x0100.

SET ANSI_PADDING OFF; for varchar columns operates in a similar way, replacing the zeros with spaces.  Take the following example:

SET ANSI_PADDING OFF;
GO
IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    [varchar(10)] varchar(10) NOT NULL 
    , [char(10)] char(10) NOT NULL
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest ([varchar(10)], [char(10)])
VALUES ('a', 'a')
    , ('a         ', 'a         ');

SELECT [varchar(10)] = pt.[varchar(10)] + '|'
    , [char(10)] = pt.[char(10)] + '|'
FROM dbo.PadTest pt


╔═════════════╦══════════════╗
║ varchar(10) ║ char(10)     ║
╠═════════════╬══════════════╣
║ a|          ║ a          | ║
║ a|          ║ a          | ║
╚═════════════╩══════════════╝

I've added the pipe symbol to the output to show where padding exists and doesn't exist.

SET ANSI_PADDING ON;
GO
IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    [varchar(10)] varchar(10) NOT NULL 
    , [char(10)] char(10) NOT NULL
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest ([varchar(10)], [char(10)])
VALUES ('a', 'a')
    , ('a         ', 'a         ');

SELECT [varchar(10)] = pt.[varchar(10)] + '|'
    , [char(10)] = pt.[char(10)] + '|'
FROM dbo.PadTest pt


╔═════════════╦══════════════╗
║ varchar(10) ║ char(10)     ║
╠═════════════╬══════════════╣
║ a|          ║ a          | ║
║ a         | ║ a          | ║
╚═════════════╩══════════════╝

The char(10) columns operate "correctly" for both variations of the SET ANSI_PADDING statement - the varchar(10) columns are a bit problematic with SET ANSI_PADDING OFF; since it's removing the spaces from the value.  This is why in Microsoft's ANSI_PADDING documentation, it states:


  We recommend that ANSI_PADDING always be set to ON.