Nulls and SQL Server 2008

View previous topic View next topic Go down

Nulls and SQL Server 2008

Post  Jeff Smith on Wed Mar 30, 2011 4:59 pm

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)?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Nulls and SQL Server 2008

Post  hang on Wed Mar 30, 2011 6:28 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Nulls and SQL Server 2008

Post  Jeff Smith on Wed Mar 30, 2011 11:32 pm

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

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Nulls and SQL Server 2008

Post  Jeff Smith on Thu Mar 31, 2011 1:56 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Nulls and SQL Server 2008

Post  hang on Thu Mar 31, 2011 5:30 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Nulls and SQL Server 2008

Post  John Simon on Thu Mar 31, 2011 8:07 pm

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).

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Nulls and SQL Server 2008

Post  Jeff Smith on Fri Apr 01, 2011 9:20 am

Thanks guys. I'm a convert.

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

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Nulls and SQL Server 2008

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum