SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum


ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Author
Message
tony.sawyer
tony.sawyer
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 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
Matt Whitfield
Matt Whitfield
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4935 Visits: 719
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
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103813 Visits: 15047
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
Consultant Straight Path Solutions
Dont let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work
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 QuestionHow 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
tony.sawyer
tony.sawyer
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144683 Visits: 18651
I'm glad this was republished. Thanks Jack - good explanation.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

Paul White
Paul White
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79890 Visits: 11400
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Daniel Bowlin
Daniel Bowlin
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17460 Visits: 2629
Nice article. I too had some of the same misconceptions about padding as you.
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103813 Visits: 15047
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
Consultant Straight Path Solutions
Dont let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work
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 QuestionHow 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
Dan W-960228
Dan W-960228
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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).
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103813 Visits: 15047
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
Consultant Straight Path Solutions
Dont let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work
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 QuestionHow 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
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