I have one more doubt, will ANSI_PADDING set option affects the datastorage?
No and yes.
No, because the working of LEN and DATA_LENGTH are not affected by this option, they still work exactly the same.
Yes, because as a result of indirect effects, the results can still be different.
Here's a repro:
SET ANSI_PADDING OFF;
CREATE TABLE dbo.Test
(c char(10), nc nchar(10), vc varchar(10), nvc nvarchar(10));
INSERT INTO dbo.Test(c, nc, vc, nvc)
VALUES ('abc ', N'abc ', 'abc ', N'abc ');
SELECT LEN(c), LEN(nc), LEN(vc), LEN(nvc)
SELECT DATALENGTH(c), DATALENGTH(nc), DATALENGTH(vc), DATALENGTH(nvc)
DROP TABLE dbo.Test;
Run this twice, with the ANSI_PADDING option ON and OFF. As you see, the DATALENGTH of the vc column is affected by the option. But not because the function behaviour is changed by the option - the change is in the INSERT statement. Here, the trailing spaces will be removed from the varchar column when the option is off. Since the trailing spaces are removed, only the letters abc are stored, and DATALENGTH correctly reports three bytes used.
Some important warnings:
1. ANSI_PADDING affects varchar columns, but not nvarchar columns. And varchar(max) columns are also excluded.
2. ANSI_PADDING only affects data that is stored in a table. When assigning values to a variable, this option has no effect.
3. ANSI_PADDING is a deprecated option. Both the option and the ANSI_PADDING OFF behaviour will be removed in a future version of SQL Server. Don't use this option in new code, and replace it if it's still being used in existing code.