Set up valuable Indexes on a table

Fredrik M 186 Reputation points
2024-05-31T14:45:31.69+00:00

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

SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
57 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 103.5K Reputation points MVP
    2024-05-31T21:10:03.6666667+00:00

    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful