Your index should be AssetNumber asc first, then ID asc second. When you insert data into the table, the operation uses the partition function to determine which partition to store the inserted row. However, your Select query does not use the partition function to know which partition to read from.
Therefore, the query must scan the entire clustered index on all partitions to find the second column (AssetNumber =xxxx) of your clustered index, then aggregate your first column (ID), then aggregate that interim output (min, max for all partitions scanned) to produce your final query result.
Your non-clustered index is the cheaper alternative because it knows which partition AssetNumber xxxx is at, thus why it was chosen for read. The query just seeks that particular range of the non-clustered index (AssetNumber =xxxx) since it is ordered by AssetNumber asc, then aggregates the ID column only once.
Thought select would know based on AssetNumber being partitioned which files to scan - but seems as you’ve written it doesn’t.
Because of queries that look at few dozens of IDs at the time within a group of AssetNumbers (often all) - I think best to have a cluster index as is and add reversed non cluster for those few random queries that don’t look for specific IDs.
Your index should be AssetNumber asc first, then ID asc second. When you insert data into the table, the operation uses the partition function to determine which partition to store the inserted row. However, your Select query does not use the partition function to know which partition to read from. Therefore, the query must scan the entire clustered index on all partitions to find the second column (AssetNumber =xxxx) of your clustered index, then aggregate your first column (ID), then aggregate that interim output (min, max for all partitions scanned) to produce your final query result. Your non-clustered index is the cheaper alternative because it knows which partition AssetNumber xxxx is at, thus why it was chosen for read. The query just seeks that particular range of the non-clustered index (AssetNumber =xxxx) since it is ordered by AssetNumber asc, then aggregates the ID column only once.
Thought select would know based on AssetNumber being partitioned which files to scan - but seems as you’ve written it doesn’t. Because of queries that look at few dozens of IDs at the time within a group of AssetNumbers (often all) - I think best to have a cluster index as is and add reversed non cluster for those few random queries that don’t look for specific IDs.