question

tb_Maintable Countryid Stateid Cityid Details1 Details2 Details3 Details4 ProcessDate C1 S1 CT1 28/02/2016 C2 S2 CT2 28/02/2016 C3 S3 CT3 28/02/2016 C4 S4 CT4 28/02/2016 C5 S5 CT5 28/02/2016 C6 S6 CT6 28/02/2016 tb_Details Countryid Stateid Cityid Details1 Details2 Details3 Details4 ProcessDate C1 1 CT1 DT11 DT21 DT31 DT41 28/02/2016 C21 S2 CT2 DT12 DT22 DT32 DT42 28/02/2016 C31 S31 CT3 DT13 DT23 DT33 DT43 28/02/2016 C4 S4 CT4 DT14 DT24 DT34 DT44 28/02/2016 C51 S5 CT5 DT15 DT25 DT35 DT45 28/02/2016 C61 S61 CT6 DT16 DT26 DT36 DT46 28/02/2016 I have following conditions 1.The tb_MainTable has around 1 million records and around 100,000 - 150,000 records are added daily. 2.The tb_Details table has around 3 millions of records and this is daily table means this is truncated and records are inserted daily. 3.The data tb_Maintable also deleted based on ProcessDate which is greater than 180 days. I will be updating the tb_MainTable from the tb_details table as follows 1. The details column(Details1,Details2,Details3,Details4) of the tb_MainTable will be updated from details column of tb_Details based on "CountryId" (tb_maintable.CountryId = tb_Details.CountryId) and ProcessDate 2. For the remaining rows the details column(Details1,Details2,Details3,Details4) of the tb_MainTable will be updated from details column of tb_Details based on "StateId" (tb_MainTable.StateId = tb_Details.StateId) and ProcessDate 3. For the remaining rows the details column(Details1,Details2,Details3,Details4) of the tb_MainTable will be updated from details column of tb_Details based on "CityId" (tb_MainTable.CityId = tb_Details.CityId) and ProcessDate Also, from the tb_Maintable reports is displayed based of individual/combination of filter CountryId,StateId,CityId,Details1,Details2 and ProcessDate My query is which of the columns/combination of columns needs to be indexed and which type of index I need to create to increase the update query performance (as main table has 1 million and the number of records is increasing on daily basis) and also the select statement performance to display the report. Also, what index maintenance strategy needs to be followed so that the performance of Update statement and Select statement does not degrade.