Query Optimizer selection...

Hi Team,
Let me explain my doubts.
There are two types of query optimizer
1. Syntax-based
2. Cost-based.
Whatever I know, SQL Server internally maintain this optimization
issue. Now I have two doubts.
1. Can user select the option before running any proc/sql statement?
2. If question no. 1 is wrong then how SQL server decides which
optimizer to select?
Please guide me.
Thanks in advance.
Regards
Arijit ChatterjeeSQL Server uses cost-based optimization. It is possible to use
"hints" to influence the execution plan generated by the optimizer,
but this is generally only required in extreme circumstances.
The details of how the optimizer works would fill a book rather than a
newsgroup message. The good news is that it works well.
Roy Harvey
Beacon Falls, ct
On 7 Aug 2006 00:10:00 -0700, arijitchatterjee123@.yahoo.co.in wrote:

>Hi Team,
>Let me explain my doubts.
>There are two types of query optimizer
>1. Syntax-based
>2. Cost-based.
>Whatever I know, SQL Server internally maintain this optimization
>issue. Now I have two doubts.
>1. Can user select the option before running any proc/sql statement?
>2. If question no. 1 is wrong then how SQL server decides which
>optimizer to select?
>Please guide me.
>Thanks in advance.
>Regards
>Arijit Chatterjee|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:ksfed2p3uu43n0lu70omnq60ngtupphohg@.
4ax.com...
> SQL Server uses cost-based optimization. It is possible to use
> "hints" to influence the execution plan generated by the optimizer,
> but this is generally only required in extreme circumstances.
> The details of how the optimizer works would fill a book rather than a
> newsgroup message. The good news is that it works well.
Also note, that any such book (though I'll still recommend Kalen's excellent
Inside SQL 2000) will be out of date soon after it's published since it's
not at all unusual (in fact very likely) that Service Packs will make
changes to the cost optimizer.
(We in fact hit a degenerate case with SP3 that w/o a hint would pick the
wrong path and do a table scan. SP4 solved the problem and we subsequently
removed the hint.)
[vbcol=seagreen]
> Roy Harvey
> Beacon Falls, ct
> On 7 Aug 2006 00:10:00 -0700, arijitchatterjee123@.yahoo.co.in wrote:
>|||Thanks Group,
Appreciate your comments.
Regards
Arijit