index hints on deletes

Can I not use index hints on delete statements as below ? Using SQL 2005
delete
from dbo.table1 WITH (index(idx_test))
where col1 <= 5
Are you getting an error? If so it would be nice to know what.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> Can I not use index hints on delete statements as below ? Using SQL 2005
> delete from dbo.table1 WITH (index(idx_test))
> where col1 <= 5
|||Msg 1069, Level 15, State 1, Line 3
Index hints are only allowed in a FROM clause.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Are you getting an error? If so it would be nice to know what.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>
|||How about this:
delete a
from dbo.table1 AS a WITH (index(idx_test))
where a.col1 <= 5
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> Msg 1069, Level 15, State 1, Line 3
> Index hints are only allowed in a FROM clause.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||or just
DELETE table1 from table1 with(index(idx_test))
WHERE col <= 5
But as far as I can tell from the BOL syntax description, the original
DELETE statement is perfectly legal. Is this some kind of bug?
Linchi
"Andrew J. Kelly" wrote:

> How about this:
> delete a
> from dbo.table1 AS a WITH (index(idx_test))
> where a.col1 <= 5
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>
|||What I'm reading is that DELETE only takes hints from a limited list
<table_hint_limited> and index hints are not in that list.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3E750688-DD9F-4567-BD65-515160B60119@.microsoft.com...[vbcol=seagreen]
> or just
> DELETE table1 from table1 with(index(idx_test))
> WHERE col <= 5
> But as far as I can tell from the BOL syntax description, the original
> DELETE statement is perfectly legal. Is this some kind of bug?
> Linchi
> "Andrew J. Kelly" wrote:

index hints on deletes

Can I not use index hints on delete statements as below ? Using SQL 2005
delete
from dbo.table1 WITH (index(idx_test))
where col1 <= 5Are you getting an error? If so it would be nice to know what.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> Can I not use index hints on delete statements as below ? Using SQL 2005
> delete from dbo.table1 WITH (index(idx_test))
> where col1 <= 5|||Msg 1069, Level 15, State 1, Line 3
Index hints are only allowed in a FROM clause.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Are you getting an error? If so it would be nice to know what.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> Can I not use index hints on delete statements as below ? Using SQL 2005
>> delete from dbo.table1 WITH (index(idx_test))
>> where col1 <= 5
>|||How about this:
delete a
from dbo.table1 AS a WITH (index(idx_test))
where a.col1 <= 5
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> Msg 1069, Level 15, State 1, Line 3
> Index hints are only allowed in a FROM clause.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> Are you getting an error? If so it would be nice to know what.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> Can I not use index hints on delete statements as below ? Using SQL 2005
>> delete from dbo.table1 WITH (index(idx_test))
>> where col1 <= 5
>|||or just
DELETE table1 from table1 with(index(idx_test))
WHERE col <= 5
But as far as I can tell from the BOL syntax description, the original
DELETE statement is perfectly legal. Is this some kind of bug?
Linchi
"Andrew J. Kelly" wrote:
> How about this:
> delete a
> from dbo.table1 AS a WITH (index(idx_test))
> where a.col1 <= 5
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> > Msg 1069, Level 15, State 1, Line 3
> > Index hints are only allowed in a FROM clause.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> >> Are you getting an error? If so it would be nice to know what.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Hassan" <hassan@.test.com> wrote in message
> >> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
> >> Can I not use index hints on delete statements as below ? Using SQL 2005
> >>
> >> delete from dbo.table1 WITH (index(idx_test))
> >> where col1 <= 5
> >>
> >
>|||What I'm reading is that DELETE only takes hints from a limited list
<table_hint_limited> and index hints are not in that list.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:3E750688-DD9F-4567-BD65-515160B60119@.microsoft.com...
> or just
> DELETE table1 from table1 with(index(idx_test))
> WHERE col <= 5
> But as far as I can tell from the BOL syntax description, the original
> DELETE statement is perfectly legal. Is this some kind of bug?
> Linchi
> "Andrew J. Kelly" wrote:
>> How about this:
>> delete a
>> from dbo.table1 AS a WITH (index(idx_test))
>> where a.col1 <= 5
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OdKr5ujUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>> > Msg 1069, Level 15, State 1, Line 3
>> > Index hints are only allowed in a FROM clause.
>> >
>> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> > news:uhAqFXjUIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> >> Are you getting an error? If so it would be nice to know what.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Hassan" <hassan@.test.com> wrote in message
>> >> news:udtwNEjUIHA.5404@.TK2MSFTNGP06.phx.gbl...
>> >> Can I not use index hints on delete statements as below ? Using SQL
>> >> 2005
>> >>
>> >> delete from dbo.table1 WITH (index(idx_test))
>> >> where col1 <= 5
>> >>
>> >
>>

