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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question