Cluster ratio? Cluster factor?

MS SQL Server 2000
let's say there is a table ORDERS with a clustered index on
(order_date, some other column). Also there is a non-clustered index on
shipment_date. Since most orders are shipped within 3 business days,
the data is stored almost ordered by shipment_date.
Most rows for the same shipment date are stored on adjacent data pages.
There is another index on zipcode, which does not correlate with order
date at all. Is there anything I can read from system views to tell the
difference? In Oracle/DB2 I can read cluster factor/cluster ratio.
TIAHi
You probably need DBCC SHOWCONTIG, you can check out the documentation in
Books online. If you want more information on this sort of thing you may als
o
want to read "Inside SQL Server 2000" by Kalen Delaney ISBN 0-7356-0998-5 an
d
Ken Henderson's "The Guru's guide to SQL Server Architecture and
Internals" ISBN 0-201-7004706
John
"ford_desperado@.yahoo.com" wrote:

> MS SQL Server 2000
> let's say there is a table ORDERS with a clustered index on
> (order_date, some other column). Also there is a non-clustered index on
> shipment_date. Since most orders are shipped within 3 business days,
> the data is stored almost ordered by shipment_date.
> Most rows for the same shipment date are stored on adjacent data pages.
> There is another index on zipcode, which does not correlate with order
> date at all. Is there anything I can read from system views to tell the
> difference? In Oracle/DB2 I can read cluster factor/cluster ratio.
> TIA
>

Clus. Index keys

Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> > Hi,
> >
> > I read in one sql document tht--If the table have a clustered index, the
> > bookmarks of all non-clustered indexes will have clustering keys for each
> > row, and physically moving a row on disk would of course not have any
> > effect
> > on these.
> >
> > My question is wht is getting stored in clustered index keys which is
> > independent of data row location(as it can be inferred from above tht it
> > doesnt affect non clus. index bookmarks).
> >
> > Thanks in advance.
> > Manu Jaidka
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>> Manu,
>> What is stored in the non-clustered key is the value of the associated
>> clustered key.
>> Therefore, searching a non-clustered index results in the clustered index
>> key, after which the clustered index is searched to find the row.
>> One side effect of this is that the size of the non-clustered index is
>> affected by the size of the clustered index, so keeping the cluster as
>> small
>> as is reasonable is a good idea.
>> RLF
>>
>> "manu" <manu@.discussions.microsoft.com> wrote in message
>> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>> > Hi,
>> >
>> > I read in one sql document tht--If the table have a clustered index,
>> > the
>> > bookmarks of all non-clustered indexes will have clustering keys for
>> > each
>> > row, and physically moving a row on disk would of course not have any
>> > effect
>> > on these.
>> >
>> > My question is wht is getting stored in clustered index keys which is
>> > independent of data row location(as it can be inferred from above tht
>> > it
>> > doesnt affect non clus. index bookmarks).
>> >
>> > Thanks in advance.
>> > Manu Jaidka
>>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David

Clus. Index keys

Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:

> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as sma
ll
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David

Clus. Index keys

Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu Jaidka
Manu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:

> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>
|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David