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 |
|
niranjankumark
Posting Yak Master
164 Posts |
 Posted - 2009-03-03 : 02:20:32
|
| Joining two tables ( huge data ) with index column. one table column is used as filter which is not having index .. if i include index over this column will it improve performance .Ex : select a.*from a, bwhere a.col1 = b.col1 ( indexed )and a.col2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) In this a.col2 distinct value is 15 . |
|
|
souLTower
Starting Member
39 Posts |
Posted - 2009-03-03 : 06:23:51
|
Indexes will always improve performance. If possible create a clustered index across all fields that you think will need it. Make sure to list the columns for the index in the logical order in which they will be searched. For example in a table representing people:lastName firstName ageSmith John 40Doe Mary 25 You would likely want the index based on the lastName, then firstName. However if the table is usually accessed searching for the age you may want to create the index with that column first.God Bless |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-06 : 10:06:29
|
quote:
Indexes will always improve performance.
Not always. Indexing a bit column, for instance, it usually pointless. Since there are only 2 allowable values for that type there's not enough distinction to be useful to the optimizer.quote:
If possible create a clustered index across all fields that you think will need it
No. Your clustered index key should be as narrow as possible, one column preferably and as small a datatype as applicable. All non-clustered index keys are built upon the clustering key. If that is a large value, or combination of values, each non-clustered index carries that data for each row. This can explode your database size very quickly and reduce performance.quote:
Make sure to list the columns for the index in the logical order in which they will be searched
Be sure to perform tests whenever you add an index to be sure the optimizer uses it and that it actually performs better. Don't just add it thinking it will help; it may not. |
|
|
|
|
|
|
|
|
Index on filter and join