SMS Suggesting covering all columns in an index?

I’m still a bit newer to indexes and database works, so I may very well be missing something obvious.

SMS suggested adding a covered index to one of my tables that Indexes on the column I believe makes perfect sense. However, it also recommended including (covering) every other column in the table. To me this seems like it is moving the entire table into the index. Which I believe is probably a bad idea.Is SMS potentially misleading me in this case?

More generally is there any reason to cover every single column in a table?

SELECT  * 
    FROM    SlideshowImages SI
    JOIN    Files F ON F.PK_FileID = SI.FK_FileID
    WHERE   F.FK_ModuleID   = 81
    AND     F.ParentType    = 2
    AND     F.FK_ItemID     = @SlideshowID
    ORDER BY F.[Order] ASC

Suggested Index by SMS – All 10 columns of the SlideshowImages table:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SlideshowImages] ([FK_FileID])
INCLUDE ([PK_SlideshowImageID],[Title],[Caption],[IsLink],[LinkAddress],[StartDate],[EndDate],[AltText],[OpenInNewWindow])

SMS suggested adding a covered index to one of my tables that indexes on the column I believe makes perfect sense. However, it also recommended including (covering) every other column in the table.

The suggestions shown in SSMS are actually generated by the query optimizer while it searches for an efficient physical execution plan for the query. The suggestions are opportunistic and based only on a superficial analysis of the single query.

To me this seems like it is moving the entire table into the index.

Copying rather than moving, but yes, an index that covers every column in the base table (either as a key or included column) is essentially a copy of the table’s data sorted in a different way. Conceptually, this is the same as having multiple clustered indexes on a table.

More generally is there any reason to cover every single column in a table?

It is quite unusual to do this, but there will always be very specific scenarios where it can make sense. An index that is a copy of the table data sorted in a different way will need to be maintained for every change to the base table, will consume more space on disk and in memory, and will generate more transaction log. For most applications, the costs outweigh the benefits.

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.

A fully covering index means all the data needed for the query can come from that single b-tree structure. If a nonclustered index does not fully cover the query, the optimizer can choose to locate rows using the index then look up the remaining column data in the base table using the row locator stored in the nonclustered index. The extra step has a cost associated with it, which is proportional to the number of rows qualified by the index key search.

In most cases, the performance improvement offered by creating the nonclustered index on FK_FileID alone would be considered the best trade-off among the various considerations. That said, you may have very specific requirements that mean you are happy to go with the fully-covering option.