July 27, 2011 at 12:34 pm
I am working on moving an old system from using ODBC to use ADO.NET. I've found that the former hid a lot of things about SQL Server that ADO.NET does not. Now this isn't a query about why they differ because obviously this isn't the forum for that. However, in my investigations I have found an apparently undocumented feature of the ANSI_PADDING setting. Basically it affects how NOT NULL CHAR column data appears in a result set by padding them when ON and not padding them when OFF regardless of how the data is stored. Can anyone tell me why this occurs and why it is limited to NOT NULL columns?
The attached script displays the behaviour that I describe and its last two queries show the difference. It does the following:
1. Sets default ansi settings.
2. Ensures ansi_padding is on.
3. Creates table with null and not null column.
4. Turns ansi_padding off.
5. Adds another null and not null column to table.
6. Inserts a row into the table.
7. Turns ansi_padding on.
8. Selects data from table.
9. Turns ansi_padding off.
10. Selects data from table.
11. Drops table.
July 27, 2011 at 10:39 pm
Your script returns different results because of the way implicit conversions are handled. In the padding off example, all columns are implicitly converted to VARCHAR(5). Some of these behaviours are indeed undocumented, and maintained for backward compatibility. When setting ansi padding on becomes fully mandatory, these quirks will go away.
You can at least confirm that the data is stored in accordance with the documented behaviour, by running:
SELECT
DATALENGTH(ps.PaddedColumn),
DATALENGTH(ps.PaddedColumnNull),
DATALENGTH(ps.UnpaddedColumn),
DATALENGTH(ps.UnpaddedColumnNull)
FROM dbo.PaddedSelect AS ps
Ref: SET ANSI_PADDING http://msdn.microsoft.com/en-us/library/ms187403.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 28, 2011 at 1:46 am
From this am I right in thinking that as the two queries are the same they both implicitly convert to VARCHAR(5) and that the conversion process is using the ANSI_PADDING setting to determine whether to include or remove trailing blanks?
Thanks for the brilliant response :-). This makes sense now that I think about it and even though padding isn't mentioned in the data type conversion documentation I hadn't made the leap to think about conversion in the first place!
July 28, 2011 at 6:14 am
kieron.dye (7/28/2011)
From this am I right in thinking that as the two queries are the same they both implicitly convert to VARCHAR(5) and that the conversion process is using the ANSI_PADDING setting to determine whether to include or remove trailing blanks?
The plans are different (and note that ANSI_PADDING is a cache key component, so separate plans are cached for the ON and OFF versions of the same query text). The CONVERT_IMPLICIT to VARCHAR(5) only appears in the plan with ANSI_PADDING OFF (look at the properties of the Compute Scalar). This extra conversion is used to honour the semantic of ANSI_PADDING OFF.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply