Nulls and SQL Server 2008

I am having trouble inserting data with nulls into a dimension table with SQl Server 2008. I've changed the default values for the columns in the dimension table to be the "dummy" values - NA, 00, Not Available, etc. But, when I insert data with nulls into the table, it doesn't replace the the null values with the default. It's inserting the null value. If I change the definition of the column to NOT NULL, then the load bombs.

I figure I can either define the the default values in the ETL software or do case statements to overcome this issue, but it makes me wonder why I bothered to change the default values of the table.

So I ask, what would Ralph do?

Is there a way to change the DDL to make it automatically insert the default value when it encounters a null during the insert? Do I have to use something other than an INSERT INTO. Do I use a combination of ISNULL and CASE STATEMENTS in the SQL? Or should I define it in the ETL software (Data Manager)?

The point is the declarative DEFAULT value would only apply if the column is left out in your insert list. So if you have NULL in your insert, you mean to put NULL in the column instead of default value. Simple solution, enforce the default values in your ETL by COALESCE function instead of table declaration.

You're awesome. You've added hours to my life. Thanks.

Do you use ISNULL when you can and COALESCE when isnull isn't an option? Or would you use COALESCE consistently?

I was wonder if there was a performance benefit of the ISNULL or vice versa.

If there is no difference, then I would want to use COALESCE just to keep it consistent.

Consistency within your work is one thing, but more importantly, COALESCE is an ANSI standard complied by all major DB vendors. Although less known and hard to spell, COALESCE can take more than two parameters, hence more powerful. I guess ISNULL, like the old way of joining tables, ie. FROM table1, table2 instead of JOIN ... ON, still exists purely for backward compatibility, and should be replaced by COALESCE in all new codes.

Yeah, I make it a point to tell my developers to use COALESCE over ISNULL.
Here's a post on a performance comparison of the two:
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx

Make sure you read the comments at the bottom.

In summary, Adam shows that in SQL 2008, COALESCE is slightly faster (not the case in 2005).

Thanks guys. I'm a convert.

It's funny how learning a new function can brighten a day.