July 4, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/ansioptionspart1ansi_padding.asp
July 8, 2002 at 9:40 am
Interesting article, I recently had a "show-stopping" application bug that traced back to this setting (in combination with other factors). In my research in BOL, I don't think I found anything as succinct as your summation - thanks.
I have for certain tables turned this option OFF. It was the simplest solution to fix the above bug in an application that depended on tables that were fed monthly through a convoluted ETL process (I inherited, not wrote). The author of the ETL process was apparently depending on this setting being OFF. When I refactored and rebuilt a table with the defaults (PADDING ON) I introduced the bug. So I rebuilt the table with PADDING OFF, until I can do it "right" by fixing the ETL process to explicitly perform the needed trims.
JasonL
JasonL
July 8, 2002 at 9:42 am
Interesting and thanks. Glad I could summarize it.
I didn't have any bugs, but the warning from Erwin when I changed tables bothered me, so I decided to find out exactly what this was.
Hopefully the rest of the series will be helpful as well.
Steve Jones
July 14, 2011 at 12:30 am
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:
http://msdn.microsoft.com/en-us/library/ms143729.aspx
So the work to change the column's padding properties to ON will need to be done eventually anyway.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply