Wow, the correct answer "Don't allow NULLs when you don't have to because they degrade performance" is somehow the last rated answer. I will upvote it and elaborate. When a RDBMS allows NULLs for a non-sparse column, that column is added to a bitmap that tracks whether the value is NULL for each individual row. So by adding NULL-ability to a column in a table where all columns do not allow NULLs, you are increasing the storage space required to save the table. Further, you are requiring the RDBMS to read and write to the bitmap, degrading performance on all operations.
Further, in a number of instances, allowing NULLs will break 3NF. While I am not a stickler for 3NF like many of my colleagues, consider the following scenario:
In the Person table there is a column, called DateOfDeath, which is nullable. If a person has died, it will be filled in with their DateOfDeath, otherwise it will be left NULL. There is also a non-nullable bit column called IsAlive. This column is set to 1 if the person is alive, and 0 if the person is dead. The vast majority of the stored procedures use the IsAlive column, they care only if a person is alive, not their DateOfDeath.
However, the IsAlive column breaks database normalization, because it is completely derivable from DateOfDeath. But since IsAlive is hard-wired into the majority of SPs, the straightforward solution is to make DateOfDeath non-nullable, and assign a default value to the column in the event the person is still alive. The few SPs that use DateOfDeath can then be rewritten to check the IsAlive column, and only honor the DateOfDeath if the person is not alive. Again, since the majority of SPs care only about IsAlive (a bit) and not DateOfDeath (a date) using this pattern speeds up access considerably.
A useful T-SQL script for finding nullable columns with no NULLs across all schemas is:
select 'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' WHERE ' + QUOTENAME(c.name) + ' IS NULL)
AND (SELECT COUNT(*) FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ') > 1 PRINT ''' + s.name + '.' + t.name + '.' + REPLACE(c.name, '''', '''''') + ''''
from sys.columns c
inner join sys.tables t ON c.object_id = t.object_id
inner join sys.schemas s ON s.schema_id = t.schema_id
where c.is_nullable = 1 AND c.is_computed = 0
order by s.name, t.name, c.name;
If you run this on a copy of your production database you can find the columns developers marked as allowing NULLs that have no NULLs in practice. The vast majority of these can be marked as NOT NULL, thereby increasing performance and lowering storage space.
It may not be possible to eliminate all NULLs in all tables and still have a clean design, but there is a considerable advantage in eliminating as many NULLs as possible. The optimizer works much faster with this information, and if you can eliminate all NULLs in a table you can regain a considerable amount of storage space.
I know that performance isn't something that DBAs think about all that much, but you can only throw a limited amount of memory and processor power at a solution, a some point you're going to have to start thinking about logical and physical design.
Also note that this is only for true RDBMSes and I am basing the technical portion of my answers off SQL Server. The listed T-SQL to find nullable columns without nulls is also from SQL Server.