clustered index more than on one field
-
Has someone experience applying clustered index more than on one field? I think it is wrong action. May be it could work with small database. My observation showed such estimation: same table same number of records. Execution query to select all fields from same table without having clustered index on single column is 8sec (650243). In other case I have apply clustered index on primary and foreign keys. Execution last about 12 sec.; basically it decreased performance. So now is a question: if I can apply non clustered index on couple fields, what is the best practice to apply clustered index. Whether it should be only single field to sort data in a table? :confused:
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
-
Has someone experience applying clustered index more than on one field? I think it is wrong action. May be it could work with small database. My observation showed such estimation: same table same number of records. Execution query to select all fields from same table without having clustered index on single column is 8sec (650243). In other case I have apply clustered index on primary and foreign keys. Execution last about 12 sec.; basically it decreased performance. So now is a question: if I can apply non clustered index on couple fields, what is the best practice to apply clustered index. Whether it should be only single field to sort data in a table? :confused:
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
By definition, there can only ever be a single clustered index on a table - it is the clustered index that sets the physical order of records. Usually, the clustered index would be on a single field, and generally that will be a key field that auto-numbers. The performance hit for clustered indices is more related to inserts and updates on your data, as if you insert/update a record that is towards the start of the index, and make it fall towards the end, nearly the whole table is physically reordered on the disk to accomodate this.
Reminiscing just isn't what it used to be!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
By definition, there can only ever be a single clustered index on a table - it is the clustered index that sets the physical order of records. Usually, the clustered index would be on a single field, and generally that will be a key field that auto-numbers. The performance hit for clustered indices is more related to inserts and updates on your data, as if you insert/update a record that is towards the start of the index, and make it fall towards the end, nearly the whole table is physically reordered on the disk to accomodate this.
Reminiscing just isn't what it used to be!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!