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 |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2006-12-21 : 05:49:29
|
| Hi All,Is there any specific way to determine the columns on which index should be created for an application ?May be a template which can be used for analysing the column usage and proposing indexes.I know there wont have any Generic tool for this purpose. Some tools or templates to ease the analysis would help.Thanks,HHA |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-12-21 : 05:52:27
|
| The general rule is that the columns which are most frequently used in th criteria expression (WHERE clause) should have indexes. Have you tried using Index Tuning Wizard?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-21 : 05:52:49
|
| It depends on the usage of the table.If few INSERTs but many SELECTs are made, a covering index is the better choice.Query Analyser has a INDEX TUNING WIZARD.Peter LarssonHelsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-21 : 06:06:34
|
| Another rule to remember is the granularity of the data being indexed.Indexing a member table over column "Gender" (50% male and 50% female) is not that efficient.Indexing their age might be an improvement if such information is needed in the queries.Peter LarssonHelsingborg, Sweden |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2006-12-21 : 06:17:05
|
| Thanks Peso & Harsh !!! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-21 : 07:15:25
|
| Make sure BOTH sides of FKs and JOINs are indexed - but watch out for poor selectivity, you may gain nothing for creating indexes on them.Keep the statistics up to date - otherwise you may check a query plan and scratch your head for hours as to why an index is not being used, only to find the Stats were stale!Kristen |
|
|
|
|
|
|
Determine columns for indexing.