• Jack Corbett (5/13/2008)


    I just checked all my servers (2005 and 2000) and in the model database ANSI PADDINGS is false meaning OFF and I have never messed with the default on a SQL Server installation, but in SSMS in the Query options ANSI PADDINGS is set to ON. I have tried setting it to off and the behavior remains the same. When you insert 'a ' into a variable length column it is inserting the trailing space. The DataLength function will tell you is spaces are stored as it gives the actual storage used. So inserting 'a' and 'a ' would return 1, 2 in varchar and 2, 4 in nvarchar.

    This is something I'd like to know more about as it goes against everything I thought I knew about how SQL Server handled trailing spaces, especially since it seems that the ANSI_PADDING option is not being reported correctly as it is OFF in all my db's and the tests I have done today don't jive with that setting.

    I am not sure how changing the setting AFTER something has been inserted affects things that were inserted prior to the setting (although from the way it's described - I don't think it affect those values)

    Hmm. Just curious - what do you get when you run this?

    create table #T(k varchar(10) primary key clustered)

    insert #T

    select 'a' union all

    select 'a ' union all

    select 'a ' union all

    select 'a ' union all

    select 'a '

    This tells me that ANSI_PADDING really is off in my case (it errors out).

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