Archives of the TeradataForum

Message Posted: Wed, 31 Oct 2001 @ 16:25:23 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Bharat,

I'm not sure about making ALL non key columns NULL, but most warehouses have 'more than a few' columns which are indeed NULL. In my view this is primarily a decision based on the ldm (i.e. Do we always know a value for this attribute?).

I appreciate most of the comments/thoughts that you make. I would certainly disagree with an end-user who wanted to make a column NULLable when it was known that there would never be never be any nulls in it - unless I suppose they were thinking of fairly concrete future plans. I've always treated making a column NOT NULL as being a form of control over data values (and by implication data quality) that you can pass onto the dbms - take it out of the hands of the application (I'm not saying that all control should be in the dbms, but this is one that I've always been happy to place there).

My usual comment to users when they're querying a column which may contain NULLs is that if they want to know about rows with unknown values then they have to code "is/is not null" as appropriate. This may mean extra coding for them, but it means that they have to think about what they're asking - makes the question more accurate and the answer more relevant, i.e. likely to be more valuable.

As to performance/storage issues:

- any column defined with COMPRESS will automatically compress NULL values as well as the chosen data value. In this case, a NULLable column may be thought of as better than NOT NULL because you can now compress two 'values' instead of only one.

- there used to be issue involving sub-queiries and NOT IN against nullable columns, but I believe those have gone away.

Some initial thoughts for you.
Cheers,

Dave

Ward Analytics Ltd: Information in motion (www.ward-analytics.com)

	<Prev	Next>		<<First	<Prev	Next>	Last>>