Which indexes for joining a table onto itself on two columns
I am using SQL Server 2016, and trying to join a table onto itself, but the query is very slow, and it seems its because SQL Server is refusing to use the indexes.
If you'll excuse the crudity of this example, I have a table like this...
TABLE [Balances] ( account_id NCHAR(20), balance_date DATE, balance INT, next_balance_date DATE )
... and I essentially want to do this query:
SELECT [t1].*, [t2].[balance] [next_balance] FROM [Balances] [t1] LEFT JOIN [Balances] [t2] ON [t1].[next_balance_date] = [t2].[balance_date] AND [t1].[account_id] = [t2].[account_id]
The problem is that no matter what index I try to put onto this table, SQL Server insists on doing a hash match. Which is slow on this large database.
And I currently have the following indexes:
NONCLUSTERED INDEX [idx_acc_date] ON [Balances]? ([account_id] ASC, [balance_date] ASC) NONCLUSTERED INDEX [idx_acc_nextdate] ON [Balances]? ([account_id] ASC, [next_balance_date] ASC)?
What indexes should I be creating to speed-up this query?
(As a small note on the data - all of the balance_dates are as at month end, and therefore the same goes for the next_balance_date. Also, each account_id only has one entry at each balance_date.)