Between vs In

I have a need for a criteria to be = 32 or 33 and wondered if BETWEEN 32 AND
33 would be faster than IN(32,33). This is an indexed field. Thanks.
DavidWhen you timed this yourself, what was the result?
What did the query analyzer explain plans say?
I'd bet the optimizer would alter your code to
whatever it thinks is fastest (yes, sql server optimizer
does do this). That said, I'd bet you wouldn't
see a different in either approach.
I'd use the IN clause because it is a direct
comparison versus a math comparison.
Robbe Morris - 2004/2005 Microsoft MVP C#
EggHeadCafe's RSS Search Engine
http://www.eggheadcafe.com/articles...ch/default.aspx
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:e0%23qscwuFHA.2504@.tk2msftngp13.phx.gbl...
>I have a need for a criteria to be = 32 or 33 and wondered if BETWEEN 32
>AND 33 would be faster than IN(32,33). This is an indexed field. Thanks.
> David
>|||I know it would have been faster to try it than to ask that question. The
best answer to performance questions is to try it out for yourself. We don't
have access to your schema, your data and your hardware.
David Portas
SQL Server MVP
--|||Hi
as others suggested, you can test with execution paln, time etc.
But include OR also, that is the best.
IN may have additional ovehead.
Regards
R.D
"David Portas" wrote:

> I know it would have been faster to try it than to ask that question. The
> best answer to performance questions is to try it out for yourself. We don
't
> have access to your schema, your data and your hardware.
> --
> David Portas
> SQL Server MVP
> --
>
>|||On Fri, 16 Sep 2005 23:06:02 -0700, R.D wrote:

>Hi
>as others suggested, you can test with execution paln, time etc.
>But include OR also, that is the best.
>IN may have additional ovehead.
Hi R.D.,
IN(value, value, ...) is exactly equal to a set of OR conditions. Just
check out the execution plan of a query that uses an IN condition.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Best way to do a free text search

HiI have a .net 2 website that works from a sql 2000 db. I am building a form that will allow the user to type in any search criteria and based on their input I need to produce some results (that's the simplest way to put it)I will need to search various parts of my db to find similar sounding information, I was just wondering what is the best way to do this. I had the following thoughts1) Search columns using Soundex tsql function (but not sure how good this is?)2) Remove all noise words from user input (eg, and, or, the etc...) and then use a regular expression to search the fields in the dbAlternatively are their some third party components to do can do this for me Many thanks in advance

You can full text search enable the database, and create a full text index on the column in the table you want to search. Then you will be able to search any word that appears inside that column. Here are a couple of links that might be helpful to you.

http://www.databasejournal.com/features/mssql/article.php/3441981

http://www.wherecanibuyit.co.uk/ASP/full-text-search.html

http://msdn2.microsoft.com/en-us/library/ms177652.aspx

|||

Hi

Thanks for that, it was a great help

I have noticed that their is a RowNumber method in SQL 2005, is their any way to do this functionality in SQL 2000

One method I thought of was to put the data in temp table and loop through each record and give it a number, and then pull out 10 records at a time as the user pages through.

Are their any other ways I could do this.

Many thanks in advance

|||

The short answer is yes. You may want to search for articles on Custom Paging with SQL Server 2000. There are ways to do it, but they are not as simple or clean as the ROW_NUMBER() function. Here is an article to get you started.

http://www.4guysfromrolla.com/webtech/042606-1.shtml