Optimal SQL

Anybody know why Query B is so much more expensive than Query A? Would you agree that Query C is the best bet?

Goal:
Return 0 results

Given:
CustomerID will never be '99999'
CustomerID is the primary key

A - SELECT * FROM Customers WHERE CustomerID=99999
B - SELECT * FROM Customers WHERE CustomerID<>CustomerID?
C - SELECT TOP 0 * FROM Customersin A you are scanning a select range of rows from a clustered index. SQL server is doing a binary search and thus able to eliminate most of the data within a couple of evaluations.

in B you are scanning all rows of a clustered index. Since you are asking SQL server to compare two attributes of a record, each record must be evaluated.

in C you are scanning an internal table.

I personnaly prefer select * from <table name> where 1 = 2.|||HI,
Agree with Paul .
select * from <table name> where 1 = 0
Cheers
Gola
Originally posted by Paul Young
in A you are scanning a select range of rows from a clustered index. SQL server is doing a binary search and thus able to eliminate most of the data within a couple of evaluations.

in B you are scanning all rows of a clustered index. Since you are asking SQL server to compare two attributes of a record, each record must be evaluated.

in C you are scanning an internal table.

I personnaly prefer select * from <table name> where 1 = 2.|||Thanks Paul. What you said makes perfect sense. I knew how SQL impelemented indexing, but failed to see the obvious need to skip the indexing and go straight to a table scan and scan each record individually when comparing one field to another.

Gola's example would seem to be more efficient, as would option C or as a friend pointed out, even...

SELECT * FROM Customers WHERE CustomerID IS NULL.

...would work since we are looking at the primary key.

Thanks guys.

Moki|||I don't agree with your friend. You will still be scanning part of the index. If the table doesn't have an index you will be scanning the table. My suggestion will always scan an internal table regardless.

the only diffrence between my suggestion and Gola's is the numbers used to generate a false condition.|||I'll need to think about that last one...maybe I don't understand MSSQL's implementation like I thought. I would have thought the primary key was always indexed and so checking for NULL would be the same as running thru the B-Tree looking for a value.

In either case, how about option C, "SELECT TOP 0...."?|||Yes, in SQL server the primary key will have some type of index. My point was that your friends suggestion will cause a partial index scan when a table has a usable index OR a table scan if no index is present or no existing indexes are present to match your where clause.

Bottom line, C is the best choice of the three.|||Ok, thanks. I thought about it (Option C) and the suggestion from you (and Gola), and think I like y'alls better. Only because I'm questioning the use of "...TOP 0..." and its conformance (or possible lack thereof) to the ANSI-92 standard...just in case that becomes a customer's constraint in the future.

Thanks again.