Are you getting that recommendation from a SELECT * query? Can you provide more details?

As @AaronBertrand said, if you could provide the table schemas and select query, that would help.

I think we need to see the query. The suggestions SSMS gives you should probably be just considered a starting point as they are very specifically suggested for that query. Even then you can usually make something more efficient yourself.

Query Added along with Actual Execution Plan

So does your query really need every column from both tables?

Alright that makes sense. The reason it's suggesting this is it would indeed benefit the query to have all of those extra columns included. If you were not doing a SELECT * it would only give you the columns you are selecting. Creating this will make the query you are running faster. However it is less likely to benefit other queries as well. You also need to keep in mind the additional overhead that an index has.

Do you already have a simple index on SlideshowImages (FK_FileID) or not?

@Zane It makes sense that would it would benefit the query :) But this seems like a questionable thing to cover the entire table. Are my worries unfounded? Should I should treat it just like I would when creating a covering index against fewer columns?

@ypercube No. There are currently no indexes except for the Primary one.

You're worries are certainly legitimate. It the current runtime of this query problematic?

Yes. Client Stats are showing an average of 70 ms with around 8,000 records in the SlideshowImages table and 9,000 rows in the other. That 70 ms is expensive when considered against the request time (web application). I'll get back to you after adding the suggested index by SMS.

Adding the index results in times going from 70ms average to 5.8 ms including all columns and 7ms with just index and no covered columns (5 trials; Client statistics). So it seems covering all is faster. So I suppose I just need to consider if it is worth the write cost (yes), and memory cost (probably). I'd still like some comfort that I'm basically creating a copy of the table indexed in a different way and that is ok.

I don't think 1.2 ms is worth having a second copy of the table, unless the table is very narrow. But not with 10 columns. Considering also that a composite index on the other table may improve efficiency even more, for this query.

Depending on the workload, it may be beneficial to change the clustered index to be on a key that leads with the foreign key column.

Are you really talking about 1.2 milliseconds or seconds?

As @JonSeigel asked, could this be a case where the clustered index could be changed to be build on the FileId? This would remove the necessity for a fully covering index, but only you know if this query is important enough to base your table ordering on this column. Other than that, Ypercube's question is legitimate. Can you test with a NC index on that FK column alone?