Reginald J Ray Jr (9/12/2008)
I'm confused. I get the same results from 'select * from duh' for both settings of ANSI_PADDING.
set ANSI_PADDING off
create table duh (
insert into duh select 'a ' + 'a'
select * from duh
drop table duh
Okay, maybe I was not clear. The ANSI_PADDING setting is based on the connection setting. So if a table is created with ANSI_PADDING OFF then inserts and updates to varchar columns in that table will not have include the trailing spaces while nvarchar columns will take on the characteristics of the ANSI_PADDING setting at insert.
If you download and run the script I provided with the article you can see the inconsistent behavior which, IMHO, is a big part of the issue. I don't want to have to run profiler to see how all my developers and 3rd party applications are setting the ANSI_PADDING setting.
The key point I was trying to make in the article is that I had always understood varchar/nvarchar columns automatically trimmed trailing spaces and that is not true so you need to deal with that situation. The best thing to do is to RTRIM varchar/nvarchar columns when inserting or updating so that you get consistent behavior. If you have ever used an SSIS lookup this is important as in SSIS the spaced count.
Thanks to all for the comments thus far.