You can't apply two diffrent sort orders (by diffrent column) using only one index. An index is a structure which store a data on a disk in logical order (B-tree approach) based on columns from index declaration.
It is worth pointing out, that a storage isn't usually problem nowdays and you should focus on CPU, network and engine (IO operations) efforts. And even in this case, you didn't say that, you just said that it would be waste of resources, but it isn't. In many cases, use of additional space for indexes gives your CPU, network and SQL Engine relief when quering data. Sometimes it is good solution for deal with concurrency or deadlock issues too.
In your case, if you want to ensure ability to identify unique row, you have to create
Id column. You shouldn't rely on
Timestamp in this matter.
Let's consider following approaches.
- Create clustered index on
- Create Primary Key as unique nonclustered index only on
Id column (
Timestamp will be included as clustered index key).
You said about range scans with
Timestamp column, so better to have
Data content in the same index (range means lot of data which should be availabe without lookup). This requires for example clustered index on
Timestamp column (which is WHERE predicate). This will be most efficient approach to query data this way.
For other queries which use
Id column, engine will use second nonclustered index. But if
Data column is required, it will cause
Key Lookup (additional effort for CPU, engine, IO).
It's quite good resolution if your queries mainly use
Timestamp as WHERE predicate and require
Data column. Still there is some effort when querying by
In terms of storage, one row have 16 bytes for clustered index and 12 bytes for nonclustered index (28 bytes), so let's think about something similar.
- Create Primary Key as unique clustered index only on
Id column. Most narrow option.
- Create nonclustered index on
Timestamp column and include
Data column (
Id will be included as clustered index key).
Best way in terms of query performance would be to create two covering indexes. In this approach there will be no need to lookup when quering data by
Timestamp column, just smooth read from appropriate index (seek or scan).
Compared to Solution 1 there is additional space used (4 bytes), but we gain benefits when query data (no key lookups).
Always create clustered index firstly, creating it as last one enforce rebouild all nonclustered indexes (replace RID with Clustered Key).
It's also worth noting, that it will be some impact on insert/update/delete operations in both solutions presented above. But it would be acceptable looking at the structure and row size, and still benefits should compensate it.
And one more thing.
It looks like you want implement time series database in SQL Server.
There are complex solution to achive this like Azure Time Series Insights, InfluxDB database or others similar database engine. If you want to stick with SQL Server, you can look for articels about it.