"Scott Hess" <[EMAIL PROTECTED]> wrote: > In some database systems, it can be beneficial to use "NOT NULL" as > much as possible when defining tables. It usually allows for a > slightly tighter storage encoding, and also allows some optimizations > to occur. > > AFAICT, in sqlite it only seems important for constraining the data > appropriately. For a column which does not contain null data, it > looks like the storage doesn't change between when it's defined with > NOT NULL or without (I'm guessing this is basically because of > manifest typing). Also, the EXPLAIN output doesn't change at all when > I add or remove the NOT NULL. So it would seem that the actual > performance would never change, nor would the storage footprint, so > long as you don't ever attempt to insert null data (in which case the > NOT NULL version would convert it to an appropriate default value, > which might cause changes to storage or performance). > > Anyone got holes to shoot in my reasoning? >
The size of the database file is unchanged with NOT NULL. Adding NOT NULL might in theory make INSERT and UPDATE go a little slower, since now those statements must check the value of values before inserting them to make sure they are NOT NULL, but the performance difference is likely to be so slight as to be unmeasurable. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
