ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jack Corbett » ANSI PADDING, Trailing Whitespace, and...

50 posts, Page 1 of 5

1 2 3 4 5 »»»

ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Rate Topic

Display Mode

Topic Options

Author

Message

Jack Corbett

Posted Thursday, September 11, 2008 10:22 PM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

Comments posted to this topic are about the item ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

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 #568248

steitelbaum

Posted Friday, September 12, 2008 7:02 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2008 1:58 PM
Points: 16, Visits: 81

jack, the table of behaviors is cutting off (part of 3rd column) in my browser.

Post #568451

Jack Corbett

Posted Friday, September 12, 2008 7:19 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

steitelbaum (9/12/2008)

jack, the table of behaviors is cutting off (part of 3rd column) in my browser.

What browser are you using? I have viewed the article in IE 7, Firefox 3, and Chrome without any problems. How about screen resolution?

Post #568466

steitelbaum

Posted Friday, September 12, 2008 7:39 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2008 1:58 PM
Points: 16, Visits: 81

IE 7.0.5730.11
resolution 1024 x 768

Post #568493

Tobar

Posted Friday, September 12, 2008 7:43 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:04 AM
Points: 167, Visits: 547

Jack,

Great article! I did read your sources and you summarized them well.

I have found this behavior equally as frustrating as you. I never realized the Trim twins did not handle what you call "special characters", "C" and Oracle, my background, has always treated them as white space. One more anxiety pill when dealling with SQL Server.

I always use varchar to minimize trailing snafus. Any time you import data just be super vigilant about getting rid of trailings. Once it is in the DB clean, and you are using varchar, you are home free.

<><
Livin' down on the cube farm. Left, left, then a right.

Post #568501

cy-dba

Posted Friday, September 12, 2008 7:57 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 17, 2011 7:09 AM
Points: 869, Visits: 963

Good article! I recently ran into issues regarding this and your post summarizes the behavior nicely. I'll use it for quick, future reference.

Post #568517

Jack Corbett

Posted Friday, September 12, 2008 7:58 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

steitelbaum (9/12/2008)

IE 7.0.5730.11
resolution 1024 x 768

It is related to the resolution. Apparently the table is not resizing based on resolution. Not being an HTML guy I'll have to fumble around a little.

Maybe someone here can tell me how to fix it.

Post #568518

Jack Corbett

Posted Friday, September 12, 2008 8:07 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

Jack Corbett (9/12/2008)

steitelbaum (9/12/2008)

IE 7.0.5730.11
resolution 1024 x 768

It is related to the resolution. Apparently the table is not resizing based on resolution. Not being an HTML guy I'll have to fumble around a little.

Maybe someone here can tell me how to fix it.

This is also only an issue in IE. Firefox and Chrome both display the entire table.

Post #568526

Reginald J Ray Jr

Posted Friday, September 12, 2008 8:09 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:12 AM
Points: 17, Visits: 55

I'm confused. I get the same results from 'select * from duh' for both settings of ANSI_PADDING.

set ANSI_PADDING off
go

create table duh (
col1 varchar(50)
)

insert into duh select 'a ' + 'a'

select * from duh

drop table duh

jack ray
jray@validata.org

Post #568529

Jack Corbett

Posted Friday, September 12, 2008 8:26 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

Reginald J Ray Jr (9/12/2008)

Okay, maybe I was not clear. The ANSI_PADDING setting is based on the connection setting. So if a table is created with ANSI_PADDING OFF then inserts and updates to varchar columns in that table will not have include the trailing spaces while nvarchar columns will take on the characteristics of the ANSI_PADDING setting at insert.

If you download and run the script I provided with the article you can see the inconsistent behavior which, IMHO, is a big part of the issue. I don't want to have to run profiler to see how all my developers and 3rd party applications are setting the ANSI_PADDING setting.

The key point I was trying to make in the article is that I had always understood varchar/nvarchar columns automatically trimmed trailing spaces and that is not true so you need to deal with that situation. The best thing to do is to RTRIM varchar/nvarchar columns when inserting or updating so that you get consistent behavior. If you have ever used an SSIS lookup this is important as in SSIS the spaced count.

Thanks to all for the comments thus far.

Post #568566

« Prev Topic | Next Topic »

50 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions