February 1, 2015 at 9:55 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:12 pm
be brave. Test it. On a DEV machine.
set statistics io on;
set statistics time on;
-- execute your query here
select *
from dbo.V_OnlineSalesOrder
WHERE IncomeGroup = 'Moderate';
set statistics io off;
set statistics time off;
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply