Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Ansi Options Part 1 - ANSI_PADDING


Ansi Options Part 1 - ANSI_PADDING

Author
Message
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40538 Visits: 18846
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/ansioptionspart1ansi_padding.asp

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
jason_langston
jason_langston
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 3
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
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40538 Visits: 18846
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
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
cameron.wells
cameron.wells
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 185
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search