 | |
#41
 
| |||
| |||
|
Re: index -
07-28-2008
, 07:00 PM
|
"Brian Selzer" <brian (AT) selzer-software (DOT) com> wrote in message news:%Vpjk.34162$ZE5.24396 (AT) nlpi061 (DOT) nbdc.sbc.com... "Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote in message news:488de63f$0$4041$9a566e8b (AT) news (DOT) aliant.net... aarklon (AT) gmail (DOT) com wrote: Hi all, I read the following in a book 1) we can have several non clustered indexes on a table , but we can have only one clustered index per table 2) index on the primary key can be clustered or non clustered now my question is 1) to what extent these statements are true ? To the extent the statements accurately describe the physical implementation of a particular dbms, they are true. Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. Vendors typically assUme nobody would ever want to incur that cost. This is bunk. Badour is an idiot. Where there is a clustered index, that index /is/ the table--that is, the table is physically stored using whatever data structure is in use for indexes--probably b-trees. |
If one stores the data twice, one can have two clustered indexes. Hence
"duplicating the data" and "incurs a cost"...
<snip>
#42
| |||
| |||
|
|
"Brian Selzer" <brian (AT) selzer-software (DOT) com> wrote in message news:%Vpjk.34162$ZE5.24396 (AT) nlpi061 (DOT) nbdc.sbc.com... "Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote in message news:488de63f$0$4041$9a566e8b (AT) news (DOT) aliant.net... aarklon (AT) gmail (DOT) com wrote: Hi all, I read the following in a book 1) we can have several non clustered indexes on a table , but we can have only one clustered index per table 2) index on the primary key can be clustered or non clustered now my question is 1) to what extent these statements are true ? To the extent the statements accurately describe the physical implementation of a particular dbms, they are true. Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. Vendors typically assUme nobody would ever want to incur that cost. This is bunk. Badour is an idiot. Where there is a clustered index, that index /is/ the table--that is, the table is physically stored using whatever data structure is in use for indexes--probably b-trees. |
If one stores the data twice, one can have two clustered indexes. Hence
"duplicating the data" and "incurs a cost"...
<snip>
#43
| |||
| |||
|
|
"Brian Selzer" <brian (AT) selzer-software (DOT) com> wrote in message news:%Vpjk.34162$ZE5.24396 (AT) nlpi061 (DOT) nbdc.sbc.com... "Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote in message news:488de63f$0$4041$9a566e8b (AT) news (DOT) aliant.net... aarklon (AT) gmail (DOT) com wrote: Hi all, I read the following in a book 1) we can have several non clustered indexes on a table , but we can have only one clustered index per table 2) index on the primary key can be clustered or non clustered now my question is 1) to what extent these statements are true ? To the extent the statements accurately describe the physical implementation of a particular dbms, they are true. Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. Vendors typically assUme nobody would ever want to incur that cost. This is bunk. Badour is an idiot. Where there is a clustered index, that index /is/ the table--that is, the table is physically stored using whatever data structure is in use for indexes--probably b-trees. |
If one stores the data twice, one can have two clustered indexes. Hence
"duplicating the data" and "incurs a cost"...
<snip>
#44
| |||
| |||
|
|
"Brian Selzer" <brian (AT) selzer-software (DOT) com> wrote in message news:%Vpjk.34162$ZE5.24396 (AT) nlpi061 (DOT) nbdc.sbc.com... "Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote in message news:488de63f$0$4041$9a566e8b (AT) news (DOT) aliant.net... aarklon (AT) gmail (DOT) com wrote: Hi all, I read the following in a book 1) we can have several non clustered indexes on a table , but we can have only one clustered index per table 2) index on the primary key can be clustered or non clustered now my question is 1) to what extent these statements are true ? To the extent the statements accurately describe the physical implementation of a particular dbms, they are true. Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. Vendors typically assUme nobody would ever want to incur that cost. This is bunk. Badour is an idiot. Where there is a clustered index, that index /is/ the table--that is, the table is physically stored using whatever data structure is in use for indexes--probably b-trees. |
If one stores the data twice, one can have two clustered indexes. Hence
"duplicating the data" and "incurs a cost"...
<snip>
#45
| |||
| |||
|
|
"Brian Selzer" <brian (AT) selzer-software (DOT) com> wrote in message news:%Vpjk.34162$ZE5.24396 (AT) nlpi061 (DOT) nbdc.sbc.com... "Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote in message news:488de63f$0$4041$9a566e8b (AT) news (DOT) aliant.net... aarklon (AT) gmail (DOT) com wrote: Hi all, I read the following in a book 1) we can have several non clustered indexes on a table , but we can have only one clustered index per table 2) index on the primary key can be clustered or non clustered now my question is 1) to what extent these statements are true ? To the extent the statements accurately describe the physical implementation of a particular dbms, they are true. Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. Vendors typically assUme nobody would ever want to incur that cost. This is bunk. Badour is an idiot. Where there is a clustered index, that index /is/ the table--that is, the table is physically stored using whatever data structure is in use for indexes--probably b-trees. |
If one stores the data twice, one can have two clustered indexes. Hence
"duplicating the data" and "incurs a cost"...
<snip>
#46
| |||
| |||
|
|
"Brian Selzer" <brian (AT) selzer-software (DOT) com> wrote in message news:%Vpjk.34162$ZE5.24396 (AT) nlpi061 (DOT) nbdc.sbc.com... "Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote in message news:488de63f$0$4041$9a566e8b (AT) news (DOT) aliant.net... aarklon (AT) gmail (DOT) com wrote: Hi all, I read the following in a book 1) we can have several non clustered indexes on a table , but we can have only one clustered index per table 2) index on the primary key can be clustered or non clustered now my question is 1) to what extent these statements are true ? To the extent the statements accurately describe the physical implementation of a particular dbms, they are true. Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. Vendors typically assUme nobody would ever want to incur that cost. This is bunk. Badour is an idiot. Where there is a clustered index, that index /is/ the table--that is, the table is physically stored using whatever data structure is in use for indexes--probably b-trees. |
If one stores the data twice, one can have two clustered indexes. Hence
"duplicating the data" and "incurs a cost"...
<snip>
#47
| |||
| |||
|
|
"Brian Selzer" <brian (AT) selzer-software (DOT) com> wrote in message news:%Vpjk.34162$ZE5.24396 (AT) nlpi061 (DOT) nbdc.sbc.com... "Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote in message news:488de63f$0$4041$9a566e8b (AT) news (DOT) aliant.net... aarklon (AT) gmail (DOT) com wrote: Hi all, I read the following in a book 1) we can have several non clustered indexes on a table , but we can have only one clustered index per table 2) index on the primary key can be clustered or non clustered now my question is 1) to what extent these statements are true ? To the extent the statements accurately describe the physical implementation of a particular dbms, they are true. Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. Vendors typically assUme nobody would ever want to incur that cost. This is bunk. Badour is an idiot. Where there is a clustered index, that index /is/ the table--that is, the table is physically stored using whatever data structure is in use for indexes--probably b-trees. A table that does not have a clustered index, on the other hand, is physically stored as a heap. The leaf nodes of a clustered index /are/ the rows of the table. The leaf nodes of a non-clustered index are either pointers to rows on the heap (where there is no clustered index) or clustered index keys (along with a uniquifier [I didn't coin the term, so don't blame me] if the clustered index isn't also a unique index). Although it is possible to have multiple indexes that cover the entire heading, that is not the same thing as having multiple clustered indexes. Though I agree with you, you are not a very nice person. Why the invective re: Badour? |
already turned the other cheek--more than once, I might add. Even though I
know it isn't very Christian, I have progressed past the "You don't cast
pearls after swine." stage to the point that I can't help but illuminate
every flaw in anything he posts. Bottom line: he earned that invective, and
I am more than happy to pay.
#48
| |||
| |||
|
|
"Brian Selzer" <brian (AT) selzer-software (DOT) com> wrote in message news:%Vpjk.34162$ZE5.24396 (AT) nlpi061 (DOT) nbdc.sbc.com... "Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote in message news:488de63f$0$4041$9a566e8b (AT) news (DOT) aliant.net... aarklon (AT) gmail (DOT) com wrote: Hi all, I read the following in a book 1) we can have several non clustered indexes on a table , but we can have only one clustered index per table 2) index on the primary key can be clustered or non clustered now my question is 1) to what extent these statements are true ? To the extent the statements accurately describe the physical implementation of a particular dbms, they are true. Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. Vendors typically assUme nobody would ever want to incur that cost. This is bunk. Badour is an idiot. Where there is a clustered index, that index /is/ the table--that is, the table is physically stored using whatever data structure is in use for indexes--probably b-trees. A table that does not have a clustered index, on the other hand, is physically stored as a heap. The leaf nodes of a clustered index /are/ the rows of the table. The leaf nodes of a non-clustered index are either pointers to rows on the heap (where there is no clustered index) or clustered index keys (along with a uniquifier [I didn't coin the term, so don't blame me] if the clustered index isn't also a unique index). Although it is possible to have multiple indexes that cover the entire heading, that is not the same thing as having multiple clustered indexes. Though I agree with you, you are not a very nice person. Why the invective re: Badour? |
already turned the other cheek--more than once, I might add. Even though I
know it isn't very Christian, I have progressed past the "You don't cast
pearls after swine." stage to the point that I can't help but illuminate
every flaw in anything he posts. Bottom line: he earned that invective, and
I am more than happy to pay.
#49
| |||
| |||
|
|
"Brian Selzer" <brian (AT) selzer-software (DOT) com> wrote in message news:%Vpjk.34162$ZE5.24396 (AT) nlpi061 (DOT) nbdc.sbc.com... "Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote in message news:488de63f$0$4041$9a566e8b (AT) news (DOT) aliant.net... aarklon (AT) gmail (DOT) com wrote: Hi all, I read the following in a book 1) we can have several non clustered indexes on a table , but we can have only one clustered index per table 2) index on the primary key can be clustered or non clustered now my question is 1) to what extent these statements are true ? To the extent the statements accurately describe the physical implementation of a particular dbms, they are true. Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. Vendors typically assUme nobody would ever want to incur that cost. This is bunk. Badour is an idiot. Where there is a clustered index, that index /is/ the table--that is, the table is physically stored using whatever data structure is in use for indexes--probably b-trees. A table that does not have a clustered index, on the other hand, is physically stored as a heap. The leaf nodes of a clustered index /are/ the rows of the table. The leaf nodes of a non-clustered index are either pointers to rows on the heap (where there is no clustered index) or clustered index keys (along with a uniquifier [I didn't coin the term, so don't blame me] if the clustered index isn't also a unique index). Although it is possible to have multiple indexes that cover the entire heading, that is not the same thing as having multiple clustered indexes. Though I agree with you, you are not a very nice person. Why the invective re: Badour? |
already turned the other cheek--more than once, I might add. Even though I
know it isn't very Christian, I have progressed past the "You don't cast
pearls after swine." stage to the point that I can't help but illuminate
every flaw in anything he posts. Bottom line: he earned that invective, and
I am more than happy to pay.
#50
| |||
| |||
|
|
"Brian Selzer" <brian (AT) selzer-software (DOT) com> wrote in message news:%Vpjk.34162$ZE5.24396 (AT) nlpi061 (DOT) nbdc.sbc.com... "Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote in message news:488de63f$0$4041$9a566e8b (AT) news (DOT) aliant.net... aarklon (AT) gmail (DOT) com wrote: Hi all, I read the following in a book 1) we can have several non clustered indexes on a table , but we can have only one clustered index per table 2) index on the primary key can be clustered or non clustered now my question is 1) to what extent these statements are true ? To the extent the statements accurately describe the physical implementation of a particular dbms, they are true. Theoretically, one can have multiple clustered indexes at the cost of duplicating the data, which of course incurs a cost. Vendors typically assUme nobody would ever want to incur that cost. This is bunk. Badour is an idiot. Where there is a clustered index, that index /is/ the table--that is, the table is physically stored using whatever data structure is in use for indexes--probably b-trees. A table that does not have a clustered index, on the other hand, is physically stored as a heap. The leaf nodes of a clustered index /are/ the rows of the table. The leaf nodes of a non-clustered index are either pointers to rows on the heap (where there is no clustered index) or clustered index keys (along with a uniquifier [I didn't coin the term, so don't blame me] if the clustered index isn't also a unique index). Although it is possible to have multiple indexes that cover the entire heading, that is not the same thing as having multiple clustered indexes. Though I agree with you, you are not a very nice person. Why the invective re: Badour? |
already turned the other cheek--more than once, I might add. Even though I
know it isn't very Christian, I have progressed past the "You don't cast
pearls after swine." stage to the point that I can't help but illuminate
every flaw in anything he posts. Bottom line: he earned that invective, and
I am more than happy to pay.
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.