locked
Advantage and Disadvantage of NOT NULL constraints RRS feed

  • Question

  • User-331457339 posted

    Hi,

    I have a table with around 1900 Trillions records. if i add a "NOT NULL" constraint to one of the varchar columns.

    Is there any performance impact when we update that column? 

    Wednesday, July 4, 2018 10:59 AM

All replies

  • User1520731567 posted

    Hi mspblr2000,

    On one hand,

    The constraint checks are very small and don't impact performance in a significant way.

    Adding them in there is a way of ensuring that your data is correct and accurate, which ultimately results in better performance.

    Constraints do not affect disk space.

    (In SQLite, NULL is just another value type.) A NOT NULL constraint can speed up some queries;

    For example, UNIQUE and NOT NULL constraints can optimize away a DISTINCT.

    However, the primary purpose of constraints is to prevent programming errors so that you can trust the database to contain correct data.

    On the other hand,

    One interesting aspect is when there is a need to add a new not null column to a huge table.

    Not null constraint in this case requires default value so the table is locked for a while during the operation.

    There are several ways to solve this, one is to allow null, fill the default values in batches and then set the not null constraint.

    More details, you could refer to :

    https://www.sqlservercentral.com/Forums/Topic1028702-373-1.aspx

    https://stackoverflow.com/questions/1222314/does-setting-not-null-on-a-column-in-postgresql-increase-performance

    http://www.hellodba.com/reader.php?ID=181&lang=EN

    Best Regards.

    Yuki Tao

    Thursday, July 5, 2018 9:37 AM
  • User77042963 posted

    Are you sure you have these many rows in your database? 1900 Trillions records.

    How big is your database?

    Thursday, July 5, 2018 1:56 PM
  • User364663285 posted

    Hi,

    I have a table with around 1900 Trillions records. if i add a "NOT NULL" constraint to one of the varchar columns.

    Is there any performance impact when we update that column? 

    When applying such new NOT NULL constraint, you would get problem when any rows is having NULL values inside, to the column.

    Wednesday, July 11, 2018 9:00 AM