Database table and NULLs

null

I know that there have been many opinions/sides concerning NULL values in a database.
I have not understood though what is the best practice for this.

I.e. if I have a relational table with an optional attribute i.e. it can take NULL value -so we can end up with a table with many NULLs on that column- is it best to make the attribute a new relational table?

What is the best approach on this?

Best Answer

From a purely relational point of view (prior to sixth normal form), I don't see any need to move a set of columns out into a separate table, just because they are frequently null.

As a trivial example, consider a customer account table with an end date as one of the columns - until the customer closes their account, the end date will be NULL. You are therefore likely to have a large number of NULL values in the end date column, yet it would be a bad choice to move this out into a separate table.

However, as an implementation issue, there may be good reasons to split out certain fields into separate tables. For example, consider a customer address table; certain addresses may require complicated delivery instructions, yet the vast majority are not likely to require them. In such circumstances, it would make sense to have a separate table for address delivery instructions, even if it has the same key (customer address ID) as the main customer address table.

(As a side note, some experts on relational theory - such as Chris Date and Fabian Pascal - are opposed to allowing NULLs within relational databases at all.)

Related Solutions

Sql-server – Update “NULL” string to Actual NULL value

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ' + QUOTENAME(name) + ' = CASE
  WHEN ' + QUOTENAME(name) + ' = ''NULL'' THEN NULL ELSE '
  + QUOTENAME(name) + ' END,'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.YourTableName')
AND system_type_id IN (35,99,167,175,231,239);

SELECT @sql = N'UPDATE dbo.YourTableName SET ' + LEFT(@sql, LEN(@sql)-1) + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

SQL Server – Including Nulls During Comparisons

To start off, NULL does not mean "no value" it means "Unknown value" in SQL Server. There is a session setting called ANSI_NULLS that could make your queries behave as you would like them to, however, it's deprecated and will be forced to ON (which you don't seem to like) in a future version: http://msdn.microsoft.com/en-us/library/ms188048.aspx

I get what you're trying to do, and to make a counter point I would ask if you've seen any queries generated by reporting services or something like Cognos? If so, you'll see exactly what you're describing you don't want to do. Why? Well because with a schema that allows nulls, that's the way to do it. I'm not saying it's a super awesome and great idea but it works all of the time.

What your designer could do is check to see if that column could even be null and if so the appropriate logic could step it and create the correct query. You could also have options such as "This column may be null, do you want those values?". I don't know the end-game per-se and writing your own dynamic querying tool is quite the deat when the logical consistencies are all factored in (such as this).

I would continue to do explicit null checking on columns that could possibly be null, sure it doesn't look the best but it works all of the time.

ANSI_NULL set option will work for now but NOT a good idea especially if you don't control the environment, plus it will be forced set ON later and cause errors where you'll need to re-write your application logic anyway. This is the way SQL Server works with NULLs.

Best Answer

Related Solutions

Sql-server – Update “NULL” string to Actual NULL value

SQL Server – Including Nulls During Comparisons

Related Question