ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

  • Bill,

    I'm not sure what you are exactly asking. Are you mentioning that if you just run the Selects you have listed in SSMS you get the same results?

    When I run:

    Select 'A' + 'A', 'A ' + 'A'

    I get:

    no_space one_space

    -------- ---------

    AA A A

    I would guess that is because the QP is treating the literals as CHAR not VARCHAR.

    Also SSMS connects to the server with ANSI_PADDING ON in the settings by default, you have to explicitly set it to off.

  • Nice article, Jack... good code example, too!

    On the subject of "Why the hell would they do that?", I've found that Microsoft set's the default for databases to ANSI_PADDING OFF using the SELECT DATABASEPROPERTYEX('dbname','IsAnsiPaddingEnabled') code snippet. It's QA and SMS that have it turned on in the connection properties of each.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thanks, Jeff. I actually put a lot more time into the code than the article.

    Yeah, I know that it is turned off by default at the Database level, which is odd considering the ability to turn it off is goign to be deprecated. Oh well, who said MS had to be consistent?

  • Jack Corbett (9/13/2008)


    Thanks, Jeff. I actually put a lot more time into the code than the article.

    Yeah, I know that it is turned off by default at the Database level, which is odd considering the ability to turn it off is goign to be deprecated. Oh well, who said MS had to be consistent?

    Heh... I wish MS would stop deprecating useful things.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Excellent Article...

  • Thanks.

  • This has always been one of my biggest frustrations in SQL Server. I don't like having to trim my fields when I'm concatenating other fields, but I accept it. I just wish SQL Server had a Trim() function like Access that is essentially Ltrim(Rtrim([x])). I've considered writing a UDF for it and throwing it in Master & Model, just never bothered.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Yes a true TRIM function would be nice. I actually had an issue recently when querying FoxPro data in SSIS around trimming. FoxPro has a 4 functions for trimming: LTRIM, RTRIM, TRIM, ALLTRIM. I assumed the TRIM function did RTRIM(LTRIM()), but it doesn't it only does RTRIM. So I was not matching on some data in a lookup because of LEADING spaces the I thought TRIM had removed!

  • Jack Corbett (9/12/2008)


    Bill,

    I'm not sure what you are exactly asking. Are you mentioning that if you just run the Selects you have listed in SSMS you get the same results?

    When I run:

    Select 'A' + 'A', 'A ' + 'A'

    I get:

    no_space one_space

    -------- ---------

    AA A A

    I would guess that is because the QP is treating the literals as CHAR not VARCHAR.

    Also SSMS connects to the server with ANSI_PADDING ON in the settings by default, you have to explicitly set it to off.

    I was just trying to recreate your results but no matter what I do in SQL80 QA when run against a SQL90 DB instance I alway get the same padded result. Manipulating ANSI_PADDING at the session level appears to be ignored.

    Select 'A' + 'A', 'A ' + 'A'

    no_space one_space IS ALWAYSTHE RESULT

    -------- ---------

    AA A A

  • Useful information; thank-you.

    What I am trying to determine, after running into various ANSI_PADDING errors in replication, is which tables currently have the ANSI_PADDING set on. I'd like to be able to predict how each table will behave and take proactive steps to insure that they will.

    ============================================================
    I believe I found the missing link between animal and civilized man. It is us. -Konrad Lorenz, Nobel laureate (1903-1989)

  • At least in SQL Server 2008 (I assume 2005 as well) you can query sys.tables and check the uses_ansi_nulls column 1 is ON, 0 is Off. I don't have any 2000 servers available to me at this time so I don't know how you'd find it there, but I assume it is possible.

  • Thanks for the reply.

    I assumed that the sys.tables.uses_ansi_nulls represented the SET ANSI_NULLS setting. BOL seems to indicate that it is. I just can't find any place where the ANSI_PADDING information is presented to the user.

    The odd thing is that replication is complaining about this setting not being correct on my table but the table does not contain ANY character data.; only INT, SMALLINT, BIT, MONEY and SMALLDATETIME.

    ============================================================
    I believe I found the missing link between animal and civilized man. It is us. -Konrad Lorenz, Nobel laureate (1903-1989)

  • What do you mean by user? The ANSI_PADDING setting is one of the connection string settings and with SSMS and ADO.NET it defaults to ON. In SSMS you can make a global change through Tools->Options->Query Execution->SQL Server->ANSI or by individual query session under Query->Query Options->Execution-ANSI. OR you can just do SET ANSI_PADDING ON/OFF statement.

    Using ADO.NET you need to execute a SET ANSI_PADDING OFF as part of the application.

  • Allow me to clarify what I am looking for.

    Setting or resetting the ANSI_PADDING value of the connection is simple enough, as you have pointed out.

    For replication, it is necessary for the tables being replicated to conform to the same collection of SET options as for Indexed Views. As I'm sure you already know, these are:

    ANSI_NULLS ON

    ANSI_PADDING ON

    ANSI_WARNINGS ON

    ARITHABORT ON

    CONCAT_NULL_YIELDS_NULL ON

    QUOTED_IDENTIFIER ON

    NUMERIC_ROUNDABORT OFF

    These settings are associated with the table based on their values at the time the table was created.

    Since our existing tables don't always conform to these settings, we have to take the steps, as part of the Snapshot, to manually modify the .sch files to correct and variances before applying the Snapshot to the subscriber.

    Conceptually, what I'd like to know is, "Which existing tables don't have these settings correctly configured?"

    sys.tables.uses_ansi_nulls does show me the settings for ANSI_NULLS. (So far, so good)

    ObjectProperty() does show me the settings for ANSI_NULLS and QUOTED IDENTIFIER. (Hey, even better! But not all the way there.)

    What I want is visibility into the other five settings (ANSI PADDING being my immediate concern) so that I, the user, can determine if I'm going to run into trouble when I replicate the table. SQL Server clearly has this information somewhere since it complains if the setting is wrong. Getting to this information is my quest.

    The long term goal is to determine if it is worth while to re-configure any particular table to have the proper settings. Knowing the current configuration would be the first step in this process.

    ============================================================
    I believe I found the missing link between animal and civilized man. It is us. -Konrad Lorenz, Nobel laureate (1903-1989)

  • Hey, I ran a Profile trace and then scripted out a table definition as that gets you the settings. The setting for ANSI_PADDING is in sys.all_columns is_ansi_padded.

Viewing 15 posts - 16 through 30 (of 50 total)

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