Is a clustered index faster than a non-clustered index with includes? [duplicate]

Question

I have a table with columns a,b,c,d,e,f,g that has roughly 500,000 rows.

There is a query that gets run very often that does a SELECT * FROM table WHERE a = @a AND b = @b AND c = @c.

Is it better to create a clustered index on a, b, and c, OR am I better off creating a non-clustered index on a, b, and c INCLUDE (d, e, f, g).

Not sure the include would help speed up the query since the select * was issued.

Any help would be appreciated!

Answer 1

A clustered index would be the fastest for that SELECT, but it may not necessarily be correct choice.

A clustered index determines the order in which records are physically stored (which is why you can only have one per table). So while it would be the fastest for THAT query, it may slow down other queries and could KILL updates and inserts if one of those columns was changing, which could mean that the record would need to be physically re-located.

An INCLUDE would again speed up that query at the expense of extra storage and extra index maintenance if any of those fields (including the included fields) were updated.

I would START with a non-clustered index on a, b, and c and see if that gets your performance to a reasonable level. Anything more could just be trading speed in one area for slowness in another.

Answer 2

The clustered index will be faster.

With SELECT *, both your clustered and non-clustered (with include-all) contain all the columns within each page. However, the non-clustered index ALSO contains a reference back to the clustered key - this is required in case you add more columns to the table, but really also because all indexes (except indexed views) are pointers to the data pages. The NCI will not feature the new columns (fixed include list) but the data pages will.

SQL Server may be smart enough to find out that SELECT * can be fulfilled by an INDEX SCAN on the NCI (+includes) without a bookmark lookup back to the data pages, but even then, that index scan will be one column wider than the equivalent clustered index scan.

It is normally not a good idea to have a 3-column clustering key. You may consider an alternative of using a simple single-column identity clustering key, and creating an indexed view clustered around the 3 columns.

Answer 3

The answer to the question as stated in your subject line in general is no. Because you generally would much prefer to have the narrowest covering (probably non-clustered) index.

But in your case you are selecting * so if the clustered index is good enough match to your seek criteria it's always going to be picked, since anything narrower will need to do a bookmark lookup.

So this raises a big question of why this query is the way it is, whether there is a better choice of clustered index in general for your app (narrow, static, increasing, unique), and whether you really need to be getting all the columns. Because neither of the two options you give is really that typical of a good design.

500000 rows is fairly small, but if performance is an issue, you want to see how many rows are fitting per page and whether you could improve that be being more selective in your query and having a covering non-clustered index.