none
ANSI_NULL vs ANSI_NULLS database setting RRS feed

All replies

  • Take a look at this for the first option (link at the bottom explaining the second)

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-null-dflt-on-transact-sql?view=sql-server-ver15


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, July 13, 2020 4:44 PM
    Moderator
  • The similarity is that both should be ignored. They only apply if you connect with really old client APIs. With newer APIs, you get the default from the API.

    If you really want to know, read the topic for ALTER DATABASE SET options in Books Online.


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

    Monday, July 13, 2020 8:35 PM
  • ok . so 

    ANSI_NULL  should be controlling whether the column is allow null or not during table creation or alter.?

    Tuesday, July 14, 2020 2:51 AM
  • This is what the link says:

    This setting only affects the nullability of new columns when the nullability of the column is not specified in the CREATE TABLE and ALTER TABLE statements. When SET ANSI_NULL_DFLT_ON is ON, new columns created by using the ALTER TABLE and CREATE TABLE statements allow null values if the nullability status of the column is not explicitly specified. SET ANSI_NULL_DFLT_ON does not affect columns created with an explicit NULL or NOT NULL.

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

    So, the answer is - both commands if they add a new column and you don't explicitly specify NULL status for that column.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 14, 2020 2:55 AM
    Moderator
  • Hi sakurai_db,

    Thank you for your post. Have you solved this problem ? 

    In order to close this thread, please kindly mark useful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 24, 2020 1:14 AM
  • ALTER DATABASE [shrinkfiletest] SET ANSI_NULL_DEFAULT ON WITH NO_WAIT
    GO
    ALTER DATABASE [shrinkfiletest] SET ANSI_NULLS ON WITH NO_WAIT
    GO

    and also in session 

    SET ANSI_NULL_DFLT_ON ON;  

    SET ANSI_NULLS on

    what are there different ...??

    why ANSI_NULLS  is off by default in database level, while default on  in the session level ?

    Friday, July 24, 2020 3:08 AM
  • Lots of options are incorrect on the database level (not sure why, most likely because they don't matter much and left incorrect for historical reasons). I had an article for the somewhat similar problem a while back https://social.technet.microsoft.com/wiki/contents/articles/17640.sql-server-set-ansi-padding-setting-and-its-importance.aspx

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, July 24, 2020 4:23 AM
    Moderator
  • ALTER DATABASE [shrinkfiletest] SET ANSI_NULL_DEFAULT ON WITH NO_WAIT
    GO
    ALTER DATABASE [shrinkfiletest] SET ANSI_NULLS ON WITH NO_WAIT
    GO

    These settings only affect very old client APIs. Say that you woudl connect with an application that uses DB-Library. DB-Library will connect with ANSI_NULLS OFF, but you can change this with the ALTER DATABASE option.

    For a modern client written in .NET, Java etc, these settings have no effect at all. These APIs will connect with ANSI_NULLS ON always.

    and also in session 

    SET ANSI_NULL_DFLT_ON ON;  

    SET ANSI_NULLS on

    These are session-level session which permits a session to alter the setting what they prefer (but which they should not do, given that these are legacy settings).

    why ANSI_NULLS  is off by default in database level, while default on  in the session level ?

    I've told you before: ignore these settings. They are only confusing, and they serve no practical purpose for the majority of SQL users.


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

    Friday, July 24, 2020 8:45 AM