of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question:
Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
EricThis is a multi-part message in MIME format.
--=_NextPart_000_00F4_01C3777E.0F0200C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Under the covers, it builds a "uniqueifier", so that all "keys" in the =clustered index are unique, even if you created it on a non-unique =column.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message =news:11269dcb.0309100520.6d6a4e1f@.posting.google.com...
In the case of a heap, I understand the leaf-level contains some sort
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question: Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
Eric
--=_NextPart_000_00F4_01C3777E.0F0200C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Under the covers, it builds a ="uniqueifier", so that all "keys" in the clustered index are unique, even if you created =it on a non-unique column.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eric Mamet"
--=_NextPart_000_00F4_01C3777E.0F0200C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0037_01C3782F.68E72280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
To add to Tom's comments, From memory this will add four bytes to the =size of your field for the subsequent non unique values. Plus an =addition two bytes because you now have a non fixed length column. As =you can see the extra overhead can be huge on something like an INT. If =there is another column you can add to the clustered index that can make =it unique, then I would consider this as it may actually use less space. = I.e. two INTs unique may use less space that one INT with low =selectivity.
-- Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
http://www.sqlfe.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:exfAK%235dDHA.568@.TK2MSFTNGP11.phx.gbl...
Under the covers, it builds a "uniqueifier", so that all "keys" in the =clustered index are unique, even if you created it on a non-unique =column.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message =news:11269dcb.0309100520.6d6a4e1f@.posting.google.com...
In the case of a heap, I understand the leaf-level contains some sort
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question: Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
Eric
--=_NextPart_000_0037_01C3782F.68E72280
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
To add to Tom's comments, From memory =this will add four bytes to the size of your field for the subsequent non unique =values. Plus an addition two bytes because you now have a non fixed length =column. As you can see the extra overhead can be huge on something like an =INT. If there is another column you can add to the clustered index that can =make it unique, then I would consider this as it may actually use less =space. I.e. two INTs unique may use less space that one INT with low selectivity.
-- Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.comhttp://www.sqlfe.com">http://www.sqlfe.com
"Tom Moreau"
Under the covers, it builds a ="uniqueifier", so that all "keys" in the clustered index are unique, even if you created =it on a non-unique column.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eric Mamet"
--=_NextPart_000_0037_01C3782F.68E72280--|||This question bothered me and it makes much more sense now.
Thanks very much to both of you.
