If a FK does not have a dedicated index on them but are part of wider indexes used for covering queries, Should they have a dedicated index created?
It depends on the table's access patterns. If the column is being searched a lot (and, ideally, is highly selective), then yes, you absolutely should have an index on that column, with the column as the first key column in the definition.
Should I be removing some of these indexes and combining them with included columns instead? then have dedicated indexes for my foreign keys?
What was given in the question is somewhat unclear, and the question you've asked is a bit... confused, so let's take a step back for a second.
In SQL Server 2005+, the three most important parts of an index definition are:
The key columns, which determines the index sort order. This means the order of the key columns is very important, because SQL Server uses an index by searching for a value in the first key column, then in the second key column, etc.
The included columns, which are copies of row data tagged onto the index structure. The order included columns are specified is irrelevant.
Is the index unique? This means that the index key can contain only unique combinations of column values.
(While this is not relevant to the discussion at hand, for completeness I will mention it here: SQL Server 2008+ introduces the concept of filtered indexes, which only includes rows in the index that satisfy a predicate.)
The first thing you should do is index consolidation. This involves using the points above to combine indexes that share commonalities.
For example, consider the following two indexes:
CREATE INDEX IX_1 ON [dbo].[t1](C1) INCLUDE(C3, C4);
CREATE INDEX IX_2 ON [dbo].[t1](C1, C2) INCLUDE(C5);
These indexes share the leading key column, C1. Included columns can be specified in any order, so these two indexes could be combined as follows:
CREATE INDEX IX_3 ON [dbo].[t1](C1, C2) INCLUDE(C3, C4, C5);
Where index keys differ in their composition or other properties, you have to be very careful. Consider these indexes:
CREATE INDEX IX_4 ON [dbo].[t1](C1, C3) INCLUDE(C4);
CREATE UNIQUE INDEX IX_5 ON [dbo].[t1](C1, C4) INCLUDE(C5);
Now the decision is not as easy. You have to determine what to do based on your workload, which queries hit the table, and the selectivity of the data itself.
So to answer the question more directly: if you currently have one or more indexes where the column of interest is the first key column in those indexes, you don't have to add more indexes, because the indexes you have are useful.
If the column is searched frequently and there isn't an index with that column as the first key column, you should create an index with that column as the first key column. (Depending on query requirements, you may want to specify other columns as well, for either the key or the included columns.)
If the column is not searched frequently, you can potentially get away with having it contained in another index (not the first key column): the query may be satisfied by scanning the index that contains the column. This is not as efficient as an index seek (for many reasons), but if this operation doesn't happen too often, and the performance in this case is acceptable, you may be okay.
Remember that creating indexes isn't free -- they take up data space, log space, cache memory, and can potentially slow down INSERT/UPDATE/DELETE activity (having said that, there can be other advantages to creating indexes). It's a balance you have to strike for your environment.
Can i replace the non-clustered indices with one covering index ?
No. Suppose you sometimes find people by LastName and sometimes by FirstName. An index on (LastName, FirstName) won't help you find people by FirstName.
If not, is there any way to get rid of RID Lookup rather than a covering index ?
Not in a way that's particularly useful.
Does each query require a different covering index depending on the columns in the select list and the search conditions ?
Pretty much. Your job is to come up with a compromise.
I have no clustered indexes in the table , does adding a unique column and setting it as a primary key helps in getting rid of RID Lookups ?
A PK doesn't necessarily mean a clustered index. And having a CIX just means your RID Lookups will become Key Lookups, which are potentially worse. But without CIXs you have heaps which can fragment when you change or delete data. So CIXs are fine, but won't improve performance of your Lookups.
Best Answer
As I understand it, you created two non-clustered indexes and in order to cover the query, you have all the columns from the table in these indexed (the key is the key and the other columns are included columns).
If above is correct, you now have two more copies of the table table, sort of. I.e., three instances of the same data. You have the clustered index that is the table and is sorted by the clustered key. Then you have all columns again, twice, in each of the non-clustered indexes. The table has now tripled in size.
So, your creation of the non-clustered indexes causes the modifications to be two times more expensive compared to earlier. Or even a bit more than that since you likely now in the non-clustered indexes will experience more page-splits compared to earlier and you pay the penalty for that as well when it happens (including the transaction-logging of the data-movement when the split occurs).