Does use of COALESCE slows down the query performance

Question

I was just curious to know that the use of COALESCE slows down the query performance. I read it in an article that we should avoid using COALESE and instead of it, we should use case for this. Is it really true about COALESE that it hinders the query perfromance? Any help will be appreciated.

Thanks

Answer 1

I can't be sure about SQL Server. However I will give my experience of it in Oracle just in case there may be a correlation. It depends on the context that you are using it. For example consider the case where you may have a predicate in your SQL statement that reads as follows:

SELECT UserId, FirstName, Surname,  DepartmentId
FROM Users
WHERE DepartmentId = COALESCE(pDepartmentId,DepartmentId)

The use of the COALESCE in this context (in Oracle at least - and yes, I realise this is a SQL Server question) would preclude the use of any index on the DepartmentId.

As I said, it depends on the context and although this is from an Oracle point of view I would suspect the same may apply to SQL Server.

EDIT

I have just seen your post on the context that you use it and it is exactly the same as the above.

What you could try in your case would be the following which is a neat trick I learned to avoid the problem - again - Oracle but could work for you.

SELECT UserId, FirstName, Surname,  DepartmentId
FROM Users
WHERE pDepartmentId IS NOT NULL AND DepartmentId = pDepartmentId

UNION ALL

SELECT UserId, FirstName, Surname,  DepartmentId
FROM Users
WHERE pDepartmentId IS NULL

Sorry for interloping on a SQL Server post but if this helps you it may be worth it.

Final Edit

As I commented below the CASE statement does indeed get around the issue. Therefore, no need for the over-complicated UNION hack (which incidently gives the same cost as the CASE). Unfortunately, I still need to use this in Oracle. Another win for the MSSQL case (no pun intended!!)