is there an advantage in using WITH INDEX in select statement when
PK_ID is a clustered index , will this speed up the process?

SELECT xml FROM FTUS.dbo.data with(nolock, index(PK_ID)) WHERE ID =
200

Eli

First off this is a Clustering group which is a High Availability topic not
indexes. But in any case there is no need to specify a hint in this case and
rarely any other if you have the correct indexes to begin with. In this case
the column is a PK. That means there can only ever be at most 1 row that
matches the value you supply with a = expression in the WHERE clause. Since
it is a PK there should be a Primary Key constraint on it and it uses an
index to enforce the constraint. So the optimizer will favor this index in
this scenario and there should be no need what so ever for a hint. Never use
hints unless you are sure there is a valid reason to and never assume a hint
is the first recourse. Now the question of should this be a clustered index
and will it be faster than a non-clustered index depends on how you access
the table overall. If you always query by the PK then making it the
clustered index will alleviate a bookmark lookup and be the fastest way to
access the row.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Emil" <emil.chirambattu (AT) gmail (DOT) com> wrote

First off this is a Clustering group which is a High Availability topic not
indexes. But in any case there is no need to specify a hint in this case and
rarely any other if you have the correct indexes to begin with. In this case
the column is a PK. That means there can only ever be at most 1 row that
matches the value you supply with a = expression in the WHERE clause. Since
it is a PK there should be a Primary Key constraint on it and it uses an
index to enforce the constraint. So the optimizer will favor this index in
this scenario and there should be no need what so ever for a hint. Never use
hints unless you are sure there is a valid reason to and never assume a hint
is the first recourse. Now the question of should this be a clustered index
and will it be faster than a non-clustered index depends on how you access
the table overall. If you always query by the PK then making it the
clustered index will alleviate a bookmark lookup and be the fastest way to
access the row.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Emil" <emil.chirambattu (AT) gmail (DOT) com> wrote

First off this is a Clustering group which is a High Availability topic not
indexes. But in any case there is no need to specify a hint in this case and
rarely any other if you have the correct indexes to begin with. In this case
the column is a PK. That means there can only ever be at most 1 row that
matches the value you supply with a = expression in the WHERE clause. Since
it is a PK there should be a Primary Key constraint on it and it uses an
index to enforce the constraint. So the optimizer will favor this index in
this scenario and there should be no need what so ever for a hint. Never use
hints unless you are sure there is a valid reason to and never assume a hint
is the first recourse. Now the question of should this be a clustered index
and will it be faster than a non-clustered index depends on how you access
the table overall. If you always query by the PK then making it the
clustered index will alleviate a bookmark lookup and be the fastest way to
access the row.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Emil" <emil.chirambattu (AT) gmail (DOT) com> wrote

First off this is a Clustering group which is a High Availability topic not
indexes. But in any case there is no need to specify a hint in this case and
rarely any other if you have the correct indexes to begin with. In this case
the column is a PK. That means there can only ever be at most 1 row that
matches the value you supply with a = expression in the WHERE clause. Since
it is a PK there should be a Primary Key constraint on it and it uses an
index to enforce the constraint. So the optimizer will favor this index in
this scenario and there should be no need what so ever for a hint. Never use
hints unless you are sure there is a valid reason to and never assume a hint
is the first recourse. Now the question of should this be a clustered index
and will it be faster than a non-clustered index depends on how you access
the table overall. If you always query by the PK then making it the
clustered index will alleviate a bookmark lookup and be the fastest way to
access the row.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Emil" <emil.chirambattu (AT) gmail (DOT) com> wrote

First off this is a Clustering group which is a High Availability topic not
indexes. But in any case there is no need to specify a hint in this case and
rarely any other if you have the correct indexes to begin with. In this case
the column is a PK. That means there can only ever be at most 1 row that
matches the value you supply with a = expression in the WHERE clause. Since
it is a PK there should be a Primary Key constraint on it and it uses an
index to enforce the constraint. So the optimizer will favor this index in
this scenario and there should be no need what so ever for a hint. Never use
hints unless you are sure there is a valid reason to and never assume a hint
is the first recourse. Now the question of should this be a clustered index
and will it be faster than a non-clustered index depends on how you access
the table overall. If you always query by the PK then making it the
clustered index will alleviate a bookmark lookup and be the fastest way to
access the row.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Emil" <emil.chirambattu (AT) gmail (DOT) com> wrote

First off this is a Clustering group which is a High Availability topic not
indexes. But in any case there is no need to specify a hint in this case and
rarely any other if you have the correct indexes to begin with. In this case
the column is a PK. That means there can only ever be at most 1 row that
matches the value you supply with a = expression in the WHERE clause. Since
it is a PK there should be a Primary Key constraint on it and it uses an
index to enforce the constraint. So the optimizer will favor this index in
this scenario and there should be no need what so ever for a hint. Never use
hints unless you are sure there is a valid reason to and never assume a hint
is the first recourse. Now the question of should this be a clustered index
and will it be faster than a non-clustered index depends on how you access
the table overall. If you always query by the PK then making it the
clustered index will alleviate a bookmark lookup and be the fastest way to
access the row.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Emil" <emil.chirambattu (AT) gmail (DOT) com> wrote

First off this is a Clustering group which is a High Availability topic not
indexes. But in any case there is no need to specify a hint in this case and
rarely any other if you have the correct indexes to begin with. In this case
the column is a PK. That means there can only ever be at most 1 row that
matches the value you supply with a = expression in the WHERE clause. Since
it is a PK there should be a Primary Key constraint on it and it uses an
index to enforce the constraint. So the optimizer will favor this index in
this scenario and there should be no need what so ever for a hint. Never use
hints unless you are sure there is a valid reason to and never assume a hint
is the first recourse. Now the question of should this be a clustered index
and will it be faster than a non-clustered index depends on how you access
the table overall. If you always query by the PK then making it the
clustered index will alleviate a bookmark lookup and be the fastest way to
access the row.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Emil" <emil.chirambattu (AT) gmail (DOT) com> wrote

First off this is a Clustering group which is a High Availability topic not
indexes. But in any case there is no need to specify a hint in this case and
rarely any other if you have the correct indexes to begin with. In this case
the column is a PK. That means there can only ever be at most 1 row that
matches the value you supply with a = expression in the WHERE clause. Since
it is a PK there should be a Primary Key constraint on it and it uses an
index to enforce the constraint. So the optimizer will favor this index in
this scenario and there should be no need what so ever for a hint. Never use
hints unless you are sure there is a valid reason to and never assume a hint
is the first recourse. Now the question of should this be a clustered index
and will it be faster than a non-clustered index depends on how you access
the table overall. If you always query by the PK then making it the
clustered index will alleviate a bookmark lookup and be the fastest way to
access the row.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Emil" <emil.chirambattu (AT) gmail (DOT) com> wrote

First off this is a Clustering group which is a High Availability topic not
indexes. But in any case there is no need to specify a hint in this case and
rarely any other if you have the correct indexes to begin with. In this case
the column is a PK. That means there can only ever be at most 1 row that
matches the value you supply with a = expression in the WHERE clause. Since
it is a PK there should be a Primary Key constraint on it and it uses an
index to enforce the constraint. So the optimizer will favor this index in
this scenario and there should be no need what so ever for a hint. Never use
hints unless you are sure there is a valid reason to and never assume a hint
is the first recourse. Now the question of should this be a clustered index
and will it be faster than a non-clustered index depends on how you access
the table overall. If you always query by the PK then making it the
clustered index will alleviate a bookmark lookup and be the fastest way to
access the row.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Emil" <emil.chirambattu (AT) gmail (DOT) com> wrote