I have a table with almost 3 billion records
the are 4 columns
ID (varchar), start (int), end(int), value (varchar)
ID and start compounded together can make the record unique but ID can be a mix of alphanumerics so is set as a varchar.
the main query I would like to run is fairly simple.
SELECT ID, start, [end] , value
WHERE ID = 'somevalue' AND start >= someint and [end] <= someint
This query is usually run through several times in a batch script where different ID, start and end values are
passed to the query and the results are selected into a temp table.
I'm not sure about the best index to build on this table to make the query run the most efficiently. Whether to build a clustered or non clustered index on ID, start, end.
Or even to reload the table with a unique identity column and partition on this.