July 2, 2008 at 8:07 pm
Hello,
I just saw someone use this type of code in a query:
Query A.
SELECT id, mystring
FROM mytable
WHERE mystring > ''
I figured Query A is intended to be equivalent to:
Query B.
SELECT id, mystring
FROM mytable
WHERE LEN(mystring) > 0
My questions are:
1. Is Query A equivalent in results to Query B?
2. Is Query A faster than Query B?
Just wondering, especially about question 2. The Query A method looks pretty neat if it runs faster and returns the same results.
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 3, 2008 at 12:06 am
webrunner (7/2/2008)
My questions are:1. Is Query A equivalent in results to Query B?
To the best of my knowledge, yes
2. Is Query A faster than Query B?
Possibly. It depends on indexing and data distribution. If there's no index on mystring, then they are probably equivalent (or nearly so). If all of the table satisfies that predicate, then they are probably fairly equivalent.
As always, feel free to test for yourself.
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
July 4, 2008 at 5:57 am
If you use a manipulation (like LEN, UPPER, etc) on the left side of a comparison, then SQL is unable to use existing indexes of that column. If there is no index on the specified column, the performance probably will be similar. But if the query normaly should use an index for that column, the query-analyser will not use this because of the manipulation of that column and therefore performance will most likely drop.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply