I am new to table performance tuning. I have a flat table with millions-billions of records, with no delete-update operations. Only insert and read operations.
Its structure is like this:
PRIMARY CLUSTERED ON (Column1, Column2) --> because uniqueness based on these 2 columns is important.
Queries will be any of these:
-- query 1 select <all columns> from dbo.this_table where column1 = 'col1val1' -- query 2 select < all columns > from dbo.this_table where Column4= 'col4val1' and Column5='col5val2' and Column7='col7cval3' -- query 3 select < all columns > from dbo.this_table where Column4= 'col4val1' and Column6='col6val2' and Column7='col7cval3' -- query 4 select < all columns > from dbo.this_table where Column4= 'col4val1'
How can I optimize my table for column4, column5, column6, column7? (last 3 queries) Storage is not a issue in my case but speed to read data must be minimum, so I am thinking of columnstore.
Can someone suggest what can be the indexes and in what combination?
In total I have 23 columns, out of which columns 4 to 6 are actually 7 columns. All these 7 columns are varchar with repeating values. Expect maybe 0.01% to 5% of the table to result from each query.