If i have only one index and that being a clustered index on say a datetime
column namely date1 and if my query is
select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
If I look at the query plan and it says its doing an index scan, does that
mean its actually touching each and every page of that table or will it
somehow start at the page that has the first entry for '2/1/2008' and
continues through the linked list at the leaf level of the pages and stops
after it reaches '2/3/2008' ?
How is this different if instead of a clustered index, its a non clustered
index ?
Thanks
John
It is doing Clustered Index Scan , you meant? If you have CI on the table
that means SQL Server logicaly orders all data by Clustered Index Key.
It depends on the query , an optimizer may or may not decide to do a scan
, for example the table is pretty small .
In this case it scans index pages toread the data which ordered (logicaly)
by date column
In you case I'd suggest t create an index on col1,col2 and dt columns called
COVERING index.
> How is this different if instead of a clustered index, its a non clustered
> index ?
The difference is that clusterd index contains at the bottom level the
actual data , while noclustetred contains pointers to the data pages.
"John Doe" <Johndoe@.jd.com> wrote in message
news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
> If i have only one index and that being a clustered index on say a
> datetime column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
> If I look at the query plan and it says its doing an index scan, does that
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered
> index ?
> Thanks
>
|||Uri,
In my case, its doing a clustered index scan and wanted to know if as a
result, its touching all the pages that may have dates prior to '2/1' and
after '2/3' as my query is only seeking to obtain data between '2/1/2008 and
'2/3/2008' and as you mentioned that data in the CI is ordered. Let me know
how the storage engine fetches the pages.
Also if it was a non clustered index on date1 instead of a clustered index,
i take it that the leaf level of the non clustered index is also
ordered..right ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
> John
> It is doing Clustered Index Scan , you meant? If you have CI on the table
> that means SQL Server logicaly orders all data by Clustered Index Key.
> It depends on the query , an optimizer may or may not decide to do a scan
> , for example the table is pretty small .
> In this case it scans index pages toread the data which ordered (logicaly)
> by date column
> In you case I'd suggest t create an index on col1,col2 and dt columns
> called COVERING index.
>
> The difference is that clusterd index contains at the bottom level the
> actual data , while noclustetred contains pointers to the data pages.
>
> "John Doe" <Johndoe@.jd.com> wrote in message
> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>
|||John
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
Think about CI as phone book which is 'ordered' by LastName ,so if you
want to search by Lastname is easy to traverse and get it
But what if you want to search by NCI -FirstName, then you will have to
page by page which requires an 'extra work'
I'd suggest to take some course or buying a book to underastand the
structure and behaviour.
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>
|||On Feb 19, 12:45Xpm, "John Doe" <John...@.jd.com> wrote:
> If i have only one index and that being a clustered index on say a datetime
> column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
> If I look at the query plan and it says its doing an index scan, does that
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered
> index ?
> Thanks
With clustered indexes records are stored on the leaf level and
therefore faster but non-clustered indexes leaf level pages have
locations to the page of searched record and therefore slower. HTH.
|||If you have a clustered index, yes, it will go to the first entry in the
table with a datetime between '2/1/2008' and '2/3/2008', do a partial scan
of the clustered index and stop as soon as it finds an datetime >
'2/3/2008'. BTW, it is best in SQL Server to specify datetimes as
'yyyy-mm-ddThh:mm:ss.mmm'
or 'yyyy-mm-ddThh:mm:ss'
or 'yyyymmdd'
When you use a date format like '2/1/2008', that is Feb 1 in some locations
and Jan 2 in others. But '20080201' is Feb 1 everywhere.
For a nonclustered index, the answer is, "it depends". If the index is
nonclustered, then SQL Server knows the rows for any given value of dt might
be scattered throughout the physical table. So, for example, the first row
might be in page 1000, followed by a bunch of rows in other pages, followed
by another row in page 1000. But by this time page 1000 might not be in
memory anymore, so the page must be physically read again (this could
possibly happen many times). So SQL Server attempts to keep statistics on
how many rows are in each range and will attempt to estimate what percentage
of the table your query will return. If it is a small percentage, it will
use your index on dt, go to the first entry in the index with a date >=
'2/1/2008', start there and scan the index until it reaches a row with a dt
> '2/3'2008' and then stop. For each row it finds in the index on dt, it
will then use the clustered index to find the actual row and return your
values. But if it is a large percentage, then SQL Server will just scan the
entire clustered index (that is, it won't use the nonclustered index on dt
at all), because it knows that way it only has to read each physical page in
the clustered index once.
As Uri points out, your nonclustered index can be what is known as a
"covering index" for your query. This occurs when every column you need for
your query is in the index (either explicitly as part of the key or in the
INCLUDED columns or implicitly because the column is in the key of the
clustered index). When that happens, SQL Server knows it can satisify the
query requirements without ever reading from the table, so it will use the
index and only scan the part of the index it needs for the range of data you
want.
Tom
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>
Showing posts with label col1. Show all posts
Showing posts with label col1. Show all posts
Wednesday, March 28, 2012
Index scan
If i have only one index and that being a clustered index on say a datetime
column namely date1 and if my query is
select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
If I look at the query plan and it says its doing an index scan, does that
mean its actually touching each and every page of that table or will it
somehow start at the page that has the first entry for '2/1/2008' and
continues through the linked list at the leaf level of the pages and stops
after it reaches '2/3/2008' ?
How is this different if instead of a clustered index, its a non clustered
index ?
ThanksJohn
It is doing Clustered Index Scan , you meant? If you have CI on the table
that means SQL Server logicaly orders all data by Clustered Index Key.
It depends on the query , an optimizer may or may not decide to do a scan
, for example the table is pretty small .
In this case it scans index pages toread the data which ordered (logicaly)
by date column
In you case I'd suggest t create an index on col1,col2 and dt columns called
COVERING index.
> How is this different if instead of a clustered index, its a non clustered
> index ?
The difference is that clusterd index contains at the bottom level the
actual data , while noclustetred contains pointers to the data pages.
"John Doe" <Johndoe@.jd.com> wrote in message
news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
> If i have only one index and that being a clustered index on say a
> datetime column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
> If I look at the query plan and it says its doing an index scan, does that
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered
> index ?
> Thanks
>|||Uri,
In my case, its doing a clustered index scan and wanted to know if as a
result, its touching all the pages that may have dates prior to '2/1' and
after '2/3' as my query is only seeking to obtain data between '2/1/2008 and
'2/3/2008' and as you mentioned that data in the CI is ordered. Let me know
how the storage engine fetches the pages.
Also if it was a non clustered index on date1 instead of a clustered index,
i take it that the leaf level of the non clustered index is also
ordered..right ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
> John
> It is doing Clustered Index Scan , you meant? If you have CI on the table
> that means SQL Server logicaly orders all data by Clustered Index Key.
> It depends on the query , an optimizer may or may not decide to do a scan
> , for example the table is pretty small .
> In this case it scans index pages toread the data which ordered (logicaly)
> by date column
> In you case I'd suggest t create an index on col1,col2 and dt columns
> called COVERING index.
>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
> The difference is that clusterd index contains at the bottom level the
> actual data , while noclustetred contains pointers to the data pages.
>
> "John Doe" <Johndoe@.jd.com> wrote in message
> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or will
>> it somehow start at the page that has the first entry for '2/1/2008' and
>> continues through the linked list at the leaf level of the pages and
>> stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>|||Let me get back to the original question. SQL Server may decide to go for
partial scan (reading a range of values) if data is ordered.
And yes, data is ordered logically by the key in both cases, difference
being that in the CI, ALL data is in the leaf level.
MC
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||John
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
Think about CI as phone book which is 'ordered' by LastName ,so if you
want to search by Lastname is easy to traverse and get it
But what if you want to search by NCI -FirstName, then you will have to
page by page which requires an 'extra work'
I'd suggest to take some course or buying a book to underastand the
structure and behaviour.
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||On Feb 19, 12:45=A0pm, "John Doe" <John...@.jd.com> wrote:
> If i have only one index and that being a clustered index on say a datetim=e
> column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'=
> If I look at the query plan and it says its doing an index scan, does that=
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops=
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered=
> index ?
> Thanks
With clustered indexes records are stored on the leaf level and
therefore faster but non-clustered indexes leaf level pages have
locations to the page of searched record and therefore slower. HTH.|||If you have a clustered index, yes, it will go to the first entry in the
table with a datetime between '2/1/2008' and '2/3/2008', do a partial scan
of the clustered index and stop as soon as it finds an datetime >
'2/3/2008'. BTW, it is best in SQL Server to specify datetimes as
'yyyy-mm-ddThh:mm:ss.mmm'
or 'yyyy-mm-ddThh:mm:ss'
or 'yyyymmdd'
When you use a date format like '2/1/2008', that is Feb 1 in some locations
and Jan 2 in others. But '20080201' is Feb 1 everywhere.
For a nonclustered index, the answer is, "it depends". If the index is
nonclustered, then SQL Server knows the rows for any given value of dt might
be scattered throughout the physical table. So, for example, the first row
might be in page 1000, followed by a bunch of rows in other pages, followed
by another row in page 1000. But by this time page 1000 might not be in
memory anymore, so the page must be physically read again (this could
possibly happen many times). So SQL Server attempts to keep statistics on
how many rows are in each range and will attempt to estimate what percentage
of the table your query will return. If it is a small percentage, it will
use your index on dt, go to the first entry in the index with a date >='2/1/2008', start there and scan the index until it reaches a row with a dt
> '2/3'2008' and then stop. For each row it finds in the index on dt, it
will then use the clustered index to find the actual row and return your
values. But if it is a large percentage, then SQL Server will just scan the
entire clustered index (that is, it won't use the nonclustered index on dt
at all), because it knows that way it only has to read each physical page in
the clustered index once.
As Uri points out, your nonclustered index can be what is known as a
"covering index" for your query. This occurs when every column you need for
your query is in the index (either explicitly as part of the key or in the
INCLUDED columns or implicitly because the column is in the key of the
clustered index). When that happens, SQL Server knows it can satisify the
query requirements without ever reading from the table, so it will use the
index and only scan the part of the index it needs for the range of data you
want.
Tom
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>
column namely date1 and if my query is
select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
If I look at the query plan and it says its doing an index scan, does that
mean its actually touching each and every page of that table or will it
somehow start at the page that has the first entry for '2/1/2008' and
continues through the linked list at the leaf level of the pages and stops
after it reaches '2/3/2008' ?
How is this different if instead of a clustered index, its a non clustered
index ?
ThanksJohn
It is doing Clustered Index Scan , you meant? If you have CI on the table
that means SQL Server logicaly orders all data by Clustered Index Key.
It depends on the query , an optimizer may or may not decide to do a scan
, for example the table is pretty small .
In this case it scans index pages toread the data which ordered (logicaly)
by date column
In you case I'd suggest t create an index on col1,col2 and dt columns called
COVERING index.
> How is this different if instead of a clustered index, its a non clustered
> index ?
The difference is that clusterd index contains at the bottom level the
actual data , while noclustetred contains pointers to the data pages.
"John Doe" <Johndoe@.jd.com> wrote in message
news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
> If i have only one index and that being a clustered index on say a
> datetime column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
> If I look at the query plan and it says its doing an index scan, does that
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered
> index ?
> Thanks
>|||Uri,
In my case, its doing a clustered index scan and wanted to know if as a
result, its touching all the pages that may have dates prior to '2/1' and
after '2/3' as my query is only seeking to obtain data between '2/1/2008 and
'2/3/2008' and as you mentioned that data in the CI is ordered. Let me know
how the storage engine fetches the pages.
Also if it was a non clustered index on date1 instead of a clustered index,
i take it that the leaf level of the non clustered index is also
ordered..right ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
> John
> It is doing Clustered Index Scan , you meant? If you have CI on the table
> that means SQL Server logicaly orders all data by Clustered Index Key.
> It depends on the query , an optimizer may or may not decide to do a scan
> , for example the table is pretty small .
> In this case it scans index pages toread the data which ordered (logicaly)
> by date column
> In you case I'd suggest t create an index on col1,col2 and dt columns
> called COVERING index.
>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
> The difference is that clusterd index contains at the bottom level the
> actual data , while noclustetred contains pointers to the data pages.
>
> "John Doe" <Johndoe@.jd.com> wrote in message
> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or will
>> it somehow start at the page that has the first entry for '2/1/2008' and
>> continues through the linked list at the leaf level of the pages and
>> stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>|||Let me get back to the original question. SQL Server may decide to go for
partial scan (reading a range of values) if data is ordered.
And yes, data is ordered logically by the key in both cases, difference
being that in the CI, ALL data is in the leaf level.
MC
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||John
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
Think about CI as phone book which is 'ordered' by LastName ,so if you
want to search by Lastname is easy to traverse and get it
But what if you want to search by NCI -FirstName, then you will have to
page by page which requires an 'extra work'
I'd suggest to take some course or buying a book to underastand the
structure and behaviour.
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||On Feb 19, 12:45=A0pm, "John Doe" <John...@.jd.com> wrote:
> If i have only one index and that being a clustered index on say a datetim=e
> column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'=
> If I look at the query plan and it says its doing an index scan, does that=
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops=
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered=
> index ?
> Thanks
With clustered indexes records are stored on the leaf level and
therefore faster but non-clustered indexes leaf level pages have
locations to the page of searched record and therefore slower. HTH.|||If you have a clustered index, yes, it will go to the first entry in the
table with a datetime between '2/1/2008' and '2/3/2008', do a partial scan
of the clustered index and stop as soon as it finds an datetime >
'2/3/2008'. BTW, it is best in SQL Server to specify datetimes as
'yyyy-mm-ddThh:mm:ss.mmm'
or 'yyyy-mm-ddThh:mm:ss'
or 'yyyymmdd'
When you use a date format like '2/1/2008', that is Feb 1 in some locations
and Jan 2 in others. But '20080201' is Feb 1 everywhere.
For a nonclustered index, the answer is, "it depends". If the index is
nonclustered, then SQL Server knows the rows for any given value of dt might
be scattered throughout the physical table. So, for example, the first row
might be in page 1000, followed by a bunch of rows in other pages, followed
by another row in page 1000. But by this time page 1000 might not be in
memory anymore, so the page must be physically read again (this could
possibly happen many times). So SQL Server attempts to keep statistics on
how many rows are in each range and will attempt to estimate what percentage
of the table your query will return. If it is a small percentage, it will
use your index on dt, go to the first entry in the index with a date >='2/1/2008', start there and scan the index until it reaches a row with a dt
> '2/3'2008' and then stop. For each row it finds in the index on dt, it
will then use the clustered index to find the actual row and return your
values. But if it is a large percentage, then SQL Server will just scan the
entire clustered index (that is, it won't use the nonclustered index on dt
at all), because it knows that way it only has to read each physical page in
the clustered index once.
As Uri points out, your nonclustered index can be what is known as a
"covering index" for your query. This occurs when every column you need for
your query is in the index (either explicitly as part of the key or in the
INCLUDED columns or implicitly because the column is in the key of the
clustered index). When that happens, SQL Server knows it can satisify the
query requirements without ever reading from the table, so it will use the
index and only scan the part of the index it needs for the range of data you
want.
Tom
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>
Monday, March 12, 2012
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:
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
>> >>
>> >
>>
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
>> >>
>> >
>>
Sunday, February 19, 2012
Index behaviour.
Hi all,
I have a non clustered index IDX_Col1234 on Col1,Col2,Col3,col4.
Which of the following Query would benefit from the above mentioned index ?
Query 1:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col5 = @.Val
Query 2:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col1 = @.val1
AND Col2 = @.val2
AND Col3 = @.val3
AND Col4 = @.val4
Query 3:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col1 = @.val1
AND Col2 = @.val2
Query 4:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col3 = @.val3
AND Col4 = @.val4
Query 5:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col1 = @.val1
AND Col4 = @.val4
Query 6:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col4 = @.val4
AND Col2 = @.val2
Thanks in advance,
DBAnalyst
Hi,
Here is a short summary of the use of the index:
QUERY 1:
no index is used because col5 is not added to the index
QUERY 2:
index will be used and then a lookup will be done to search for col5 which is not included in the index
QUERY 3:
index will be used and then a lookup will be done to search for col5 which is not included in the index
QUERY 4:
Index will be used and then a lookup will be done to search for col5 which is not included in the index
Here the index is less performant because the columns that are referenced, are not in front of the list of columns in the index.
QUERY 5:
Index will be used and then a lookup will be done to search for col5 which is not included in the index.
Here the index gives better results compared to QUERY 4 because Col1 is included as first column
QUERY 6:
Same as QUERY 4
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Geert's comments are right, provided there are no other indexes on these columns. You could have multiple indexes on the table - if you have one with only col1 and col2 this might get used instead of the one you mentioned. SQL tries to match the index that has the most columns in the WHERE condition. If the columns in your SELECT list are from the Index, then its faster, if the columns are not in any of the indexes, it has to do lookup for those columns.|||Also you can check the same by applying the index hint on your SELECT statment then check the estimated execution plan.|||I would like to add my two cents here, an existing index will qualify to be use in the execution plan only when the order of the columns in the where clause follow the same order as the index structure. Otherwise is doing an index scan and an index scan is not really improving the performance.
If you still have questions, I invite you to create the table dump test data and compare the execution plans with your own eyes.
So on my opinion queries 4 and 6 don't match the index structure that's why they scan the index.
Index behaviour.
Hi all,
I have a non clustered index IDX_Col1234 on Col1,Col2,Col3,col4.
Which of the following Query would benefit from the above mentioned index ?
Query 1:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col5 = @.Val
Query 2:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col1 = @.val1
AND Col2 = @.val2
AND Col3 = @.val3
AND Col4 = @.val4
Query 3:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col1 = @.val1
AND Col2 = @.val2
Query 4:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col3 = @.val3
AND Col4 = @.val4
Query 5:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col1 = @.val1
AND Col4 = @.val4
Query 6:
=======
SELECT Col1, Col2 , Col3, Col4, Col5 FROM TABLE
WHERE Col4 = @.val4
AND Col2 = @.val2
Thanks in advance,
DBAnalyst
Hi,
Here is a short summary of the use of the index:
QUERY 1:
no index is used because col5 is not added to the index
QUERY 2:
index will be used and then a lookup will be done to search for col5 which is not included in the index
QUERY 3:
index will be used and then a lookup will be done to search for col5 which is not included in the index
QUERY 4:
Index will be used and then a lookup will be done to search for col5 which is not included in the index
Here the index is less performant because the columns that are referenced, are not in front of the list of columns in the index.
QUERY 5:
Index will be used and then a lookup will be done to search for col5 which is not included in the index.
Here the index gives better results compared to QUERY 4 because Col1 is included as first column
QUERY 6:
Same as QUERY 4
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Geert's comments are right, provided there are no other indexes on these columns. You could have multiple indexes on the table - if you have one with only col1 and col2 this might get used instead of the one you mentioned. SQL tries to match the index that has the most columns in the WHERE condition. If the columns in your SELECT list are from the Index, then its faster, if the columns are not in any of the indexes, it has to do lookup for those columns.|||Also you can check the same by applying the index hint on your SELECT statment then check the estimated execution plan.|||I would like to add my two cents here, an existing index will qualify to be use in the execution plan only when the order of the columns in the where clause follow the same order as the index structure. Otherwise is doing an index scan and an index scan is not really improving the performance.
If you still have questions, I invite you to create the table dump test data and compare the execution plans with your own eyes.
So on my opinion queries 4 and 6 don't match the index structure that's why they scan the index.
Subscribe to:
Posts (Atom)