Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Ansi Options Part 1 - ANSI_PADDING Expand / Collapse
Author
Message
Posted Thursday, July 4, 2002 12:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:53 PM
Points: 31,177, Visits: 15,623
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
Post #5251
Posted Monday, July 8, 2002 9:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 19, 2009 8:45 AM
Points: 29, 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
Post #36874
Posted Monday, July 8, 2002 9:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:53 PM
Points: 31,177, Visits: 15,623
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
Post #36875
Posted Thursday, July 14, 2011 12:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 5:19 PM
Points: 20, Visits: 161
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('');

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.
Post #1141490
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse