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 |
|
dewacorp.alliances
452 Posts |
 Posted - 2009-02-10 : 02:06:17
|
| Hi thereWith creating indexing, does make it different between creating 1 indexing with 5 assigned columns or 5 indexing with 1 assigned column. Someone mentioned that it recommend to use each indexing assigned into 1 or 2 columns? Is this true?Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-10 : 02:37:11
|
It depends on your application.The best scenario is to have a covering index. SELECTs will be fast on the cost of speed for UPDATs, DELETEs and INSERTs.A general rule of thumb is to create index on columns used for filtering, such as JOIN conditions and WHERE clauses.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2009-02-10 : 05:05:28
|
quote:
The best scenario is to have a covering index. SELECTs will be fast on the cost of speed for UPDATs, DELETEs and INSERTs.A general rule of thumb is to create index on columns used for filtering, such as JOIN conditions and WHERE clauses.
I usually do the same thing: creating index on columns for filtering (JOIN and WHERE).But what do you mean by "The best scenario is to have a covering index. SELECTs will be fast on the cost of speed for UPDATs, DELETEs and INSERTs." ?This application has few table that basicaly it's like "mini datawarehouse" and few transactional tables. The one that I ask is in relation with "mini datawarehouse" which is heavily used for reporting and once data get insert, never change. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-10 : 10:02:33
|
| It depends on the selectivity of columns.Covering index can provide benefit when you have Bookmark looup in execution plan.In other side, It consumes storage and difficult to maintain for High OLTP operation. Use Include with Non-key columns for index in SQL 2005.It doesn't take overhead to maintain it. |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-02-11 : 23:05:51
|
| Instead of guessing. I would collect a trace for my workload and use them in DTA to verify if my assumptions and DTA recommendations are same. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-12 : 07:05:40
|
quote:
Originally posted by saurabhsrivastava
Instead of guessing. I would collect a trace for my workload and use them in DTA to verify if my assumptions and DTA recommendations are same.
Sometimes DTA can suggest to create and drop unnecessary indexes. |
|
|
|
|
|
|
Performance Indexing