ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

  • Jack Corbett

    SSC Guru

    Points: 184296

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


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • steitelbaum

    SSC Eights!

    Points: 864

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

  • Jack Corbett

    SSC Guru

    Points: 184296

    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?


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • steitelbaum

    SSC Eights!

    Points: 864

    IE 7.0.5730.11

    resolution 1024 x 768

  • Tobar

    SSCarpal Tunnel

    Points: 4767

    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.

  • cy-dba

    SSCarpal Tunnel

    Points: 4149

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

  • Jack Corbett

    SSC Guru

    Points: 184296

    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.


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Jack Corbett

    SSC Guru

    Points: 184296

    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.


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Reginald J Ray Jr

    Old Hand

    Points: 383

    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

  • Jack Corbett

    SSC Guru

    Points: 184296

    Reginald J Ray Jr (9/12/2008)


    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

    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.


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • SqlOnMyMind

    SSCarpal Tunnel

    Points: 4913

    I just ran into this problem in SSIS this week! In my Data Flow, I had to convert varchar columns to be used in nvarchar columns in the final destination table, and apparently this introduced blanks. I solved it by add RTrim() to the conversion, but now I wonder if it would be better to use "Set ANSI_PADDING OFF" somewhere in the Control Flow?

    p.s. - Jack, thanks for the summary and the excellent script to demonstrate the variations!

  • Tobar

    SSCarpal Tunnel

    Points: 4767

    From what I have seen ""Set ANSI_PADDING OFF" somewhere in the Control Flow" will have no effect. The setting is a "table creation" parameter, if you will, in that it depends what is in effect when the table is created, not when the table is populated.

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

  • Jack Corbett

    SSC Guru

    Points: 184296

    Thanks for the positive comments Carla, glad it was helpful.

    If you really want to turn it off you could actually make the change in the advanced properties of the connection. The biggest issue is that MS is going to deprecate the SET ANSI_PADDING statement so using that will eventually break your code.


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • SqlOnMyMind

    SSCarpal Tunnel

    Points: 4913

    Tobar and Jack,

    Thanks for your comments. I just wanted to make sure that there wasn't something I should have been doing instead of using the RTrim function. I am always looking to improve my code and my knowledge of SQL. (These articles and discussions are great!)

  • TechnoPeasant

    SSC-Addicted

    Points: 417

    Then why is it that regardless of session or db settings SQL90 returns "A A" for

    select 'A' + 'A'

    and

    select 'A ' + 'A'

    Is that because theres still a "table" involved albeit a temporary one and the "settings at creation" rule is being applied?

Viewing 15 posts - 1 through 15 (of 50 total)

You must be logged in to reply to this topic. Login to reply