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 |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2009-04-15 : 20:06:50
|
| Hi there,If I've got a table with a column that only has two possible values, should I put an index on it?I say, YES!, because if I filter on this column, then the db engine knows that it can turn left at the index and every row can be assumed to have the required value in the column.Cheers, XF. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-04-15 : 23:14:23
|
| A column like that is probably the worst possible candidate for an index, except for a column with only a single value.I doubt the SQL Server would use the index, because most likely it would have to read every page to get the data you need, so there is no point in first reading the index, and then reading every page in the table.It's like if you had a library with shelves full of white and black books randomly mixed up on each row. Would it be faster to just walk down each row to get the white books, or to first look up all the white books in the card catalog, and then go get each book?CODO ERGO SUM |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLinTheCloud
Starting Member
3 Posts |
Posted - 2009-04-16 : 01:26:46
|
| Indexing a column with such a low variance is a waste of time and space, since SQL Server won't use it unless... there's a reason to combine the yes/no column with some other column that has higher variance and would also be used to determine the result set. For instance, if you were looking for people who are active employees (yes/no) and who work at the Delaware plant (assuming that the firm has offices scattered all over the U.S.), then creating the index by concatenating active_emp with plant_location might be useful. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-04-16 : 09:42:16
|
Well my intutions are clearly out of whack here.quote:
Would it be faster to just walk down each row to get the white books, or to first look up all the white books in the card catalog, and then go get each book?
I don't see how that contrasts with a query with high variance...quote:
Would it be faster to just walk down each row to get the books that begin with 'A', or to first look up all the books that begin with 'A' in the card catalog, and then go get each book?
|
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-04-16 : 12:21:02
|
| ...or are indexes only supposed to used when looking up individual items? |
|
|
|
|
|
|
|
|
Index on 'ere?