Is a clustered index faster than a non-clustered index with it turned on?

Question

I have a table with columns a, b, c, d, e, f, g, which contains approximately 500,000 rows.

There is a query that runs very often, which makes 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 I better create a non-clustered index on a, b and c INCLUDE (d, e, f, g) .

Not sure if inclusion will help speed up the request from the moment that select is issued.

Any help would be appreciated!

Answer 1

A clustered index will be the fastest for this SELECT , but this may not necessarily be the right choice.

A clustered index determines the storage order of records physically (so you can only have one per table). Thus, although this would be the fastest THAT request, it can slow down other requests and can KILL update and insert if one of these columns changes, which may mean that the record needs to be physically redone.

INCLUDE will speed up this request again by providing additional storage and additional index maintenance if any of these fields (including the included fields) were updated.

I would START with a non-clustered index on a, b and c and see if it would deliver your performance to a reasonable level. Anything else may simply be the speed of trading in one area for slowness in another.

Answer 2

A clustered index will be faster.

With SELECT * , both your clustered and non-clustered (with all included) contain all the columns on each page. However, the nonclustered ALSO index contains a link back to the clustered key - this is required if you add more columns to the table, but also because all indexes (except indexed views) are pointers to data pages. The NCI will not include new columns (a fixed list of inclusions), but there will be data pages.

SQL Server may be smart enough to know that SELECT * can be performed using INDEX SCAN on NCI (+ includes) without searching for bookmarks back to data pages, but even then this index scan will be one column wider than the equivalent cluster index scan .

As a rule, it is not recommended to have a cluster key with 3 columns. You might consider an alternative to using a simple single-column clustering clustering key and creating an indexed view grouped around three columns.

Answer 3

The answer to the question, as indicated in your topic, in general is no. Because you usually prefer to have the narrowest (albeit non-clustered) index.

But in your case, you select *, so if the clustered index is good enough for your search criteria, it will always be selected, since something narrower will need to be searched in the bookmark.

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

500,000 rows are pretty small, but if performance is a problem, you want to see how many rows are suitable for each page, and whether you can improve, which will be more selective in your query and have a covering non-clustered index.

Answer 4

Your clustered index is the order in which data is stored in the table, so you can only have one clustered index for each table. If you are creating a new index (not clustered by default), make sure that the columns are defined in the index in the same order as they are used in the WHERE clause, which allows SQL to perform direct indexing to find records, to search.