# Thread: Optimal SQL

1. Registered User
Join Date
Apr 2003
Location
CA, East Bay
Posts
13

## Unanswered: 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 Customers

2. Registered User
Join Date
Feb 2002
Location
Houston, TX
Posts
809
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.

3. Registered User
Join Date
Apr 2003
Posts
18
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.

4. Registered User
Join Date
Apr 2003
Location
CA, East Bay
Posts
13

## Makes Sense

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

5. Registered User
Join Date
Feb 2002
Location
Houston, TX
Posts
809
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.

6. Registered User
Join Date
Apr 2003
Location
CA, East Bay
Posts
13
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....."?

7. Registered User
Join Date
Feb 2002
Location
Houston, TX
Posts
809
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.

8. Registered User
Join Date
Apr 2003
Location
CA, East Bay
Posts
13
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•