Efficiency of is null v\s nullif

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))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|||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:
> 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|||prabhakar wrote:
> 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
If the execution plan is the same, they will run exactly the same. I'm
guessing you are assuming for this exercise that NULL = NULL. Is that
correct?
How about this one:
Select * from tablefornull where isnull(col1, -999) = isnull(col2, -999)
David Gugick
Imceda Software
www.imceda.com|||prabhakar,
The performance difference in the actual processing of an individual row
not differ much between the two syntaxis. What could make a real
difference is using a syntax in a way that the optimizer can use indexes
so only the relevant rows need to be processed. Unfortunately, both
syntaxis will not achieve that.
But why do you want to compare NULLs? NULL is intended to mean something
like "unknown". In any case, the purpose of using NULLs is that one
row's NULL is not (never) equal to another row's NULL, by definition.
That is the definition that the ANSI SQL committee gave it. If you
reject that idea, you could take a look at "SET ANSI_NULLS OFF" in BOL,
and see if that works for you.
If you acknowledge the 'proper' use of NULLs, then you should never use
something like "nullif(col2,col1) is null". If there are some NULLs that
you want to treat as a values, then you could replace them with an
actual value, for example a predefined 'special' value. For varchar
columns this value could be '**Empty'. For int columns this could be
-2,147,483,648, etc.
After that you can use the following query. It is more complex than your
original query, but is likely to perform better.
SELECT *
FROM MyTable T1
WHERE NOT EXISTS (
SELECT 1
FROM MyTable T2
WHERE T2.KeyColumn = T1.KeyColumn
AND T2.Col1=T2.Col2
)
Hope this helps,
Gert-Jan
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 f
or
> 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))

Efficiency in inserting Null Values into fields which allow nulls.

Hi,
I have fields in my table which allow nulls. Is it efficient to not insert anything (the field automatically shows up as null in this case) and leave or store some value into it. The field is a smallint field?

Thanksheres some info from BOL :

Allowing Null Values
The nullability of a column determines if the rows in the table can contain a null value for that column. A null value, or NULL, is not the same as zero (0), blank, or a zero-length character string such as ""; NULL means that no entry has been made. The presence NULL usually implies that the value is either unknown or undefined. For example, a null value in the price column of the titles table of the pubs database does not mean that the book has no price; NULL means that the price is unknown or has not been set.In general, avoid permitting null values because they incur more complexity in queries and updates and because there are other column options, such as PRIMARY KEY constraints, that cannot be used with nullable columns.

If a row is inserted but no value is included for a column that allows null values, Microsoft® SQL Server? 2000 supplies the value NULL (unless a DEFAULT definition or object exists). A column defined with the keyword NULL also accepts an explicit entry of NULL from the user, no matter what data type it is or if it has a default associated with it. The value NULL should not be placed within quotation marks because it will be interpreted as the character string 'NULL', rather than the null value.

Specifying a column as not permitting null values can help maintain data integrity by ensuring that a column in a row always contains data. If null values are not allowed, the user entering data in the table must enter a value in the column or the table row cannot be accepted into the database.

Note Columns defined with a PRIMARY KEY constraint or IDENTITY property cannot allow null values.
|||To be honest I'm not sure. Once you've made the decision to use NULLs (and there are lots of pros/cons to that) I doubt there is much in it. However, if you have a choice *and* you're really trying to eek out the last drop of performance then I'd avoid NULLs. Having said that I'm sure there are thousands of other places to look for better optimisations before you go here.