Replaces Nulls with 0 without using a function?

Question

I want to replace null values in a table but without using a function such as isnull because its dealing with a large amount of data and slowing it down.

everywhere online says isnull and coalesce but is there any way without using such functions. I need this because the query

OPENING_OTHER + OPENING_FEE + OPENING_INT AS TOTAL_BALANCE

If one value is NULL then the total balance is always null

Cheers

Yeah, why not using something like `UPDATE table SET field=2 WHERE field=NULL`? — Simon, Nov 01 '13 at 14:35
"Slow" == "full table scan". Which is unavoidable in your scenario - it isn't as though you have an index to identify null columns ;) It's not that you're using a "function". "coalesce" or "isnull" are perfectly appropriate choices here. — paulsm4, Nov 01 '13 at 14:36
They're built in functions and should be vastly quicker than the I/O costs involved in running the rest of your query. — Damien_The_Unbeliever, Nov 01 '13 at 14:37
Is `TOTAL_BALANCE` a column in a table? Or a column in a SELECT query? Or a calculated column (in a table or view?) — ypercubeᵀᴹ, Nov 01 '13 at 14:37
@RichardC in that case you're going to want to post the view definition. — swasheck, Nov 01 '13 at 14:44
@RichardC which makes it not a VIEW. I agree --- the problem here isn't `COALESCE` or `ISNULL` ... the source problem will be identified later. — swasheck, Nov 01 '13 at 14:52

Jodrell · Answer 1 · 2013-11-01T14:57:09.937

2

No, how can you do something without doing anything?

You could permanenetly replace the NULL values with 0 but that would waste a lot of storage.

Transforming your data in a SELECT statement is not terribley costly if you use built in functions designed for that purpose.

The use of coalesce will be the quickest, most effiecient and expediant way to do this.

coalesce(OPENING_OTHER, 0) + coalesce(OPENING_FEE, 0) + 
    coalesce(OPENING_INT, 0) AS TOTAL_BALANCE

In fact, I'd suggest the actual cost of coalesce is so small that its hard to measure.

edited Nov 01 '13 at 14:57

answered Nov 01 '13 at 14:34

Jodrell

34,946
5
87
124

+1 I think it's a very good answer :) – paulsm4 Nov 01 '13 at 14:37
Hi, it is not a presentation concern i edited the question it is making values change to null – rkyyk Nov 01 '13 at 14:44
@RichardC I removed that from my answer. – Jodrell Nov 01 '13 at 14:47

Replaces Nulls with 0 without using a function?

1 Answers1