documentation clarification on nonclustered index

I have read through the explanation of how non-clustered and clustered index
but I never understand this part. This is a part of non-clustered index
documentation that I got from sql server 2005:
"If the table has a clustered index, or the index is on an indexed view, the
row locator is the clustered index key for the row. If the clustered index
is not a unique index, SQL Server 2005 makes any duplicate keys unique by
adding an internally generated value called a uniqueifier. This four-byte
value is not visible to users. It is only added when required to make the
clustered key unique for use in nonclustered indexes. SQL Server retrieves
the data row by searching the clustered index using the clustered index key
stored in the leaf row of the nonclustered index."
Assuming my table has a non-unique clustered index ; my question is, because
the internally generated uniqueifier, retrieving a row in a non-clustered
index would involve:
1) Search down from the root of the non-clustered index to get to the leaf
page containing the key
2) In the leaf page, it has a clustered index key that would lead to the
data page that has the row, then what does it use the uniqueifier for?
Doesn't it have to linearly search through the data page for *all* the rows
with the value it originally wants?
Hope someone out there have a better explanation. Thank you very much.let me modify my question a little bit
2) In the leaf page, it has a clustered index key that would lead to the
data page that has the row by searching down the clustered index structure
from its root, then what does it use the uniqueifier for? Doesn't it have
to linearly search through the data page for *all* the rows with the value
it originally wants?
How is this related to "Bookmark lookup"?
Thanks!!
"Zen" <zen@.nononospam.com> wrote in message
news:OpnfzD6qGHA.2108@.TK2MSFTNGP03.phx.gbl...
>I have read through the explanation of how non-clustered and clustered
>index but I never understand this part. This is a part of non-clustered
>index documentation that I got from sql server 2005:
> "If the table has a clustered index, or the index is on an indexed view,
> the row locator is the clustered index key for the row. If the clustered
> index is not a unique index, SQL Server 2005 makes any duplicate keys
> unique by adding an internally generated value called a uniqueifier. This
> four-byte value is not visible to users. It is only added when required to
> make the clustered key unique for use in nonclustered indexes. SQL Server
> retrieves the data row by searching the clustered index using the
> clustered index key stored in the leaf row of the nonclustered index."
> Assuming my table has a non-unique clustered index ; my question is,
> because the internally generated uniqueifier, retrieving a row in a
> non-clustered index would involve:
> 1) Search down from the root of the non-clustered index to get to the leaf
> page containing the key
> 2) In the leaf page, it has a clustered index key that would lead to the
> data page that has the row, then what does it use the uniqueifier for?
> Doesn't it have to linearly search through the data page for *all* the
> rows with the value it originally wants?
> Hope someone out there have a better explanation. Thank you very much.
>|||If a table has a clustered index, the clustered key exists in the
nonclustered index leaf level as the 'bookmark', which is also called the
'row locator'. So a bookmark lookup basically is what you have described in
your first post:
1) Search down from the root of the non-clustered index to get to
the leaf
page containing the key
2) In the leaf page, it has a clustered index key << -- this is
the bookmark
that would lead to the data page that has the row < --
this is the bookmark lookup
If the clustered key is not unique, SQL Server add the uniqueifier, which is
considered part of the key, and exists as part of the bookmark in the leaf
levels of your nc indexes. So if your nonclustered index on firstname was
searching for Fred, and then at the leaf level you found the clustered key
(last name) for Fred was Smith, it might be the 4th Smith, the bookmark
would actually Smith-4. That is a unique value now. Just like with any
compound key, the index is sorted by both parts. SQL Server can search for
Smith-4 without having to look through ALL the Smiths.
Is that any clearer?
--
HTH
Kalen Delaney, SQL Server MVP
"Zen" <zen@.nononospam.com> wrote in message
news:%23DpxRJ6qGHA.2232@.TK2MSFTNGP04.phx.gbl...
> let me modify my question a little bit
> 2) In the leaf page, it has a clustered index key that would lead to the
> data page that has the row by searching down the clustered index structure
> from its root, then what does it use the uniqueifier for? Doesn't it have
> to linearly search through the data page for *all* the rows with the value
> it originally wants?
> How is this related to "Bookmark lookup"?
> Thanks!!
>
> "Zen" <zen@.nononospam.com> wrote in message
> news:OpnfzD6qGHA.2108@.TK2MSFTNGP03.phx.gbl...
>>I have read through the explanation of how non-clustered and clustered
>>index but I never understand this part. This is a part of non-clustered
>>index documentation that I got from sql server 2005:
>> "If the table has a clustered index, or the index is on an indexed view,
>> the row locator is the clustered index key for the row. If the clustered
>> index is not a unique index, SQL Server 2005 makes any duplicate keys
>> unique by adding an internally generated value called a uniqueifier. This
>> four-byte value is not visible to users. It is only added when required
>> to make the clustered key unique for use in nonclustered indexes. SQL
>> Server retrieves the data row by searching the clustered index using the
>> clustered index key stored in the leaf row of the nonclustered index."
>> Assuming my table has a non-unique clustered index ; my question is,
>> because the internally generated uniqueifier, retrieving a row in a
>> non-clustered index would involve:
>> 1) Search down from the root of the non-clustered index to get to the
>> leaf page containing the key
>> 2) In the leaf page, it has a clustered index key that would lead to the
>> data page that has the row, then what does it use the uniqueifier for?
>> Doesn't it have to linearly search through the data page for *all* the
>> rows with the value it originally wants?
>> Hope someone out there have a better explanation. Thank you very much.
>>
>|||Thanks for the response, I'm not clear on what's involved in a bookmark
lookup as it usually takes a big chuck of time. Bookmark contains FileID,
pageId, and the row number within the page. Why does it take a long time?
thanks!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e%23QiZH7qGHA.4632@.TK2MSFTNGP05.phx.gbl...
> If a table has a clustered index, the clustered key exists in the
> nonclustered index leaf level as the 'bookmark', which is also called the
> 'row locator'. So a bookmark lookup basically is what you have described
> in your first post:
> 1) Search down from the root of the non-clustered index to get to
> the leaf
> page containing the key
> 2) In the leaf page, it has a clustered index key << -- this is
> the bookmark
> that would lead to the data page that has the row < --
> this is the bookmark lookup
> If the clustered key is not unique, SQL Server add the uniqueifier, which
> is considered part of the key, and exists as part of the bookmark in the
> leaf levels of your nc indexes. So if your nonclustered index on firstname
> was searching for Fred, and then at the leaf level you found the clustered
> key (last name) for Fred was Smith, it might be the 4th Smith, the
> bookmark would actually Smith-4. That is a unique value now. Just like
> with any compound key, the index is sorted by both parts. SQL Server can
> search for Smith-4 without having to look through ALL the Smiths.
> Is that any clearer?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Zen" <zen@.nononospam.com> wrote in message
> news:%23DpxRJ6qGHA.2232@.TK2MSFTNGP04.phx.gbl...
>> let me modify my question a little bit
>> 2) In the leaf page, it has a clustered index key that would lead to the
>> data page that has the row by searching down the clustered index
>> structure from its root, then what does it use the uniqueifier for?
>> Doesn't it have to linearly search through the data page for *all* the
>> rows with the value it originally wants?
>> How is this related to "Bookmark lookup"?
>> Thanks!!
>>
>> "Zen" <zen@.nononospam.com> wrote in message
>> news:OpnfzD6qGHA.2108@.TK2MSFTNGP03.phx.gbl...
>>I have read through the explanation of how non-clustered and clustered
>>index but I never understand this part. This is a part of non-clustered
>>index documentation that I got from sql server 2005:
>> "If the table has a clustered index, or the index is on an indexed view,
>> the row locator is the clustered index key for the row. If the clustered
>> index is not a unique index, SQL Server 2005 makes any duplicate keys
>> unique by adding an internally generated value called a uniqueifier.
>> This four-byte value is not visible to users. It is only added when
>> required to make the clustered key unique for use in nonclustered
>> indexes. SQL Server retrieves the data row by searching the clustered
>> index using the clustered index key stored in the leaf row of the
>> nonclustered index."
>> Assuming my table has a non-unique clustered index ; my question is,
>> because the internally generated uniqueifier, retrieving a row in a
>> non-clustered index would involve:
>> 1) Search down from the root of the non-clustered index to get to the
>> leaf page containing the key
>> 2) In the leaf page, it has a clustered index key that would lead to the
>> data page that has the row, then what does it use the uniqueifier for?
>> Doesn't it have to linearly search through the data page for *all* the
>> rows with the value it originally wants?
>> Hope someone out there have a better explanation. Thank you very much.
>>
>>
>|||Zen wrote:
> Thanks for the response, I'm not clear on what's involved in a bookmark
> lookup as it usually takes a big chuck of time. Bookmark contains FileID,
> pageId, and the row number within the page. Why does it take a long time?
> thanks!
>
A single bookmark alone doesn't necessarily take "a long time", it's
just additional I/O against the database. How are you determining that
it takes "a long time"? Are you looking at the execution plan of a
query? If so, what you're seeing is process of doing the bookmark
lookups for EVERY RECORD that was referenced via an index in your query.
For instance, your query scans an index, finding 100,000 rows that
match your filters. Unfortunately, you've asked for additional fields
that aren't part of the index, so now it has to go to the table, do
100,000 bookmark lookups, retrieving those additional fields from the table.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for the response; so how do that explain the longer time to retrieve
1 datarow with a (unique) primary key with a nc index (clustered index is
for another field) comparing with the primary key being the only field in
the clustered index? For example, if my table has 3 fields:
fileId - primary key
companyId
data
if i make a clustered index on fileid, it's faster to retrieve exactly one
row
if I make clustered index on companyId and nc index on fileId, it would take
longer to retrieve exactly one row
Both situations, I use this select statement
select *
from MyTable
where fileId = @.id
And yes, I look at the execution plan to determine how fast it is.
thanks!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23Y76SGCrGHA.4508@.TK2MSFTNGP04.phx.gbl...
> Zen wrote:
>> Thanks for the response, I'm not clear on what's involved in a bookmark
>> lookup as it usually takes a big chuck of time. Bookmark contains FileID,
>> pageId, and the row number within the page. Why does it take a long
>> time? thanks!
> A single bookmark alone doesn't necessarily take "a long time", it's just
> additional I/O against the database. How are you determining that it
> takes "a long time"? Are you looking at the execution plan of a query?
> If so, what you're seeing is process of doing the bookmark lookups for
> EVERY RECORD that was referenced via an index in your query. For instance,
> your query scans an index, finding 100,000 rows that match your filters.
> Unfortunately, you've asked for additional fields that aren't part of the
> index, so now it has to go to the table, do 100,000 bookmark lookups,
> retrieving those additional fields from the table.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Zen wrote:
> Thanks for the response; so how do that explain the longer time to retrieve
> 1 datarow with a (unique) primary key with a nc index (clustered index is
> for another field) comparing with the primary key being the only field in
> the clustered index? For example, if my table has 3 fields:
> fileId - primary key
> companyId
> data
> if i make a clustered index on fileid, it's faster to retrieve exactly one
> row
> if I make clustered index on companyId and nc index on fileId, it would take
> longer to retrieve exactly one row
> Both situations, I use this select statement
> select *
> from MyTable
> where fileId = @.id
> And yes, I look at the execution plan to determine how fast it is.
> thanks!
>
You're using SELECT *, which means "give me every field in the row", for
a specific fileID value. With a clustered index on fileID, the query
engine only has to hit the table one time, and is able to retrieve all
of the columns it needs from the same row in the clustered index.
With the other indexing arrangement, it takes two hits to get all of the
fields - the first hit is against the index, where it obtains fileID and
a bookmark. The second hit uses the bookmark to go back to the base
table to find the bookmarked record, from where it fetches the remaining
field values.
The second example takes longer because there is simply more work to be
done. This is a classic illustration as to why proper indexing is
crucial to database performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||From what I understand, the leaves of nc index and the intermediate node
right above the leaf level of clustered index contain the same information
(row locator), from your explanation, they are not the same. If they are
the same, then the look up for entire data of the row should be the same
unless the traversing through the two index structures takes different time.
Could you please explain more? thanks!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ef7fioCrGHA.516@.TK2MSFTNGP05.phx.gbl...
> Zen wrote:
>> Thanks for the response; so how do that explain the longer time to
>> retrieve 1 datarow with a (unique) primary key with a nc index (clustered
>> index is for another field) comparing with the primary key being the only
>> field in the clustered index? For example, if my table has 3 fields:
>> fileId - primary key
>> companyId
>> data
>> if i make a clustered index on fileid, it's faster to retrieve exactly
>> one row
>> if I make clustered index on companyId and nc index on fileId, it would
>> take longer to retrieve exactly one row
>> Both situations, I use this select statement
>> select *
>> from MyTable
>> where fileId = @.id
>> And yes, I look at the execution plan to determine how fast it is.
>> thanks!
> You're using SELECT *, which means "give me every field in the row", for a
> specific fileID value. With a clustered index on fileID, the query engine
> only has to hit the table one time, and is able to retrieve all of the
> columns it needs from the same row in the clustered index.
> With the other indexing arrangement, it takes two hits to get all of the
> fields - the first hit is against the index, where it obtains fileID and a
> bookmark. The second hit uses the bookmark to go back to the base table
> to find the bookmarked record, from where it fetches the remaining field
> values.
> The second example takes longer because there is simply more work to be
> done. This is a classic illustration as to why proper indexing is crucial
> to database performance.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Zen wrote:
> From what I understand, the leaves of nc index and the intermediate node
> right above the leaf level of clustered index contain the same information
> (row locator), from your explanation, they are not the same. If they are
> the same, then the look up for entire data of the row should be the same
> unless the traversing through the two index structures takes different time.
> Could you please explain more? thanks!
>
No, I'm afraid I cannot...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The leaf level rows and the node rows in the NC index are similar, but the
number of rows is different.
The leaf level contains a row locator for every single row in table. The
upper levels only contain enough information to traverse down the index
tree. You have to go to the leaf before you can then use the bookmark to
find the actual data row.
--
HTH
Kalen Delaney, SQL Server MVP
"Zen" <zen@.nononospam.com> wrote in message
news:%23l6XapDrGHA.2464@.TK2MSFTNGP03.phx.gbl...
> From what I understand, the leaves of nc index and the intermediate node
> right above the leaf level of clustered index contain the same information
> (row locator), from your explanation, they are not the same. If they are
> the same, then the look up for entire data of the row should be the same
> unless the traversing through the two index structures takes different
> time. Could you please explain more? thanks!
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:ef7fioCrGHA.516@.TK2MSFTNGP05.phx.gbl...
>> Zen wrote:
>> Thanks for the response; so how do that explain the longer time to
>> retrieve 1 datarow with a (unique) primary key with a nc index
>> (clustered index is for another field) comparing with the primary key
>> being the only field in the clustered index? For example, if my table
>> has 3 fields:
>> fileId - primary key
>> companyId
>> data
>> if i make a clustered index on fileid, it's faster to retrieve exactly
>> one row
>> if I make clustered index on companyId and nc index on fileId, it would
>> take longer to retrieve exactly one row
>> Both situations, I use this select statement
>> select *
>> from MyTable
>> where fileId = @.id
>> And yes, I look at the execution plan to determine how fast it is.
>> thanks!
>>
>> You're using SELECT *, which means "give me every field in the row", for
>> a specific fileID value. With a clustered index on fileID, the query
>> engine only has to hit the table one time, and is able to retrieve all of
>> the columns it needs from the same row in the clustered index.
>> With the other indexing arrangement, it takes two hits to get all of the
>> fields - the first hit is against the index, where it obtains fileID and
>> a bookmark. The second hit uses the bookmark to go back to the base
>> table to find the bookmarked record, from where it fetches the remaining
>> field values.
>> The second example takes longer because there is simply more work to be
>> done. This is a classic illustration as to why proper indexing is
>> crucial to database performance.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||Kalen,
I was referring to the leaf nodes in *non-clustered* index structure and the
intermediate node at the level right above the leaf nodes of *clustered*
index structure. From documentation, they seem to contain the same
information, so why referencing via a clustered index is faster than via
non-clustered index?
thanks!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%230q9V5DrGHA.3484@.TK2MSFTNGP04.phx.gbl...
> The leaf level rows and the node rows in the NC index are similar, but the
> number of rows is different.
> The leaf level contains a row locator for every single row in table. The
> upper levels only contain enough information to traverse down the index
> tree. You have to go to the leaf before you can then use the bookmark to
> find the actual data row.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Zen" <zen@.nononospam.com> wrote in message
> news:%23l6XapDrGHA.2464@.TK2MSFTNGP03.phx.gbl...
>> From what I understand, the leaves of nc index and the intermediate node
>> right above the leaf level of clustered index contain the same
>> information (row locator), from your explanation, they are not the same.
>> If they are the same, then the look up for entire data of the row should
>> be the same unless the traversing through the two index structures takes
>> different time. Could you please explain more? thanks!
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:ef7fioCrGHA.516@.TK2MSFTNGP05.phx.gbl...
>> Zen wrote:
>> Thanks for the response; so how do that explain the longer time to
>> retrieve 1 datarow with a (unique) primary key with a nc index
>> (clustered index is for another field) comparing with the primary key
>> being the only field in the clustered index? For example, if my table
>> has 3 fields:
>> fileId - primary key
>> companyId
>> data
>> if i make a clustered index on fileid, it's faster to retrieve exactly
>> one row
>> if I make clustered index on companyId and nc index on fileId, it would
>> take longer to retrieve exactly one row
>> Both situations, I use this select statement
>> select *
>> from MyTable
>> where fileId = @.id
>> And yes, I look at the execution plan to determine how fast it is.
>> thanks!
>>
>> You're using SELECT *, which means "give me every field in the row", for
>> a specific fileID value. With a clustered index on fileID, the query
>> engine only has to hit the table one time, and is able to retrieve all
>> of the columns it needs from the same row in the clustered index.
>> With the other indexing arrangement, it takes two hits to get all of the
>> fields - the first hit is against the index, where it obtains fileID and
>> a bookmark. The second hit uses the bookmark to go back to the base
>> table to find the bookmarked record, from where it fetches the remaining
>> field values.
>> The second example takes longer because there is simply more work to be
>> done. This is a classic illustration as to why proper indexing is
>> crucial to database performance.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>>
>|||They don't contain the same thing. The leaf level of a nc index has a
bookmark for every single row of data, but to get to the actual data, you
then have to traverse the clustered index, all levels. The node levels in
the clustered index contain page pointers to pages in the leaf level of the
clustered index, and those pages are the actual data, so you don't have to
go through any more levels. You're there.
And BTW, the execution plan does not tell you how fast a query is. It only
tells you the plan.
--
HTH
Kalen Delaney, SQL Server MVP
"Zen" <zen@.nononospam.com> wrote in message
news:u6KglTErGHA.3680@.TK2MSFTNGP02.phx.gbl...
> Kalen,
> I was referring to the leaf nodes in *non-clustered* index structure and
> the intermediate node at the level right above the leaf nodes of
> *clustered* index structure. From documentation, they seem to contain the
> same information, so why referencing via a clustered index is faster than
> via non-clustered index?
> thanks!
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%230q9V5DrGHA.3484@.TK2MSFTNGP04.phx.gbl...
>> The leaf level rows and the node rows in the NC index are similar, but
>> the number of rows is different.
>> The leaf level contains a row locator for every single row in table. The
>> upper levels only contain enough information to traverse down the index
>> tree. You have to go to the leaf before you can then use the bookmark to
>> find the actual data row.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Zen" <zen@.nononospam.com> wrote in message
>> news:%23l6XapDrGHA.2464@.TK2MSFTNGP03.phx.gbl...
>> From what I understand, the leaves of nc index and the intermediate node
>> right above the leaf level of clustered index contain the same
>> information (row locator), from your explanation, they are not the same.
>> If they are the same, then the look up for entire data of the row should
>> be the same unless the traversing through the two index structures takes
>> different time. Could you please explain more? thanks!
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:ef7fioCrGHA.516@.TK2MSFTNGP05.phx.gbl...
>> Zen wrote:
>> Thanks for the response; so how do that explain the longer time to
>> retrieve 1 datarow with a (unique) primary key with a nc index
>> (clustered index is for another field) comparing with the primary key
>> being the only field in the clustered index? For example, if my table
>> has 3 fields:
>> fileId - primary key
>> companyId
>> data
>> if i make a clustered index on fileid, it's faster to retrieve
>> exactly one row
>> if I make clustered index on companyId and nc index on fileId, it
>> would take longer to retrieve exactly one row
>> Both situations, I use this select statement
>> select *
>> from MyTable
>> where fileId = @.id
>> And yes, I look at the execution plan to determine how fast it is.
>> thanks!
>>
>> You're using SELECT *, which means "give me every field in the row",
>> for a specific fileID value. With a clustered index on fileID, the
>> query engine only has to hit the table one time, and is able to
>> retrieve all of the columns it needs from the same row in the clustered
>> index.
>> With the other indexing arrangement, it takes two hits to get all of
>> the fields - the first hit is against the index, where it obtains
>> fileID and a bookmark. The second hit uses the bookmark to go back to
>> the base table to find the bookmarked record, from where it fetches the
>> remaining field values.
>> The second example takes longer because there is simply more work to be
>> done. This is a classic illustration as to why proper indexing is
>> crucial to database performance.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>>
>>
>|||yes, that all makes sense now. BTW, I ran multiple statements at the same
time and see the % distribution in the execution plan, that way I can tell
how fast (relatively). Is there a better way? The execution time on query
analyzer status bar at the bottom only tells me in the seconds unit, that's
not sufficient to measure one select statement. Thanks!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OMMZQbFrGHA.4424@.TK2MSFTNGP05.phx.gbl...
> They don't contain the same thing. The leaf level of a nc index has a
> bookmark for every single row of data, but to get to the actual data, you
> then have to traverse the clustered index, all levels. The node levels in
> the clustered index contain page pointers to pages in the leaf level of
the
> clustered index, and those pages are the actual data, so you don't have to
> go through any more levels. You're there.
> And BTW, the execution plan does not tell you how fast a query is. It only
> tells you the plan.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Zen" <zen@.nononospam.com> wrote in message
> news:u6KglTErGHA.3680@.TK2MSFTNGP02.phx.gbl...
> > Kalen,
> >
> > I was referring to the leaf nodes in *non-clustered* index structure and
> > the intermediate node at the level right above the leaf nodes of
> > *clustered* index structure. From documentation, they seem to contain
the
> > same information, so why referencing via a clustered index is faster
than
> > via non-clustered index?
> >
> > thanks!
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:%230q9V5DrGHA.3484@.TK2MSFTNGP04.phx.gbl...
> >> The leaf level rows and the node rows in the NC index are similar, but
> >> the number of rows is different.
> >> The leaf level contains a row locator for every single row in table.
The
> >> upper levels only contain enough information to traverse down the index
> >> tree. You have to go to the leaf before you can then use the bookmark
to
> >> find the actual data row.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Zen" <zen@.nononospam.com> wrote in message
> >> news:%23l6XapDrGHA.2464@.TK2MSFTNGP03.phx.gbl...
> >> From what I understand, the leaves of nc index and the intermediate
node
> >> right above the leaf level of clustered index contain the same
> >> information (row locator), from your explanation, they are not the
same.
> >> If they are the same, then the look up for entire data of the row
should
> >> be the same unless the traversing through the two index structures
takes
> >> different time. Could you please explain more? thanks!
> >>
> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> news:ef7fioCrGHA.516@.TK2MSFTNGP05.phx.gbl...
> >> Zen wrote:
> >> Thanks for the response; so how do that explain the longer time to
> >> retrieve 1 datarow with a (unique) primary key with a nc index
> >> (clustered index is for another field) comparing with the primary
key
> >> being the only field in the clustered index? For example, if my
table
> >> has 3 fields:
> >>
> >> fileId - primary key
> >> companyId
> >> data
> >>
> >> if i make a clustered index on fileid, it's faster to retrieve
> >> exactly one row
> >> if I make clustered index on companyId and nc index on fileId, it
> >> would take longer to retrieve exactly one row
> >>
> >> Both situations, I use this select statement
> >>
> >> select *
> >> from MyTable
> >> where fileId = @.id
> >>
> >> And yes, I look at the execution plan to determine how fast it is.
> >> thanks!
> >>
> >>
> >> You're using SELECT *, which means "give me every field in the row",
> >> for a specific fileID value. With a clustered index on fileID, the
> >> query engine only has to hit the table one time, and is able to
> >> retrieve all of the columns it needs from the same row in the
clustered
> >> index.
> >>
> >> With the other indexing arrangement, it takes two hits to get all of
> >> the fields - the first hit is against the index, where it obtains
> >> fileID and a bookmark. The second hit uses the bookmark to go back
to
> >> the base table to find the bookmarked record, from where it fetches
the
> >> remaining field values.
> >>
> >> The second example takes longer because there is simply more work to
be
> >> done. This is a classic illustration as to why proper indexing is
> >> crucial to database performance.
> >>
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com
> >>
> >>
> >>
> >>
> >
> >
>|||The plan is just an estimate.
It can be useful to compare the relative cost of multiple plans in the same
batch, but it really isn't guaranteed.
Look at SET STATISTICS TIME ON
or
SELECT getdate() before you run the query, and again after, and compare the
difference
--
HTH
Kalen Delaney, SQL Server MVP
"Timm" <thinhnguyenkhoa@.hotmail.com> wrote in message
news:e6WjO0HrGHA.3680@.TK2MSFTNGP02.phx.gbl...
> yes, that all makes sense now. BTW, I ran multiple statements at the same
> time and see the % distribution in the execution plan, that way I can tell
> how fast (relatively). Is there a better way? The execution time on query
> analyzer status bar at the bottom only tells me in the seconds unit,
> that's
> not sufficient to measure one select statement. Thanks!
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OMMZQbFrGHA.4424@.TK2MSFTNGP05.phx.gbl...
>> They don't contain the same thing. The leaf level of a nc index has a
>> bookmark for every single row of data, but to get to the actual data, you
>> then have to traverse the clustered index, all levels. The node levels in
>> the clustered index contain page pointers to pages in the leaf level of
> the
>> clustered index, and those pages are the actual data, so you don't have
>> to
>> go through any more levels. You're there.
>> And BTW, the execution plan does not tell you how fast a query is. It
>> only
>> tells you the plan.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Zen" <zen@.nononospam.com> wrote in message
>> news:u6KglTErGHA.3680@.TK2MSFTNGP02.phx.gbl...
>> > Kalen,
>> >
>> > I was referring to the leaf nodes in *non-clustered* index structure
>> > and
>> > the intermediate node at the level right above the leaf nodes of
>> > *clustered* index structure. From documentation, they seem to contain
> the
>> > same information, so why referencing via a clustered index is faster
> than
>> > via non-clustered index?
>> >
>> > thanks!
>> >
>> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> > news:%230q9V5DrGHA.3484@.TK2MSFTNGP04.phx.gbl...
>> >> The leaf level rows and the node rows in the NC index are similar, but
>> >> the number of rows is different.
>> >> The leaf level contains a row locator for every single row in table.
> The
>> >> upper levels only contain enough information to traverse down the
>> >> index
>> >> tree. You have to go to the leaf before you can then use the bookmark
> to
>> >> find the actual data row.
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >>
>> >>
>> >> "Zen" <zen@.nononospam.com> wrote in message
>> >> news:%23l6XapDrGHA.2464@.TK2MSFTNGP03.phx.gbl...
>> >> From what I understand, the leaves of nc index and the intermediate
> node
>> >> right above the leaf level of clustered index contain the same
>> >> information (row locator), from your explanation, they are not the
> same.
>> >> If they are the same, then the look up for entire data of the row
> should
>> >> be the same unless the traversing through the two index structures
> takes
>> >> different time. Could you please explain more? thanks!
>> >>
>> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> news:ef7fioCrGHA.516@.TK2MSFTNGP05.phx.gbl...
>> >> Zen wrote:
>> >> Thanks for the response; so how do that explain the longer time to
>> >> retrieve 1 datarow with a (unique) primary key with a nc index
>> >> (clustered index is for another field) comparing with the primary
> key
>> >> being the only field in the clustered index? For example, if my
> table
>> >> has 3 fields:
>> >>
>> >> fileId - primary key
>> >> companyId
>> >> data
>> >>
>> >> if i make a clustered index on fileid, it's faster to retrieve
>> >> exactly one row
>> >> if I make clustered index on companyId and nc index on fileId, it
>> >> would take longer to retrieve exactly one row
>> >>
>> >> Both situations, I use this select statement
>> >>
>> >> select *
>> >> from MyTable
>> >> where fileId = @.id
>> >>
>> >> And yes, I look at the execution plan to determine how fast it is.
>> >> thanks!
>> >>
>> >>
>> >> You're using SELECT *, which means "give me every field in the row",
>> >> for a specific fileID value. With a clustered index on fileID, the
>> >> query engine only has to hit the table one time, and is able to
>> >> retrieve all of the columns it needs from the same row in the
> clustered
>> >> index.
>> >>
>> >> With the other indexing arrangement, it takes two hits to get all of
>> >> the fields - the first hit is against the index, where it obtains
>> >> fileID and a bookmark. The second hit uses the bookmark to go back
> to
>> >> the base table to find the bookmarked record, from where it fetches
> the
>> >> remaining field values.
>> >>
>> >> The second example takes longer because there is simply more work to
> be
>> >> done. This is a classic illustration as to why proper indexing is
>> >> crucial to database performance.
>> >>
>> >>
>> >>
>> >> --
>> >> Tracy McKibben
>> >> MCDBA
>> >> http://www.realsqlguy.com
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>