Do I need a clustered index on a table with only few columns?

Question

Table:

Address (
 AddressID bigint, 
 Street VARCHAR(150), 
 City VARCHAR(200), 
 State CHAR(2), 
 State VARCHAR(5)
)

Table size: 5GB, 60 million rows.

AdressID is sequential.

Remark: 95-98% of queries used are just the same following query with variations of AddressID range in the WHERE clause:

SELECT AddressID,  Street , City, State, State 
 WHERE AddressID > someNumber AND AddressID < anotherNumber

Question: Will it be worth creating a clustered index on AddressID? Or will it be counterproductive, since at least 95% of queries select all columns of the table?

Based on the above information, are there any other good choices for creating any index that will help improve the performance of the above query? I thought of creating a covering index, but wouldn't that be just like a table scan, since it will include all the columns of the table?

Answer 1

To Erik's point, an index is worth having when you have to apply any kind of predicate against the table (e.g. JOIN, WHERE, HAVING clauses), and can also be helpful when using GROUP BY or ORDER BY, regardless if you apply a predicate to the table. The reasoning being, is an index logically sorts the data its key is defined on - typically with a B-Tree data structure. Think of an index like a phone book, which is sorted.

A clustered index is logically sorting the table data itself. A nonclustered index stores a copy of the data defined in that index, and sorts that copy of data based on its key. Since a clustered index is essentially the table, all columns are implicitly available in that index, at the leaf level nodes. And it's basically a freebie to have since it's not storing a copy of the data in the table, it is the table.

This makes a clustered index defined on (AddressID) very fitting for your use case, because you need all columns from the table, and you're filtering your table with a WHERE clause that always references that field. An index allows the SQL Engine to directly seek to the B-Tree node that starts the range of your WHERE clause values. Without one, the entire table of 60 million rows would need to be first scanned, before it could filter it down to only the rows your WHERE clause needs.

The other important benefit of having a clustered index on your table, is if you later on needed to add additional nonclustered indexes to support any other cases that predicate against your table. You can keep some of those nonclustered indexes lean by not including all of the columns that the query is SELECTing. This is because the SQL Engine can leverage the clustered index to fetch those missing fields, rather efficiently, after filtering down the rows by the nonclustered index that covers this other predicate. Every nonclustered index implicitly stores the clustered index key, so that it can lookup any columns it doesn't have stored easily. This execution step is called Key Lookups. Depending on the number of rows being returned / needing to do Key Lookups vs the width of the table / index, sometimes it's more optimal to just store the extra copies of those fields in the nonclustered index itself - but this is very situational and Key Lookups generally work well.

The only minor drawback to adding a clustered index to a table, is there's a tiny amount of overhead for the index to manage itself as rows are inserted or updated based on the key of the index. Typically the read performance gains from the clustered index far outweigh this write overhead though. It is rare to not have a clustered index on a table. An exception are use cases where the table is always read from in its entirety, and never or rarely updated or deleted from, only inserted into or truncated - for example, a staging type of table.