Difference

What are the major difference b/w index hint and creating index on column
Thanks
Joh wrote:
> What are the major difference b/w index hint and creating index on
> column
> Thanks
They really can't be compared as you pose the question. You cannot use
an index hint without the index existing and the index can be created on
one or more columns. An index hint tells the optimizer to use a
particular index on a table when executing a query. It's not recommended
users use hints because it prevents the optimizer from generating what
it believes is the best execution plan. Keeping your statistics up to
date can help prevent underperforming execution plans from being used.
In some rare cases, SQL Server may make not make an optimal decision for
a query and you can use a hint to keep SQL Server in line. However, if
you do this, it's extremely important to document the hint and revisit
the query periodically to make sure it continues to work as expected and
to re-test the query without the hint when new service packs are
installed.
David Gugick
Imceda Software
www.imceda.com
|||Thanks David...I really appreciate
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uuNcCLIXFHA.612@.TK2MSFTNGP12.phx.gbl...
> Joh wrote:
> They really can't be compared as you pose the question. You cannot use
> an index hint without the index existing and the index can be created on
> one or more columns. An index hint tells the optimizer to use a
> particular index on a table when executing a query. It's not recommended
> users use hints because it prevents the optimizer from generating what
> it believes is the best execution plan. Keeping your statistics up to
> date can help prevent underperforming execution plans from being used.
> In some rare cases, SQL Server may make not make an optimal decision for
> a query and you can use a hint to keep SQL Server in line. However, if
> you do this, it's extremely important to document the hint and revisit
> the query periodically to make sure it continues to work as expected and
> to re-test the query without the hint when new service packs are
> installed.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Index hints forces the optimizer to utilize an index. This restricts the
optimizer from doing what it is good at i.e. finding the optimized plan /
index for the given query based on the data and clauses specified.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Joh" <joh@.mailcity.com> wrote in message
news:evDIu%23HXFHA.1044@.TK2MSFTNGP10.phx.gbl...
> What are the major difference b/w index hint and creating index on column
> Thanks
>