Set up valuable Indexes on a table

Question

I have a really big transaction table, billions of rows, and I need some kinds of Indexes to make it useful.

Right now there is a clustred columnstore index on the table, and a nonclustred on ID, but I guess I need some nonclustred to make the table useful?

Valuable columns that users will query on are,

ID, ISIN, LEI, TradeDate

and a query would be like
**SELECT *** (always select star and there are 30 columns)
FROM [TransactionTable]
Where ISISN='xxxxxxx' and TradeDate between 1Jan AND 31 Jan

Would it be a good idea to create two nonclustred indexes on ISIN, TradeDate and another on LEI, TradeDate?
Or is it better to cretae one on ISIN, LEI, TradeDate even though you search for LEI and ISIN one at a time?

Is the existing clustred columnstore index a good idea or should it be a good idea with something else?

Thanks,
Fred

Answer 1

For the query above an index on (ISIN, TradeDate) seems like a good idea. For a similar query on LEI and TradeDate, an index on (LEI, TradeDate) would be useful.

An index on (ISIN, LEI, TradeDate) would be useful a query like

SELECT ...
FROM  tbl
WHERE ISIN = 'xxxx'
  AND  LEI = 'xxxx'
  AND TradeDate BETWEEN ...

As whether that table should keep its clustered columnstore, or if it should be rebuild with a regular clustered index is not something I can answer with the scant information you have shared.