Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jack Corbett » ANSI PADDING, Trailing Whitespace, and...
Add to briefcase
50 posts, Page 5 of 5
«««12345

ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
tony.sawyer
Posted Friday, July 16, 2010 4:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 23, 2010 8:30 AM
Points: 229, Visits: 146
Hi All,

The ANSI PADDING also has effects on Entity Framework 4!

I had written an Entity Diagram linking two tables together through a CHAR(10) field that wasn't fully populated. For some reason the link never worked and it took me a long time to get to the issue and notice that even though both fields were the same type and fixed length one of the tables was set to ANSI_PADDING ON and the other wasn't (yeah - great design there originally!)

When EF4 got the data from both tables, even though the fields were fixed length in the database and there were no problems accessing the data within SQL Server, as soon as it was returned outside of the database the table with ANSI_PADDING set to OFF acted more like a VARCHAR(10) field and only returned a 7 character field with no trailing spaces. The other table returned a 10 character field with 3 trailing spaces. EF4 was unable to link those two records together as it believed them to be different.

To solve the issue, we created a view of the table and cast the field to a CHAR(10) and linked through the new field and the padded field which fixed the issue (yes a bodge rather than fixing the data in the table). At the time we weren't sure whether we would use EF4 so we weren't prepared to do major undertakings on the database if we weren't getting a real benefit to our production system.

Tony
Post #953725
Matt Whitfield
Posted Friday, July 16, 2010 8:03 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:29 AM
Points: 509, Visits: 718
Nice article Jack - I think stuff like this that underlines how complex the simple can be is a real benefit to the community...

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #953890
Jack Corbett
Posted Friday, July 16, 2010 10:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 10,613, Visits: 11,959
Tony,

Are you sure it isn't just .NET issue? The ANSI PADDING setting should only affect variable length column (VARCHAR/NVARCHAR) not CHAR.


Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #953992
tony.sawyer
Posted Friday, July 16, 2010 10:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 23, 2010 8:30 AM
Points: 229, Visits: 146
The ANSI_PADDING affects the CHAR types too - surprised the hell out of me when I finally discovered why my tables weren't linking!

doing a simple 
select '<' + prod_code + '>' from ...

showed it to only contain 7 characters with no trailing white space. 

select '<' + cast(prod_code as char(10)) + '>' from ...

showed up the trailing 3 spaces (and then allowed EF4 to link the data to another table that had ANSI_PADDING set to ON)

We're using SQL Server 2005 here - give it a shot and see if you get the same results
Post #953999
SQLRNNR
Posted Friday, July 16, 2010 12:45 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 18,858, Visits: 12,443
I'm glad this was republished. Thanks Jack - good explanation.


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #954105
Paul White
Posted Saturday, July 17, 2010 11:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 10,990, Visits: 10,578
Well that's just weird. I just posted on one of the 2-year-old threads referenced in this article earlier this morning, before I saw that this article had been republished. Perhaps now would be a good time to buy a Lotto ticket?


Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #954406
Daniel Bowlin
Posted Tuesday, July 20, 2010 6:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 2,681, Visits: 2,423
Nice article. I too had some of the same misconceptions about padding as you.
Post #955487
Jack Corbett
Posted Tuesday, July 20, 2010 7:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 10,613, Visits: 11,959
tony.sawyer (7/16/2010)
The ANSI_PADDING affects the CHAR types too - surprised the hell out of me when I finally discovered why my tables weren't linking!

doing a simple 
select '<' + prod_code + '>' from ...

showed it to only contain 7 characters with no trailing white space. 

select '<' + cast(prod_code as char(10)) + '>' from ...

showed up the trailing 3 spaces (and then allowed EF4 to link the data to another table that had ANSI_PADDING set to ON)

We're using SQL Server 2005 here - give it a shot and see if you get the same results



Tony,

You are correct. The difference is that unicode (nchar/nvarchar) always behave with ANSI_PADDING ON settings.


Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #955529
Dan W-960228
Posted Friday, October 28, 2011 3:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 08, 2012 9:12 AM
Points: 19, Visits: 46
To brighten the path for others following this later (like me), I wanted to spell out more about what this means for a Lookup in SSIS (according to the package I just got working). SSIS includes any trailing spaces in a lookup, and when matching to the input source, 'a' != 'a ' (space).

My source was Excel, and I didn't find any data conversion that would add the trailing spaces so that 'a' coming in would be 'a ', so I changed my lookup from a table/view selection to a SQL statement that trimmed my db-side fields to that 'a ' was changed to 'a', thus matching the 'a' coming from Excel.

Adding the trim is easy enough, but I'm puzzled why SSIS won't allow a data conversion to a type that will behave like char(10) and automatically add trailing spaces (if I just missed it, please explain how to do that in SSIS).
Post #1197681
Jack Corbett
Posted Sunday, October 30, 2011 7:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 10,613, Visits: 11,959
I believe, and I'm not an SSIS expert, that SSIS is doing the compare within SSIS, meaning that .NET comparison rules apply. You need to match case AND trim any trailing spaces. I also believe that an Excel source uses Unicode data types for any strings, so SQL Server would pad with trailing spaces.


Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1197866
« Prev Topic | Next Topic »

Add to briefcase
50 posts, Page 5 of 5
«««12345

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.