Post reply

ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

  • September 11, 2008 at 10:22 pm

    #192743

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

    Jack Corbett
    Consultant - Straight Path Solutions
    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

  • September 12, 2008 at 7:02 am

    #870916

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

  • September 12, 2008 at 7:19 am

    #870931

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

  • September 12, 2008 at 7:39 am

    #870955

    IE 7.0.5730.11

    resolution 1024 x 768

  • September 12, 2008 at 7:43 am

    #870962

    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.

  • September 12, 2008 at 7:57 am

    #870978

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

  • September 12, 2008 at 7:58 am

    #870979

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

  • September 12, 2008 at 8:07 am

    #870985

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

  • September 12, 2008 at 8:09 am

    #870988

    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

  • September 12, 2008 at 8:26 am

    #871018

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

  • September 12, 2008 at 9:52 am

    #871104

    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!

  • September 12, 2008 at 10:31 am

    #871139

    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.

  • September 12, 2008 at 10:33 am

    #871143

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

  • September 12, 2008 at 11:02 am

    #871167

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

  • September 12, 2008 at 3:37 pm

    #871368

    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