ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

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

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

  • 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?

  • IE 7.0.5730.11

    resolution 1024 x 768

  • 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.

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

  • 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 (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.

  • 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

  • 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.

  • 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!

  • 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.

  • 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.

  • 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!)

  • 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 49 total)

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