To my dismay, our company is also having problems dealing with this setting. Our company has been using ANSI Padding OFF as our standard (I'm not sure why, but it's a legacy system).
We are transferring much of the companies business logic into a new framework which will use LinQ. Therefore is using C# to compare data values; meaning case sensitivity as well as blank space sensitivity....
As I'm finding out as this project is moving along at quite a considerable pace, and as you have mentioned in your article Steve, that even though Padding is set to OFF, if the column is NOT NULL padding is enforced anyway for CHAR columns. txtPost_CommentEmoticon(':w00t:');
My job is to rectify this situation by ensuring values that are deemed the same in SQL Server, will also be deemed the same using LinQ. It's a tough task as I'll need to recreate the CHAR columns with ANSI Padding set to ON throughout the entire db.
I've added a script to SQL Server used to highlight the FK relationships (assuming these will be the columns that are likely to be used in table joins) values that will no longer be deemed as equal using LinQ. http://www.sqlservercentral.com/scripts/Referential+Integrity/74779/
A Future version of SQL Server will be forcing this ANSI Padding setting to ON as mentioned here:
So the work to change the column's padding properties to ON will need to be done eventually anyway.