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

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.