February 1, 2015 at 9:53 pm
Hello everybody,
I have a task to be accomplished at work and would like to ask you a question about it.
To give you a little background, there is a CRM system with SQL server as its back-end. The CRM uses a view in SQL Server to list all the communications a user has had with his client over any given interval of time. Now there is a requirement to add a new column in the view that tells a user if the communication was filed in automatically or if it happened overnight via an automated archive manager process. I have achieved this using an expression field which is based on the comm_url field in the communications table in database.
example:
create view vCommunications
as
select col1, col2,...,case when comm_url is null then 'Manually filed' else 'Automatically Filed' as Filing
from Communications
alternatively, this can also be achieved by the following:
create view vCommunications
as
select col1, col2,...,isnull(comm_url, 'Manually Filed') as Filing
from Communications
Now my question is, given that there are many rows in the communications table, which of the above two expression fields will be more efficient in performance i.e. CASE versus ISNULL. I've checked a lot on google but I haven't been able to come up with a concrete answer.
Your insights on this will be much appreciated. Look forward to hearing from you all.
Cheers,
Shadab Khan.
February 1, 2015 at 10:00 pm
Performance of both will be same. I tested both queries on my test database. I got same execution plan for both ISNULL and Case statement
February 1, 2015 at 10:08 pm
CASE STATEMENT is an expression while ISNULL is a function.
In case provided by you, i will say it depends on the Data, if its some few hundred or thousand rows then performance of BOTH will be Almost Same.
Check Execution Plan of both.
[ Thanks for the interesting Question, will do some more analysis ]
February 2, 2015 at 1:20 am
Please don't post multiple threads for the same question.
No further replies here please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1656650-3412-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply