Re: Wat's the effective way to improve performance in table using indexing?
azz
My approach would be to make transaction_date the clustered index and transaction_id a non clustered primary key. That should give the best performance for reading records by date as the data pages will be in the order you need them in.
Depending on your queries using the Included Columns feature may give you want you want. You could create a non clustered index on the date and then include the amount in the index, this will put the amount into the index pages together with the date and remove the need to perform a second lookup from the non clustered index to the data pages to get the amount. So getting the totals by date will only need to read the index pages, which will be more compact (and so have a shallower tree) than the index pages as each row will only have the id, date and amount.
If you have some natural partitions in the data, such as users then to query data within a year then it may be handy to use a horizontal partition. But I would look at tuning the indexes before partitioning.
Make a copy of the table, change the indexes and run the same query against both tables with the "show actual execution plan" option turned on in SSMS. It will show what proportion of the total script's execution time each query takes.
hope that helps,
aaron