help on INDEX

Hi,
I'm a beginner and so perhaps this is a dumb question,
but here goes.
here's two query statements:
SELECT *
FROM FewUniques WITH(index(0))
WHERE Status = 'Inactive'
SELECT *
FROM FewUniques WITH(index(inFewUniquesStatus))
--inFewUniquesStatus is a predefined INDEX
WHERE Status = 'Inactive'
what's the meaning of WITH(index(0)) and WITH(index(inFewUniquesStatus))?
Is index() a function or not?
I thank you very much,for your assistance:)
fridaydreamThis are directives for the optimizer (called optimizer hints) which tells t
he optimizer which index
to use for the query. 0 means a table scan. These should be avoided in produ
ction code, let the
optimizer pick the best execution plan it can.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<fridaydream@.163.com> wrote in message news:uP0rO$TJFHA.3196@.TK2MSFTNGP15.phx.gbl...darkred">
> Hi,
> I'm a beginner and so perhaps this is a dumb question,
> but here goes.
> here's two query statements:
> SELECT *
> FROM FewUniques WITH(index(0))
> WHERE Status = 'Inactive'
> SELECT *
> FROM FewUniques WITH(index(inFewUniquesStatus))
> --inFewUniquesStatus is a predefined INDEX
> WHERE Status = 'Inactive'
>
> what's the meaning of WITH(index(0)) and WITH(index(inFewUniquesStatus))?
> Is index() a function or not?
>
> I thank you very much,for your assistance:)
> fridaydream
>
>
>|||Not a dumb question.
INDEX() is not a function. In this context, it is a table hint. When
you specify "WITH(INDEX(x))" where x is either the index ID or the index
name, you are telling the query engine to use that specific index to
retrieve the data from that table.
99% of the time you shouldn't specify table hints but should just trust
the query optimiser to pick the best indexes for the query plan - it
usually gets it right.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
fridaydream@.163.com wrote:

>Hi,
>I'm a beginner and so perhaps this is a dumb question,
>but here goes.
>here's two query statements:
>SELECT *
>FROM FewUniques WITH(index(0))
>WHERE Status = 'Inactive'
>SELECT *
>FROM FewUniques WITH(index(inFewUniquesStatus))
> --inFewUniquesStatus is a predefined INDEX
>WHERE Status = 'Inactive'
>
>what's the meaning of WITH(index(0)) and WITH(index(inFewUniquesStatus))?
>Is index() a function or not?
>
>I thank you very much,for your assistance:)
>fridaydream
>
>
>
>|||Thanks a lot!
so I guess this is not a standard SQL Grammar,like WITH(INDEX(xx)),Is it?
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> '
news:u%236OOHUJFHA.3132@.TK2MSFTNGP12.phx.gbl...
Not a dumb question.
INDEX() is not a function. In this context, it is a table hint. When you
specify "WITH(INDEX(x))" where x is either the index ID or the index name,
you are telling the query engine to use that specific index to retrieve the
data from that table.
99% of the time you shouldn't specify table hints but should just trust the
query optimiser to pick the best indexes for the query plan - it usually
gets it right.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
fridaydream@.163.com wrote:
Hi,
I'm a beginner and so perhaps this is a dumb question,
but here goes.
here's two query statements:
SELECT *
FROM FewUniques WITH(index(0))
WHERE Status = 'Inactive'
SELECT *
FROM FewUniques WITH(index(inFewUniquesStatus))
--inFewUniquesStatus is a predefined INDEX
WHERE Status = 'Inactive'
what's the meaning of WITH(index(0)) and WITH(index(inFewUniquesStatus))?
Is index() a function or not?
I thank you very much,for your assistance:)
fridaydream|||What do you mean by "standard SQL Grammar"? If you refer to the ANSI SQL sta
ndard, then you are
correct. There's nothing like that in the ANSI SQL standard. Actually ANSI S
QL doesn't say a work
about indexes in the first place.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fridaydream" <fridaydream@.163.com> wrote in message news:ev1TTaUJFHA.3340@.TK2MSFTNGP12.phx
.gbl...
> Thanks a lot!
> so I guess this is not a standard SQL Grammar,like WITH(INDEX(xx)),Is it?

> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> '
> news:u%236OOHUJFHA.3132@.TK2MSFTNGP12.phx.gbl...
> Not a dumb question.
> INDEX() is not a function. In this context, it is a table hint. When you
> specify "WITH(INDEX(x))" where x is either the index ID or the index name,
> you are telling the query engine to use that specific index to retrieve th
e
> data from that table.
> 99% of the time you shouldn't specify table hints but should just trust th
e
> query optimiser to pick the best indexes for the query plan - it usually
> gets it right.
> --
> mike hodgson | database administrator | mallesons stephen jaques
> T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
> E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
>
> fridaydream@.163.com wrote:
> Hi,
> I'm a beginner and so perhaps this is a dumb question,
> but here goes.
> here's two query statements:
> SELECT *
> FROM FewUniques WITH(index(0))
> WHERE Status = 'Inactive'
> SELECT *
> FROM FewUniques WITH(index(inFewUniquesStatus))
> --inFewUniquesStatus is a predefined INDEX
> WHERE Status = 'Inactive'
>
> what's the meaning of WITH(index(0)) and WITH(index(inFewUniquesStatus))?
> Is index() a function or not?
>
> I thank you very much,for your assistance:)
> fridaydream
>
>
>
>
>