I have a transaction table with columns as below:
(1) Tran_Id int P.K [Identity]
(2) Location_Id smallint
(3) Vertical_Id smallint
(4) Customer_Id smallint
(5) M_ID int
(6) Condition_ID tinyint
(7) T_ID smallint
(8) Amount decimal(10,2)
(9) Transaction_Date DateTime
When user selects Date, Location, Vertical and Customer, a set of combination is returned (M_ID, Condition_ID and T_Id) for that date.
I want to create an index and want to know which columns to include.
I am thinking something like:
CREATE INDEX IDX_DETAILS
INCLUDE (Location_ID, Vertical_ID, Customer_ID, M_ID, Condition_ID, T_ID)
I want to do this because all the columns in the INCLUDE clause are linked to Transaction Date.
Whenever I want to pick any set of M_ID, Condition_ID and T_ID, I am first going to select Date, Location, Vertical and Customer.
I ran the Actual Execution Plan but it is not suggesting any missing indexes because the data, as of now, is low.
What type of index would be suitable in this case?
"Here is a test to find out whether your mission in life is complete. If you're alive, it isn't. "