Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    30

    Unanswered: CHECK CONSTRAINTS and NOT NULL

    What is the difference between the following two definitions ... How do they differ in performance.

    1) CREATE TABLE TAB1(COL1 INT NOT NULL,COL2 CHAR(8),COL3 CHAR(100))

    2) CREATE TABLE TAB1(COL1 INT,COL2 CHAR(8),COL3 CHAR(100))
    ALTER TABLE TAB1 ADD CONSTRAINT CHECK1 CHECK COL1 IS NOT NULL


    TIA

    Soundh

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Not 100% sure, but I believe that option 2 still retains the extra null byte in the row, and does the normal null indicator processing for it (in the database engine and the application program), and then checks whether the null indicator is set to null. Processing is probably more expensive for option 2, but maybe not measurable.

    In theory, disk storage and memory is more expensive for option 2 because of the null indicator byte. However, having just one column in a table with an extra byte may not decrease the number of rows that fit on a page, so it might not have any practical effect on disk or memory.
    Last edited by Marcus_A; 06-12-04 at 19:53.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •