Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tribune
Posting Yak Master
105 Posts |
 Posted - 2004-03-05 : 17:36:12
|
| I have several tables in my databse that use clustered indexes in the following ways....for example:Users-------UserID (PK) (Clustered index)FirstNameLastNameAlmost all queries on the users table will be either direct select X, Y, Z from users where UserID = N, or joins on the UserID.Another scenario in probably 50 tables in the database looks like this:Units-----UnitID (PK) (Clustered index)UserID (FK)UnitNameUnitAmountQueries on this table are almost always in the form of joins on the users table. For example "select X, Y, Z from Units Join Users on Units.UserID = Users.UserIDThanks |
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-05 : 17:43:31
|
| I'd say this is a nasty behaviour of SQL Server, when you define a PrimaryKey that it is always made the clustered index unless you explicitely state otherwise. As you can only have one clustered index, you certainly will use it for something better. Anyway, it is better than not having a clustered index at all.--Frank |
 |
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-03-05 : 18:06:09
|
| Ah I forgot to mention - all these IDs are identities, so even though they are all clustered, page splits shouldnt occur since the data is entered sequentially onto the ends of the last page for the table correct? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-06 : 00:05:03
|
| That's true, but that doesn't mean that ALL of your primary keys should be clustered. It's certainly not appropriate for every table (neither are identity columns as primary keys) Tables with few rows, or low insert activity, are better to cluster on something other than the primary key. Page splits should be avoided but you can't eliminate them entirely, and it's not worth compromising general query performance in that pursuit. The best candidates for clustered indexes are columns that are searched or joined frequently, and contain a large number of distinct, but not necessarily unique, values. A zip code column in a shipping address table, for example, can be an excellent choice. Date columns are also good, as are last names. GUIDs are a bad choice for clustering, ESPECIALLY if they are the primary key of a table (avoid this at all costs, you'll probably set a page splitting record during an INSERT) |
|
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-08 : 12:19:26
|
| So to answer tribune's question, and taking both Franks and rob's comments into account, the user table is most frequenly queried and joined on the userid. This makes the most sense for a clustered index.The units table, from what tribune says is almost always queried and joined on userid. So for this one, userid would also make a good clustered index from a join perspective. From an insert perspective thats a bit different as the clustered index pages would be stored in userid order, not the identity order. But this shouldnt really cause a big problem, although you may want to watch the table/index for fragmentation. If you have a database where you can play, take a commonly used query, get the test plan. Then reindex the units table to put the cluster on userid and get that test plan. Compare to see which one is best and that should be the one you use. Chris |
|
|
|
|
|
|
|
|
Is this an appropriate use of clustered indexes?