• Thanks so much for the link. That was a great introduction to indexes, something I sorely needed!

    I do have a couple questions however. The 3rd part on non-clustered indexes and covering says:

    ...the index must be either covering or return sufficiently small number of rows that the required lookups to the clustered index/heap (to retrieve the remainder of the columns) is not considered too expensive by the optimiser. If the required lookups are considered too expensive then the index will not be used... If a nonclustered index is to be used to evaluate a query it needs to be covering or sufficiently selective that the costs of the lookups aren’t deemed to be too high.

    The problem with our datalayer is that LINQ to SQL queries typically look like this:

    public Entity GetById(int id) {

    ...

    Entity e =

    (from x in context.SomeTable

    where x.Id == id

    select x).FirstOrDefault();

    return e;

    }

    The main problem with this is the "select x" line is essentially converted to SELECT *. Our app doesn't use any sort of LINQ projections, for reasons I don't want to get into, other than it was a bad choice made too long ago. The above query would just use the clustered index though. I'm more focused on creating non-clustered indexes right now to fix performance issues. The problem is, even in "find" queries we don't use any form of projection. For example,

    public List<Entity> FindByName(string name) {

    ...

    List<Entity> listOfE =

    (from x in context.SomeTable

    where x.Name == name

    select x).ToList();

    return listOfE;

    }

    So from what I understand, if I want a covering index for this query it either has to include all of the columns in SomeTable to avoid a lookup, or I need to keep the # of included columns minimal so that the cost of the lookup isn't considered too expensive and the index isn't used at all?

    I think what's really confusing me is the included columns. In my situation, should I bother with them at all? And on a query like the "find" one above, the lookups are done per row found?

    Thanks again for all your help.