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 - 2007-10-03 : 03:50:54
|
| Dear Experts,I have few questions on SQL Server indexes.Example : There is a table Table1 with columns col1,col2,col3,col4,col5.Q#1 There are below non clustered indexes on the table Table11. IDX1 ON (Col1, Col2, Col3)2. IDX2 ON (Col2, Col3)3. IDX3 ON (Col2)4. IDX4 ON (Col3, Col4, Col5)5. IDX5 ON (col5)6. IDX_PK ON (col1) is Primary key and IDENTITY.Does these indexes makes sense ? That is , do we need IDX3 when IDX2 could be used for searching col2 ? do we need IDX5 when IDX4 could be used for searching col5 ?Q#2 In case if I need to make the fill factor to 90 % , should I do it on all the indexes or only on the PK IDX_PK ?Q#3 Assume this table is write intensive. As the column col1 is Primary Key with IDENTITY property , should I still have the fill factor made as 90% changed from the default 100%. That is, does the fill factor comes to help even if there is an IDENTITY column.Thanks for your time & responses in advance.Hariarul |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-03 : 04:25:37
|
Homework?We can't tell if all indexes are good or not.It depends on the type of queries being run against the table."do we need IDX3 when IDX2 could be used for searching col2 ?"Seems reduntant"do we need IDX5 when IDX4 could be used for searching col5 ?"Yes.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2007-10-03 : 06:30:10
|
| As Peso has already said , we would need to see the queries. Some other things to keep in mind: 1)Don't have to many indices 2)develop your indices around the queries. 3)the decision the optimizer makes is dependant , amongst other things on selectivity of the dataJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL |
|
|
|
|
|
|
|
|
Few questions on indexes.