Tale of <> Operator

Question

Tale of <> Operator

karthik M

One Orange Chip

Points: 29139
More actions
November 5, 2007 at 6:43 am

#62626

Hi Experts,
I have read so many articles about <> operator. All those tales are repeatedly saying 'dont use <> operator in your query, because it is a non searchable argument, optimizer will override it. so table scan method has been used by the optimizer'.
Optimizer also designed by the programmer. I dont know, why couldn't they rewirte the optimizer programmer to accept <> operator ? is there any specific reason to avoid <> operator ?
what is the story standing back of this <> operator ?
karthik

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If you think about it, there's no way that <> can be searchable. Searches are to find specific values, finding all except a specific value requires a scan

Imagine a telephone book. If you ahd to find all the entries except for people with a surname of Smith, would you be able to search for specific entries, or would you have to read through the telephone book from beginning to end, ignoring the entries for Smith?

I don't think I've ever personally told someone to avoid the <> operator. If you want all entries except for one value, that's what you have to use. Just be aware, without other arguments present, it will require a table/index scan.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

CREATE TABLE#Sample

(

RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

theValue TINYINT

)

INSERT#Sample

SELECTABS(CHECKSUM(NEWID())) % 3

FROMsyscolumns AS c1

CROSS JOINsyscolumns AS c2

CREATE INDEX IX_Peso ON #Sample (theValue)

SELECTtheValue

FROM#Sample

WHEREtheValue = 1

/*

|--Index Seek(OBJECT: ([tempdb].[dbo].[#Sample]), SEEK: ([tempdb].[dbo].[#Sample].[theValue]=(1)) ORDERED FORWARD)

*/

SELECTtheValue

FROM#Sample

WHEREtheValue <> 2

/*

|--Index Seek ( OBJECT: ([tempdb].[dbo].[#Sample]), SEEK: ( [tempdb].[dbo].[#Sample].[theValue] (2) ) ORDERED FORWARD )

*/

DROP INDEX #Sample.IX_Peso

SELECTtheValue

FROM#Sample

WHEREtheValue = 1

/*

|--Clustered Index Scan(OBJECT: ([tempdb].[dbo].[#Sample]), WHERE: ([tempdb].[dbo].[#Sample].[theValue]=(1)))

*/

SELECTtheValue

FROM#Sample

WHEREtheValue <> 2

/*

|--Clustered Index Scan(OBJECT: ([tempdb].[dbo].[#Sample]), WHERE: ([tempdb].[dbo].[#Sample].[theValue]<>(2)))

*/

N 56°04'39.16"
E 12°55'05.25"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Re your second example (SELECT theValue FROM #Sample WHERE theValue <> 2), one thing I learnt recently is that partial index scans (seek to a value, then scan from there to the end/beginning of the table) appear in execution plans as seeks, not scans. Only a full scan appears as a table/index scan

The seek predicate of that query is 'End Range: [tempdb].[dbo].[#Sample].theValue < Scalar Operator((2)), Start Range: [tempdb].[dbo].[#Sample].theValue > Scalar Operator((2))' ie, seek the value in the index I don't want, then scan from there the rest of the table.

My point was, it's not going to seek all the values it wants, but will scan (partially or fully) and ignore the values it doesn't want.

Edit: $%^#$#$$ network problems....

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 4

How to replace this <> operator in or sql query ?

But i dont want to use the below logic.

for example

select columnname from table name where salary <> 0

Alternate way :

select columnname from table name where salary > 0 or salary < 0

Anyway here we removed the <> operator.

But we are using 'OR' which could generate cartesian product.

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Depends on the query. In the example you give, you can't replace the inequality operator. The alternative you give is identical to the original in terms of how its executed and, in fact, is how the engine will execute the query. Just be aware that you will get a partial or complete table scan. (depending on the indexes you have)

In some cases you can replace the !=. If a column has only 4 distinct values in it (eg 0,1,2,3) then instead of saying !=0 you can say in (1,2,3).

p.s. you have salaries that can be less than 0?

p.p.s Can you point us at some of those articles that repeatedly say not to use the inequality?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Can you give me the description about p.s & p.p.s ?

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

They're english abbreviations. Means postscript. Thoughts added after something else, usually only partially related.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Thanks.

URL : http://www.sql-server-performance.com/tips/t_sql_where_p1.aspx

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 9

Gila,

Suppose if i have a big table how should i avoid <> operator ?

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Depends on the query and what you're trying to achieve.

FRom what I read of the article you linked to, the advice isn't to not use the inequality ever, but to avoid it where possible.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 11

If you are really desparate to avoid the <> operator you could try this:

declare @exclusion table

(salary money)

insert @exclusion select 0

select columname

from table t1

left outer join @exclusion t2

on t1.salary = t2.salaray

where t2.salary is null

I'm not saying that this would be anymore efficient (probably not as efficient but others will give more details) but it does avoid the <> operator.

I agree with other posters that using <> is acceptable usage.

Jez

Greg Snidow SSCoach Points: 16108 More actions · Answer 12

karthikeyan (11/7/2007)
Gila,
Suppose if i have a big table how should i avoid <> operator ?

Just curious, but what is the difference between <> and NOT IN?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 13

Absolutely nothing... NOT IN is evaluated to <> for single values and some exclusionary AND's using both for multi-values.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 14

But we are using 'OR' which could generate cartesian product.

Ok, Karth... your turn to explain something... How could the use of 'OR' cause a Cartesian product?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)