I'm not a DBA, excuse me if this question sounds dumb. Hope I can get help from an experienced DBA, because I'm questioning this for a long time.
Assume there are 2 tables, one table with a lot of records, and one table is just static data with 2 records (for example: a Yes/No table).
As example take these 2 tables:
Table_Yes_No(only 2 records: Yes and No. Of course they have an incrementing clustered PK)
Table_Form(Very big table with lots of records, and one column has FK to the PK of
Now my question; Is it worth putting an index on the FK (reference from
Table_Form to the
Table_Yes_No table)? The column will only contain 1 or 2 (but not sorted, because no index). Is it worth indexing such a FK?
An example in SQLFiddle: http://sqlfiddle.com/#!18/51614/3/0
That column will be queried anyway, the question is will the index help the performance or not. The small table is static data, will never be changed. The big table will be queried heavily, and there will be also a lot of CRUDs on it.