Index hints in a view.

Hi
SQL Server 2000 SP4
I am using view in my stored procedure. The view gets data from only one
table. I can not use table directly due to some reasons. I would like to add
an index hint to this table. Is View definition the only place for that? I
tried to add hint index to the view directly in the FROM clause of my SP but
i got a message that the hint is ignored.
Thanks in advance.For the view, you can do index hints on indexed views only. Otherwise, to
specify a specific index for a table in the view, you will need to modify th
e
view and supply the table hint for the index you want to use for the require
d
table using the WITH INDEX () hint.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Alexander Korol" wrote:

> Hi
> SQL Server 2000 SP4
> I am using view in my stored procedure. The view gets data from only one
> table. I can not use table directly due to some reasons. I would like to a
dd
> an index hint to this table. Is View definition the only place for that? I
> tried to add hint index to the view directly in the FROM clause of my SP b
ut
> i got a message that the hint is ignored.
> Thanks in advance.

Index hints ignored

Under what conditions will index hints be ignored ? I forced a query to use
index hints and it gave me a warning as such
Warning: Index hints supplied for view 'dbo.view1' will be ignored.
and did not use the hints..
On Dec 28, 7:31Xam, "Hassan" <has...@.test.com> wrote:
> Under what conditions will index hints be ignored ? I forced a query to use
> index hints and it gave me a warning as such
> Warning: Index hints supplied for view 'dbo.view1' will be ignored.
> and did not use the hints..
you can use set forcaplan on
|||No, please read the documentation for SET FORCEPLAN. It is not related to
this issue at all. SET FORCEPLAN forces a particular join order and
(usually) a join type, but will not override the optimizers determination
that a hint cannot be used.
Hassan, there is no simple set of guidelines. In general, a hint will be
ignored it if is impossible or meaningless in the situation where you are
trying to use it. For your particular case, trying to force an index on a
view to be used, you must also use the NOEXPAND hint or the hint will be
ignored.
Please read the documentation on "Resolving Indexes on Views"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/430593ce-5dec-48d5-86ed-78388091880a.htm
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"SB" <othellomy@.yahoo.com> wrote in message
news:28d87e5a-3894-4bd7-945d-5ab728a0573d@.s8g2000prg.googlegroups.com...
On Dec 28, 7:31 am, "Hassan" <has...@.test.com> wrote:
> Under what conditions will index hints be ignored ? I forced a query to
> use
> index hints and it gave me a warning as such
> Warning: Index hints supplied for view 'dbo.view1' will be ignored.
> and did not use the hints..
you can use set forcaplan on
|||On Dec 28, 10:17 am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> No, please read the documentation for SET FORCEPLAN. It is not related to
> this issue at all. SET FORCEPLAN forces a particular join order and
> (usually) a join type, but will not override the optimizers determination
> that a hint cannot be used.
> Hassan, there is no simple set of guidelines. In general, a hint will be
> ignored it if is impossible or meaningless in the situation where you are
> trying to use it. For your particular case, trying to force an index on a
> view to be used, you must also use the NOEXPAND hint or the hint will be
> ignored.
> Please read the documentation on "Resolving Indexes on Views"
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/430593ce-5dec-48d5-86ed-783-88091880a.htm
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://sqlblog.com
> "SB" <othell...@.yahoo.com> wrote in message
> news:28d87e5a-3894-4bd7-945d-5ab728a0573d@.s8g2000prg.googlegroups.com...
> On Dec 28, 7:31 am, "Hassan" <has...@.test.com> wrote:
>
>
> you can use set forcaplan on
Maybe its join order but whenever the optimizer ignored index hints I
used forceplan and then it worked. Obviously there was a performance
gain otherwise I wouldn't go into so much trouble.

Index hints ignored

