SQLServer - treat table as index

I have table that is more or less as follows:
table Sample (
location bigint,
device bigint,
timestamp datetime,
type bigint,
value bigint,
)


For performance reasons we also have one mighty index with same columns in identical order, except that value is included rather than used in index.

This works as expected, except that AFAIU data is kept twice in database - once in the table and once in an index.

Question: is there some way to, conceptually, keep data only in the index? I believe that would require keeping table organized in the same way index is organized, effectively removing need for the index. I tried to create composed primary key that would emulate aforementioned index, but it boiled down to automatic creation of identical index.

What you ask for is what we call a "clustered index" in the SQL Server world. The clustered index is the data. I.e., the b-tree for the index is you actual table data.

A clustered index don't have the concept of included columns since all columns are in the clustered index - the clustered index being the data. So you have the key columns (what you use to "drive the query", like the WHERE clause, so to speak); and the rest of the columns are in the leaf.

You can think of the non-key columns in a clustered index just like included columns in a non-clustered index.

Don't confuse the PK with the clustered index. You decide whether the PK should come with a clustered index or a non-clustered index.

You might have a better candidate for the clustered index than the PK. Fine! Just make the PK non-clustered and create the clustered index on that better candidate.

StackExchange Database Administrators Q#282806, answer score: 8