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 |
|
Tim
Starting Member
392 Posts |
 Posted - 2003-08-27 : 22:22:41
|
| I have two tables with identity primary keys. I migrate the primary key from table1 to table2 as a foreign key.Question is, should I index the foreign key in table2?I think so as the index will be available for joining the two tables. Some here think it is not necessary.I know that SQL Server may choose not to use the index if stats indicate a better way, but at least the index can be considered if it is there.Any advice pls? |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-08-27 : 22:59:44
|
| Why not try both in QA? See what gives the best performance. Don't forget to free the proc cache after running the query w/out the index, so it doesn't skew results once you have the index.Sarah Berger MCSD |
 |
|
|
Tim
Starting Member
392 Posts |
Posted - 2003-08-28 : 00:18:06
|
| well I could, but it would only make the case for the specific example I showed, and even then might not be supportive. I am trying to promote it as a good practice in general terms. Standards review type stuff.Any more comment? |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-08-28 : 02:26:01
|
quote:
Originally posted by TimQuestion is, should I index the foreign key in table2?
Yes. All foreign keys should be indexed, this can improve performance when you perform a join operation on these columns (as you said in your post). In fact, indexing any keys used in join operations would be a best practice, even if a foreign key constraint is not used.Don't overindex your tables though. Too many indexes will hurt your performance on inserts and updates - which involve table locking. Too much activity on a table can have a cascading effect on performance, especially if it has too many indexes.-ec |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2003-08-28 : 03:15:52
|
| that's the firs eyechart I was ever able to read :)do you know of any authoritative text/opinion on this (besides your own of course!) |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2003-08-28 : 03:50:33
|
Hi Owais,quote:
Make it idiot proof and someone will make a better idiot
just came across your signatur, and would like to add'It is impossible to make anything foolproof because fools are so ingenious.'  Cheers,Frank |
|
|
|
|
|
|
|
|
index question