December 17, 2003 at 7:42 am
I Know That Clustered Index Caries The Data At the Leef Level
I have A Table With The Following Indexes
Both are UNIQUE AND not Clustured
I want to make One Of them as Clustered
Which One Is Beter (Both are Dectated by a Legacy Application and cannot be changed)
Index One (PolicyOwner,ChildName)
Index Tow (BirthDate,PolicyOwner,ChildName)
Range Scaning is Very Rare and most Selects
are for a unique records.
December 17, 2003 at 8:17 am
It depends on how your application query the table.
I would create clustered index on
Queries that return large result sets
Columns used in order by or group by queries
Columns used in table joins
Columns used in lots of queries
December 29, 2003 at 12:54 am
To add to what Allen said, if you are having mostly inserts having the clustered index on a sequential column can give you a hot spot for inserts and help increase your performance for inserts, also, watch out having composite clustered indexes as they bloat all non-clustered indexes.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
December 29, 2003 at 8:10 am
If you really have only single row record retrievals (no range select, no sort)
then it doeasn't make too many sense to create a clustered index.
Non clustered index will remain faster especially on inserts and selects where normally a nonclusterd index would be selected.
But if you still want to create a clustered index then check your application (especially the where clauses) in the profiler and also the data distribution.
If there are some queries where you have just partial index coverages (where you do not qualify all the columns of the index) then this is a good candidate for the clustered index.
Bye
Gabor
Bye
Gabor
December 29, 2003 at 10:40 am
Clustered index - low selectivity columns, those with relatively few different values or ranges, like dates.
Non clustered, those that have high selectivity, like identities, where you usually return one column.
Due to page splitting and performance, you should ALWAYS have a clustered index somewhere.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply