It looks as though the ANSI_PADDING setting is taken from the connection NOT the database default. I ran the following in 2 databases (AdventureWorks which had ANSI_PADDING ON and Northwind, ANSI_PADDING OFF):

[font="Courier New"]SET Ansi_Padding OFF

CREATE TABLE test.categories

   (

   CategoryID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),

   CategoryName NVARCHAR(15)

   )

INSERT INTO test.categories

   (

   CategoryName

   )

   SELECT 'a' UNION ALL

   SELECT 'a ' UNION ALL

   SELECT 'a  ' UNION ALL

   SELECT 'a   ' UNION ALL

   SELECT 'a    '

SELECT CategoryID, CategoryName, LEN(CategoryName) AS len_name, DATALENGTH(CategoryName) AS Datalength_Name FROM test.categories

GO

SET Ansi_Padding ON

CREATE TABLE test.categories2

   (

   CategoryID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),

   CategoryName NVARCHAR(15)

   )

INSERT INTO test.categories2

   (

   CategoryName

   )

   SELECT 'a' UNION ALL

   SELECT 'a ' UNION ALL

   SELECT 'a  ' UNION ALL

   SELECT 'a   ' UNION ALL

   SELECT 'a    'UNION ALL

   SELECT 'a     '

SELECT CategoryID, CategoryName, LEN(CategoryName) AS len_name, DATALENGTH(CategoryName) AS Datalength_Name FROM test.categories2

DROP TABLE test.categories2

DROP TABLE test.categories[/font]

And here were the results in both cases:

ANSI PADDING OFF

CategoryID CategoryName len_name Datalength_Name

----------- --------------- ----------- ---------------

1 a 1 2

2 a 1 2

3 a 1 2

4 a 1 2

5 a 1 10

ANSI PADDING ON

CategoryID CategoryName len_name Datalength_Name

----------- --------------- ----------- ---------------

1 a 1 2

2 a 1 4

3 a 1 6

4 a 1 8

5 a 1 10

6 a 1 12

Also not how once you get to 5 characters in both cases the DataLength returns 10 which I did not expect with ANSI PADDING OFF. Further testing reveals that with ANSI PADDING OFF DataLength appears to return the full length of the character string on the final record returned.