Combining Columns Ignoring Null Columns

Let’s start with an example.

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.

Result is,

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

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.

And the result is,

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.

So there is no additional cost with the new function.