I have a table called Companies with int identity column as primary key and other fields. Also there is a Status column which can hold either 0 or 1. I use this status column in a join from some child table like where a.status = 1 along with other conditions.
Now, the question is should I create an index for this Status column? Will it improve the performance?
You say your column only holds ones and zeros. If it is a bit field it cannot be indexed. Even if it is not a bit field, if the distribution of values for one and zero are about 50%, the optimizer might not get much out of using the index. In a binary tree it would only save 1 search ply.
And the optimizer is making the right call in your case
How many rows of data are we talking about?
Thank you for your concern. Yes I do list all the fields and never use the * from my programs. Number of records in the comp. table is around 500 and the orders table may be few thousands. I also filter by company.
On such a small number of records, you will not see much of an improvement. Anytime you have so a limited distribution like yes/no, male/female ... the optimizer will normally chose a table scan over an index (so normally the recommendation is No Way). Unless your distribution is very high for 1 value and very low for the other value, an index will only help for the low value anyway. If the distribution of these values are remotely close to each the optimizer will probably perform a table scan anyway. Since these tables are small, sql will probably chose a table scan over an index even if your distribution is ripe for an index.