Sunday, November 13, 2011

Combining Columns Ignoring Null Columns

Let’s start with an example.

image

You will see that MiddleName column has NULL values. If you want to combine all four columns and display them in one column you will write following query.

image

Result is,

image

You will realize the obvious problems from the above result set. To avoid this what you should is, use the ISNULL function.

image

Though the above will solve your problem, as a developer you need to keep an eye on nullability of the column.

With Denali, you have a new function called CONCAT.

image

And the result is,

image

which is what we were looking for.

Next question is, how about the performance.

To answer that I will display query execution plans for two queries, one with CONCAT and the other with ISNULL function.

image

So there is no additional cost with the new function.

No comments:

Post a Comment