Under what conditions will index hints be ignored ? I forced a query to use
index hints and it gave me a warning as such
Warning: Index hints supplied for view 'dbo.view1' will be ignored.
and did not use the hints..On Dec 28, 7:31=A0am, "Hassan" <has...@.test.com> wrote:
> Under what conditions will index hints be ignored ? I forced a query to us=e
> index hints and it gave me a warning as such
> Warning: Index hints supplied for view 'dbo.view1' will be ignored.
> and did not use the hints..
you can use set forcaplan on|||No, please read the documentation for SET FORCEPLAN. It is not related to
this issue at all. SET FORCEPLAN forces a particular join order and
(usually) a join type, but will not override the optimizers determination
that a hint cannot be used.
Hassan, there is no simple set of guidelines. In general, a hint will be
ignored it if is impossible or meaningless in the situation where you are
trying to use it. For your particular case, trying to force an index on a
view to be used, you must also use the NOEXPAND hint or the hint will be
ignored.
Please read the documentation on "Resolving Indexes on Views"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/430593ce-5dec-48d5-86ed-78388091880a.htm
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"SB" <othellomy@.yahoo.com> wrote in message
news:28d87e5a-3894-4bd7-945d-5ab728a0573d@.s8g2000prg.googlegroups.com...
On Dec 28, 7:31 am, "Hassan" <has...@.test.com> wrote:
> Under what conditions will index hints be ignored ? I forced a query to
> use
> index hints and it gave me a warning as such
> Warning: Index hints supplied for view 'dbo.view1' will be ignored.
> and did not use the hints..
you can use set forcaplan on|||On Dec 28, 10:17 am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> No, please read the documentation for SET FORCEPLAN. It is not related to
> this issue at all. SET FORCEPLAN forces a particular join order and
> (usually) a join type, but will not override the optimizers determination
> that a hint cannot be used.
> Hassan, there is no simple set of guidelines. In general, a hint will be
> ignored it if is impossible or meaningless in the situation where you are
> trying to use it. For your particular case, trying to force an index on a
> view to be used, you must also use the NOEXPAND hint or the hint will be
> ignored.
> Please read the documentation on "Resolving Indexes on Views"
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/430593ce-5dec-48d5-86ed-783-88091880a.htm
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://sqlblog.com
> "SB" <othell...@.yahoo.com> wrote in message
> news:28d87e5a-3894-4bd7-945d-5ab728a0573d@.s8g2000prg.googlegroups.com...
> On Dec 28, 7:31 am, "Hassan" <has...@.test.com> wrote:
> > Under what conditions will index hints be ignored ? I forced a query to
> > use
> > index hints and it gave me a warning as such
> > Warning: Index hints supplied for view 'dbo.view1' will be ignored.
> > and did not use the hints..
> you can use set forcaplan on
Maybe its join order but whenever the optimizer ignored index hints I
used forceplan and then it worked. Obviously there was a performance
gain otherwise I wouldn't go into so much trouble.

Index hints ignored

Under what conditions will index hints be ignored ? I forced a query to use
index hints and it gave me a warning as such
Warning: Index hints supplied for view 'dbo.view1' will be ignored.
and did not use the hints..On Dec 28, 7:31=A0am, "Hassan" <has...@.test.com> wrote:
> Under what conditions will index hints be ignored ? I forced a query to us=[/vbcol
]
e[vbcol=seagreen]
> index hints and it gave me a warning as such
> Warning: Index hints supplied for view 'dbo.view1' will be ignored.
> and did not use the hints..
you can use set forcaplan on|||No, please read the documentation for SET FORCEPLAN. It is not related to
this issue at all. SET FORCEPLAN forces a particular join order and
(usually) a join type, but will not override the optimizers determination
that a hint cannot be used.
Hassan, there is no simple set of guidelines. In general, a hint will be
ignored it if is impossible or meaningless in the situation where you are
trying to use it. For your particular case, trying to force an index on a
view to be used, you must also use the NOEXPAND hint or the hint will be
ignored.
Please read the documentation on "Resolving Indexes on Views"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/430593ce-5dec-48d5-86ed-7838
8091880a.htm
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"SB" <othellomy@.yahoo.com> wrote in message
news:28d87e5a-3894-4bd7-945d-5ab728a0573d@.s8g2000prg.googlegroups.com...
On Dec 28, 7:31 am, "Hassan" <has...@.test.com> wrote:
> Under what conditions will index hints be ignored ? I forced a query to
> use
> index hints and it gave me a warning as such
> Warning: Index hints supplied for view 'dbo.view1' will be ignored.
> and did not use the hints..
you can use set forcaplan on|||On Dec 28, 10:17 am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> No, please read the documentation for SET FORCEPLAN. It is not related to
> this issue at all. SET FORCEPLAN forces a particular join order and
> (usually) a join type, but will not override the optimizers determination
> that a hint cannot be used.
> Hassan, there is no simple set of guidelines. In general, a hint will be
> ignored it if is impossible or meaningless in the situation where you are
> trying to use it. For your particular case, trying to force an index on a
> view to be used, you must also use the NOEXPAND hint or the hint will be
> ignored.
> Please read the documentation on "Resolving Indexes on Views"
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/430593ce-5dec-48d5-86ed-78
3-88091880a.htm
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://sqlblog.com
> "SB" <othell...@.yahoo.com> wrote in message
> news:28d87e5a-3894-4bd7-945d-5ab728a0573d@.s8g2000prg.googlegroups.com...
> On Dec 28, 7:31 am, "Hassan" <has...@.test.com> wrote:
>
>
>
> you can use set forcaplan on
Maybe its join order but whenever the optimizer ignored index hints I
used forceplan and then it worked. Obviously there was a performance
gain otherwise I wouldn't go into so much trouble.