Re: Efficiency of is null v\s nullif

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: prabhakar (prabhakarnarvekar_at_msn.com)
Date: 02/15/05 

Date: Tue, 15 Feb 2005 14:12:05 +0530

I am comparing two fields in a table to check if they are not equal.This can
be done in following two ways
1) select * from tablefornull where ((col1 <> col2) or (col1 is null and
col2 is not null) or (col1 is not null and col2 is null))

2) select * from tablefornull where not(nullif(col1,col2) is null and
nullif(col2,col1) is null)

which one will be more efficient? The qeury execution plan is same for both

"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:uJFAyhzEFHA.2572@tk2msftngp13.phx.gbl...
> prabhakar wrote:
>> hi ,
>> i am querying for data in which i compare nulls . And null = null for
>> my scenario. Thus i need to either use extra where conditions in the
>> query to check if the two compared fields are nulls. This i can do
>> either by using is null or nullif function. Which of the two options
>> will be more efficient for SQL to execute considering large data
>> scenario? eg:
>>
>> Use NULLIF function
>> where (nullif(col1,col2) is null and nullif(col2,col1) is null
>> where not(nullif(col1,col2) is null and nullif(col2,col1) is null)
>>
>>
>> Use IS NULL construct
>>
>> where ((col1 = col2) or (col1 is null and col2 is null)) where ((col1
>> <> col2) or (col1 is null and col2 is not null) or (col1 is not null
>> and col2 is null))
>
> Not sure I understand. You have two WHERE clauses in each example. Can you
> post the real SELECTs you are comparing. You can also check the execution
> plans to see which one is cleaner.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Relevant Pages

  • Are there any INTRINSIC advantages of the C# language over VB.NET?
    ... in comparing the two all of the advantages that I have come ... execution falls into the next case; this substantially ... When you have a series of terminating braces in C#, ... Can anyone come up with advantages of the C# language ...
    (microsoft.public.dotnet.general)
  • Re: cxx performance
    ... > Yes, the real performance lies in execution, but when executing the TDD ... > grand total of much lost productivity over a day. ... I assume that you are being sarcastic about comparing ... > me - I was looking for ways to speed up the compilation in the current ...
    (comp.os.vms)
  • Re: Human rights warning over school discipline plans
    ... comparing the thing in question to something else. ... My inferrence is that the thing that the consequence of executions are ... being compared with is alternative sentences for murder that are used ... execution are compared to the situation in the same jurisdiction ...
    (uk.legal)
  • Re: circles
    ... I wasn't trying to offend your execution time... ... Just comparing. ... subroutines are, the faster they execute. ... and compare the time of 100 loops of your code ...
    (comp.sys.apple2.programmer)