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 |
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
 Posted - 2003-07-10 : 13:05:04
|
| When is it an advantage to combine fields in an index? Let's say I have 2 char fields that I am constantly querying on. Is there an advantage to setting up a single index that combines the two fields and then combining these in a WHERE clause?Or, should the combining of fields in an Index be used only for Uniqueness control? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-10 : 14:45:37
|
| Ken,I'm not sure what you mean by combine...If you mean composite, yesif you mean concate then noFor example if you have SELECT col1, col2 FROM myTable Where col1 = 'a' AND col2 = 'B'If there was an index (and doesn't have to be unique) it will on use the index (fast)However, if you hadSELECT col1, col2, col3 FROM myTable Where col1 = 'a' AND col2 = 'B'It would still use the index, but the would have still have to go to the data page to get the other value (col3).But lets say the col3 is a column about gender. Because of it's low cardinality (it only has 2 values) putting an index on it is useless (or more than useless?)BUT!If you frequently need that column, you might want to "overload" your index and add col3 to it. That way it will only go to the index.I'm sure there is a point of diminishing retrun on this, but it's all about monitoring to see which is faster.I don't know, what does the brain trust think?Brett8-) |
 |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-07-10 : 16:09:18
|
| Brett:I think I led you astray...Sorry about my lack of proper terminology.What I mean by combine is adding multiple columns to one index in the table designer, which from what I read in help is a composite index.I thought that when I added multiple columns to one index that it would be a single index "Key" which would combine the fields internally. That way I could setup an unique index which would contain 2 fields, and the uniqueness would be based on both fields, not just one. Am I wrong?My original question was if I didSelect * FROM MyTable WHERE ColumnA = x and ColumnB = yshould I have a single composite index that contains both columns, or should I have 2 separate indexes, one for ColumnA and one for ColumnB. From what I just read on Composite Indexes I think I should have one composite index containing both columns. Is this correct?Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-10 : 16:24:00
|
quote:
I thought that when I added multiple columns to one index that it would be a single index "Key" which would combine the fields internally. That way I could setup an unique index which would contain 2 fields, and the uniqueness would be based on both fields, not just one. Am I wrong?
Uniqueness (if the index is unique that is) is based upon all columns in the index. So yes you are correct, it is based on both fields in your example.quote:
Select * FROM MyTable WHERE ColumnA = x and ColumnB = y should I have a single composite index that contains both columns, or should I have 2 separate indexes, one for ColumnA and one for ColumnB. From what I just read on Composite Indexes I think I should have one composite index containing both columns. Is this correct?
Yes you are correct. But change your select statement so that it has explicit column names instead of select *. For example:SELECT ColumnA, ColumnB, ColumnC --etc... (but only the columns that you need)FROM MyTableWHERE ColumnA = x and ColumnB = y Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-10 : 16:27:43
|
Well, first off I don't recommend designing tables that way. It just adds to the confusion. Because when you say:quote:
adding multiple columns to one index in the table designer
Are you going in to "Manage Indexes and Keys"?That's the second to last button on the toolbar.If your just adding Access looking "keys" to the table design, then you're creating a unique primary index (which may not be what you want).You can create non unique indexes. Just remeber the more indexes the slower thing will get for inserts (they not only have to update the table, but all the indexes as well).quote:
I thought that when I added multiple columns to one index that it would be a single index "Key" which would combine the fields internally. That way I could setup an unique index which would contain 2 fields, and the uniqueness would be based on both fields, not just one. Am I wrong?
No you're right (if your doing it right from the table view. That'sa altering the Primary key. Be careful if you have data in there, and what you set. It may give you a hard time if you have non unique data for the columns you are now calling unique.quote:
should I have a single composite index that contains both columns, or should I have 2 separate indexes, one for ColumnA and one for ColumnB. From what I just read on Composite Indexes I think I should have one composite index containing both columns. Is this correct?
You are correct sir! One composite index, unless of course the column that is secondary in the composite needs to be part of a predicate by itself. Then (I think) the index is not used.Script your table and look at the ddl.Brett8-) |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-11 : 03:31:23
|
quote:
You are correct sir! One composite index, unless of course the column that is secondary in the composite needs to be part of a predicate by itself. Then (I think) the index is not used.
In that case I notice it does an index scan instead of a seek. Which you probably wont notice, unless you have millions of rows :| (Gah. I hate having millions of rows.)-------Moo. :) |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-07-11 : 09:02:53
|
| Understood. Thanks to all. |
|
|
|
|
|
|
|
|
When to combine fields in an Index