2 added 512 characters in body
source | link

There are 2 options:
1. Create clustered index by account_id. Delete all other indexes.
2. Add new column Balance_ID INT IDENTITY(1,1) and create clustered index on that column. Create another non-clustered index on ONLY one column account_id.

The second option is more preferable because it is much faster. You join INT only by 4 bytes. While your current queries join by 40 bytes. That means your current operation is 10 times more expensive.

Also, ask yourself couple of questions:
- Do you really need have Account_ID as Unicode?
- Can you convert Account_ID to INT or BIGINT?
Hopefully, you've got my point.

ADDITION:

  1. You create clustered index on only one column. Technically, you can do it on multiple, but the main goal to have it as short as possible.
  2. If you created clustered index on account_id you DO NOT NEED any other indexes on that column.
  3. I can guess that your performance improved because before there was no Clustered index and indexes were so bad that SQL decided to do full table scan instead.
  4. Suggestion: read a book about Indexes, their differences and how they work.

There are 2 options:
1. Create clustered index by account_id. Delete all other indexes.
2. Add new column Balance_ID INT IDENTITY(1,1) and create clustered index on that column. Create another non-clustered index on ONLY one column account_id.

The second option is more preferable because it is much faster. You join INT only by 4 bytes. While your current queries join by 40 bytes. That means your current operation is 10 times more expensive.

Also, ask yourself couple of questions:
- Do you really need have Account_ID as Unicode?
- Can you convert Account_ID to INT or BIGINT?
Hopefully, you've got my point.

There are 2 options:
1. Create clustered index by account_id. Delete all other indexes.
2. Add new column Balance_ID INT IDENTITY(1,1) and create clustered index on that column. Create another non-clustered index on ONLY one column account_id.

The second option is more preferable because it is much faster. You join INT only by 4 bytes. While your current queries join by 40 bytes. That means your current operation is 10 times more expensive.

Also, ask yourself couple of questions:
- Do you really need have Account_ID as Unicode?
- Can you convert Account_ID to INT or BIGINT?
Hopefully, you've got my point.

ADDITION:

  1. You create clustered index on only one column. Technically, you can do it on multiple, but the main goal to have it as short as possible.
  2. If you created clustered index on account_id you DO NOT NEED any other indexes on that column.
  3. I can guess that your performance improved because before there was no Clustered index and indexes were so bad that SQL decided to do full table scan instead.
  4. Suggestion: read a book about Indexes, their differences and how they work.
1
source | link

There are 2 options:
1. Create clustered index by account_id. Delete all other indexes.
2. Add new column Balance_ID INT IDENTITY(1,1) and create clustered index on that column. Create another non-clustered index on ONLY one column account_id.

The second option is more preferable because it is much faster. You join INT only by 4 bytes. While your current queries join by 40 bytes. That means your current operation is 10 times more expensive.

Also, ask yourself couple of questions:
- Do you really need have Account_ID as Unicode?
- Can you convert Account_ID to INT or BIGINT?
Hopefully, you've got my point.