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 |
|
dambo5
Starting Member
12 Posts |
 Posted - 2012-09-20 : 10:43:47
|
| Previously created this thread by mistake on other forum :-(I have been reading a lot that creating a single column index on foreign key columns, usually improves performance and I have few questions regarding it.I have 2 central and mostly heavy used tables in the db, one of them 1M rows the other 6M rows, both of them containing more than 30 columns. One of them has > 10 foreign key constraints applied. On the other table there are no constraints although there are around 10 foreign key columns (logically). The referential constraint is handled by the application. 1. If I create indexes on all of this FK I am afraid that the inserts and updates will become extremely slow? By the way the tables that hold the PKs are mostly registers which means that very rear updated and almost never deleted. If the update does not touch the PK columns that means that the FK table is not scanned right??? The registers usually have clustered index on the PK column (default).2. On the other hand a lot of these columns are used for massive JOINS (left outer) so would I benefit from creating indexes on all of it? Would the benefit of all the joins prevail over the slow inserts?3. I have total of 750 FKs through the DB that do not have indexes on it. I guess that on a tables which are less than 1000 rows it does not make sense (difference) if there is an index on it or no.Thank you.A. |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2012-09-20 : 11:41:11
|
| Indexing the Fk columns being used in joins and where clause will really improve query performance on huge tables. |
 |
|
|
|
|
|
Index on Foreign Keys (Performance)