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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-18 : 09:30:33
|
| Rajesh writes "I have a table with 3 columns say name, address, phoneno ( a kind of directory). Now a record may be searched. A value has to be supplied for any one of 3 colums & the remaining 2 columns value is to be obtained by a query. Now my question is(1)On what columns do i build indexes for optimum performance.My database is large. The phoneno is unique but the other 2 columns aren't. Should this index/indexes be clustered or non clustered.Please advice. Thanks in advance. " |
|
|
andre
Constraint Violating Yak Guru
259 Posts |
Posted - 2002-02-18 : 09:37:40
|
| I would make phoneno the primary key since it is unique for your table. If it is not an Identity field, you could make it a clustered index. As for the other fields, one question to consider regarding indexes is are there going to be a lot of inserts. If not and the table is searched often, adding a non-clustered index to each field would be worth it. If this table is updated often, an index might be more trouble than it's worth. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-18 : 10:35:12
|
| I agree with andre, you should add indexes to each column. Though I think that it would be better to put the clustered index on last name, first name instead of phone number (phone number should still be primary key). This is how printed phone directories are ordered/clustered. If name searches are most common, this will give you the best performance. |
|
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-02-18 : 10:48:08
|
| I agree that the clustered index should be on whatever field is used most for searches or links to other tables.To minimize the performance issues with inserts, set the fill factor low enough so that the pages aren't reshuffled with each insert. |
|
|
|
Spyder
SQLTeam Author
75 Posts |
Posted - 2002-02-18 : 13:26:04
|
If you have the option of adding another column then I might suggest adding a surrogate key named person_id (or something like that) and make it an INT IDENTITY and then make that the PRIMARY KEY with a CLUSTERED INDEX -- the reason I suggest this because we all know in the real world that phone numbers, names, and addresses are not necessarily unique. Then, put NON-CLUSTERED indexes on the name, phone number, and possibly the address columns.Unless you are doing range searches (e.g., WHERE yada BETWEEN 'foo' AND 'bar'), then a clustered index isn't going to help you much for data retrieval performance; however, with SQL Server 7.x and beyond, having a clustered index becomes desirable even if you don't plan on doing range searches. This is due to the newer 8k data page architecture and the use of IAMs, GAMs, and SGAMs for keeping track of page and extent allocations. To put it bluntly, big "heaps" (tables without clustered indexes) should be avoided with SQL Server versions 7.x and beyond. In addition, since 7.x and beyond have true row-level locking it is now less of a problem to have a monotonically-increasing key for a clustered index (which is something you would have wanted to avoid like the plague under SQL Server 6.x). |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-02-18 : 16:27:42
|
| I halfway agree with spyder. I think it would be a good idea to have a surrogate key due to the inherent non-uniqueness of first and last names. Even a composite key of first name, last name, and phone number may not be sufficient (for example John Smith [sr], and John Smith [jr] living in the same household and therefore with the same phone number). But I disagree that the clustered index should be on the surrogate. Rob is right, a directory should be clustered on the same clustering scheme of the physical representation (in this case probably lastname followed by firstname). m2c,Justin |
|
|
|
Spyder
SQLTeam Author
75 Posts |
Posted - 2002-02-18 : 17:33:03
|
I guess it REALLY depends on the insert/update and retrieval patterns. If most of the activity is retrievals and most of them are on name, and if the name provided is partial (e.g., WHERE name LIKE 'spyder%') then the clustered index on name would be a really good thing. Also, clustering on name would save physical space vis a vis creating a non-clustered index. However, if insert/updates are heavy then clustering on a character-based key could lead to lots of page splitting activity which could maybe possibly be bad (which is what I've been conditioned to believe due to the environment in --I'm still digging). So I guess I'm saying that I think I only halfway agree with myself, too -- thanks for setting me straight, guys! Edited by - spyder on 02/18/2002 20:48:52 |
|
|
|
|
|
|
|
|
Creating Indexes