locked
Create table with ANSI_NULLS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL set off RRS feed

  • Question

  • What's the actual impact create table with deprecated set option ANSI_NULLS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL set off?

    How to fix this ? Need to recreate the whole table ?

    If the features is really discountiued , what's the affect on the current table with these option off ?

    Thursday, August 6, 2020 3:11 AM

All replies

  • What's the actual impact create table with deprecated set option ANSI_NULLS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL set off?

    I think ANSI_NULLS affects check constraints, but is not entirely clear to me. It could also affect computed columns if they include CASE expressions.

    ANSI_PADDING relates to char and varchar columns. With ANSI_PADDING ON, 'abc   ' will be stored as such. With the setting OFF, the blanks will be stripped. I think it also relates to (var)binary columns. However, n(var)char are unaffected by this setting, trailing spaces are always retained.

    I don't think the setting of CONCAT_NULL_YIELDS_NULL when the table is created matters; it is only the run-time setting. But, hey, why don't you run a test?

    How to fix this ? Need to recreate the whole table ?

    For the ANSI_NULLS setting there is no supported way to flip the switch.

    For ANSI_PADDNING, you can to ALTER TABLE ALTER COLUMN. This setting is not saved per table. It is saved per column.

    If the features is really discountiued , what's the affect on the current table with these option off ?

    I guess Microsoft will have to figure something out. Well, for ANSI_PADDING it's simple as it only have effect on INSERT, but ANSI_NULLS is worse. I guess they will have to block the upgrade.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 6, 2020 7:40 AM
  • I don't think the setting of CONCAT_NULL_YIELDS_NULL when the table is created matters; it is only the run-time setting. But, hey, why don't you run a test?  << I am not sure how to test 

    ANSI_NULLS << basically need to create a new table and reload ? And wonder why if the table has ANSI_NULLS off, creating column index persist will have error? what's the reason behind it ? 

    ANSI_PADDING_OFF columns <<< how to solve ? create a new column ? or can alter the current column ?

    Thursday, August 6, 2020 12:02 PM
  • ANSI_NULLS << basically need to create a new table and reload ? And wonder why if the table has ANSI_NULLS off, creating column index persist will have error? what's the reason behind it ? 

    What do you mean with "creating column index persist will have error"?

    ANSI_PADDING_OFF columns <<< how to solve ? create a new column ? or can alter the current column ?

    If memory serves, ALTER TABLE ALTER COLUMN helps. I could run a test - or you could run a rest yourself! Inspect the setting in sys.columns. (Where for some reason the column is_ansiå_padding is 0 for columns it does not apply to.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 6, 2020 9:15 PM