Post reply

ANSI_PADDING

  • December 22, 2008 at 8:18 am

    #72270

    When importing a flat file into a table I first import to a staging table, where all fields are defined as char, and then insert into a final table where the final data type (datetime, float, int, varchar, etc.) has been defined.

    I was distressed to find that my varchar fields were all imported with trailing blanks. I think that setting ANSI_PADDING OFF would have eliminated that problem, but BOL suggests that the ANSI_PADDING option is going to be eliminated, and that "ON" should always be used.

    Not a big deal, as I was able to apply RTRIM to the fields going from CHAR to VARCHAR, but I'm confused as to why the default behavior is to not trim trailing blanks, and why the recommendation is to not use ANSI_PADDING OFF?

  • December 22, 2008 at 8:58 am

    #916206

    I think it makes the CHAR definition make more sense, rather than making a fundamental change to VARCHAR. With ANSI_PADDING off, sometimes CHAR column act more like VARCHAR columns (when they are set to nullable); if it's on, a fixed width column really does have a fixed width.

    Besides - it's the option that leaves you with the most flexibility in VARCHAR. Meaning - you can programmatically control whether the trim should happen or not, while not having to play games to make sure that any trailiing spaces you might actually want don't get "whacked" by the VARCHAR definition.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • December 22, 2008 at 9:01 am

    #916207

    Thanks Matt, I guess that does make sense.

  • December 22, 2008 at 10:08 am

    #916242

    You might want to read "Guru's Guide to Transact SQL by Ken Henderson ... page 36 Google it and Google books will allow you to search for ANSI PADDING ... Keeping it on also is more compliment with ANSI SQL-92 standard. Ken's example of using LIKE and = when comparing values in a select statement's where clause is particularly interesting and illustrates the problem when it is ANSI PADDING is set to off.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

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