Post reply

legacy database, ALTER TABLE, and ANSI_PADDING warnings

  • May 3, 2004 at 6:03 pm

    #66369

    ALTER TABLE tablename ADD fieldname VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    GO

    should I worry about this warning below on an ALTER TABLE command ?

    I just need to add a column.

    I copied the table to my test SQL server and did not get this msg with aLTER TABLE above.

    both have same colation settings.

    'tablename' table

    - Warning: One or more existing columns have ANSI_PADDING 'off' and will be re-created with ANSI_PADDING 'on'.

    - Warning: The table was created with ANSI_NULLS 'off' and will be re-created with ANSI_NULLS 'on'.

     

    I tried various ANSI PADDING OFF / ON commands.

    nothing special about this table. no foreign keys, single index , (fill factor 0)

    its a goldmine database... cludgy thing, converted from DBASE IV.

  • May 6, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • May 6, 2004 at 11:45 pm

    #505593

    From BOL:

    ANSI_PADDING

    When set to ON, trailing blanks in character values inserted into varchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column. When set to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are trimmed. This setting affects only the definition of new columns.

    Char(n) and binary(n) columns that allow nulls are padded to the length of the column when SET ANSI_PADDING is set to ON, but trailing blanks and zeros are trimmed when SET ANSI_PADDING is OFF. Char(n) and binary(n) columns that do not allow nulls are always padded to the length of the column.

    Important  It is recommended that ANSI_PADDING always be set to ON. SET ANSI_PADDING must be ON when creating or manipulating indexes on computed columns or indexed views.

    The status of this option can be determined by examining the IsAnsiPaddingEnabled property of the DATABASEPROPERTYEX function.

    I would hazard a guess that when the table was originally created your database had ANSI_PADDING turned off.  Somehow that setting has been changed back to on.  When you add your new column, it will drop and recreate the table with ANSI_PADDING ON.

    Hope this helps,

    Angela

  • July 29, 2008 at 8:01 am

    #849804

    It appears that by definition Alter Table by default sets ansi_padding to on. Take a look:

    http://msdn.microsoft.com/en-us/library/ms190273.aspx

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

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