Whis is fast != 0 or > 0 (Query Performance)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Whis is fast != 0 or > 0 (Query Performance)

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2010-09-13 : 04:53:39

Hi all,

When filtering which is fast !=0 or >0

Thanks and regards,
Babu Kumarasamy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-13 : 05:21:30

Please try it and report back your findings.

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-13 : 05:22:54

the two options aren't the same. I'm assuming you are talking about a number type column?

!= 0 (which in SQL SERVER should be <> 0) is NOT THE SAME as >0

Both should give similar performance though. Both could use an index.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-13 : 05:32:36

Example:

Run the following and use "show actual execution plan"


DECLARE @foo TABLE (
	[number] INT PRIMARY KEY
	)

INSERT @foo ([number])
SELECT
	ROW_NUMBER() OVER (ORDER BY a.[1]) - 1000
FROM
	   (SELECT 1 AS [1] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS a
CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS b
CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS c
CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d
CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS e
CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS f

SELECT
	[number]
FROM
	@foo
WHERE
	[number] > 0


SELECT
	[number]
FROM
	@foo
WHERE
	[number] != 0

The two selects at the end will provide different returns (you are asking for different things) but both perform an INDEX SEEK.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-13 : 05:45:43

Depending on granularity, one of the index seeks may eventually turn into a index scan.
So there is no way for us to test what OP wants.

N 56°04'39.26"
E 12°55'05.63"

Sachin.Nand

2937 Posts

Posted - 2010-09-13 : 05:52:21

Well I think it will all depend on how 'distributed' your 0's are stored in the data pages & their density.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources