Simon E Doubt - Monday, February 27, 2017 2:54 PM

We have a table that stores ~3 billion rows with the following columns:
[ID], [Year], [CustomerID], [Data1], [Data2], [Data3].
The ID column is the Clustered PK.

The table will have one daily INSERT, many SELECTs per day, and DELETEs once per year.

The query that reads from this table looks like:
Select Data1, Data2, Data3... from Table1 where Year = @Year and CustomerID = @CustomerID

To optimize this select query, we created a non clustered index on Year and CustomerID, and included all the Data colummns.

However, this seems like a waste of space since basically we have 2 copies of the table.

I'm wondering whether it would be more beneficial if we just created the table using a composite cluserting key of Year, CustomerID, ID.
In theory then we wouldn't need a non-clustered index anymore?

Thanks in advance.