OLTP Indexed Views or SQL Calculation At our financial company, we have different OLTP transactions for customers. We have a transaction table (debits, credits) and balance table.
We will only have 1000 transactions per month.

What is the best way to have account balance table?

1) One person wanted to use an indexed view, since we have relatively static data.

2) I want to calculate account balance right in sql code. I did not want to deal with issue of queries sometimes not using indexed view in execution plans, deadlocks during update, complicated execution plans when updating/delete data etc. Prefer indexed views for reporting/Olap databases.

Is there a correct answer for concurrency transactional databases? Any issues to watch out for using indexed views in small transactional oltp databases? 

create table dbo.AccountTransaction
( AccountTransactionId int primary key identity(1,1),
  CustomerId int null,
  TransactionAmount money not null )

create table dbo.AccountBalance
( AccountBalanceId int primary key identity(1,1),
  CustomerId int null,
  BalanceAmount money null )


SQL Calculation Method:

begin transaction
    insert into AccountTransaction 
    values(@CustomerId, @TransactionAmount)

    update AccountBalance
    set BalanceAmount = BalanceAmount + TransactionAmount
    where CustomerId = @CustomerId

end transaction


Indexed View Method:

create view dbo.AccountBalanceVw
with schemabinding
as
select
    customerid,
    sum(transactionamount) as BalanceAmount,
    COUNT_BIG(*) AS CountLines
from dbo.AccountTransaction
group by customerid

create unique clustered index cx_AccountBalanceVw on AccountBalanceVw(CustomerID);
This is a very open ended question where lots of factors you havent revealed could change peoples opinions.

That said; I've had to deal with similar scenarios in my career.

To get the current balance you are going to need to sum up all the transactions.  This leads to the view that an indexed view is the right solution when using SQL Server.  It is indeed a good one, however, it is not without its limitations.  You need to consider these factors:


How much impact is a schema change going to cost me if I have an indexed view?
Will I need to drop the index and rebuild it?
How long will that take?
Can I afford the blocking on the base table(s) during that rebuild?
Does my indexed view prevent refactoring the transaction table(s) later in anyway?


These could become considerable blockers to your application development in the future.

Because of these (and I am sure other) factors you might start leaning towards calculating the balance "on the fly".  That is, you essentially have your view but without the index on it.  Or just query the table and sum the transactions.  If you start looking at this approach you have to consider:


Will we have lots of transactions per account?
And if we do, do the balance queries perform?
Will calculating the balance impact other queries (notably adding new transactions) in any way (think blocking here)


You have given the third option of calculating and storing the balance at the time of data updates.  This seems attractive as it avoids all the downsides of the previous two scenarios I've covered.  You have fast look ups and my schema is not hampered or tied to the indexed view.  There are some gotchas here too:


Do I calculate the new balance manually each time I update the table?
Do I use a trigger to maintain the balance (and avoid the previous point)
Do I have to cater for rows also being removed or updated (protip: you will...even if you think you wont)
Can I afford the performance impact of any trigger I create? Do its benefits outweigh this cost?


We havent even thought about transaction isolation levels yet!  Are you going to be using READ COMMITTED SNAPSHOT or SNAPSHOT ISOLATION at all? Because these have an impact you need to consider.

What I would probably do from your information given

You mention you only expect 1000 transactions per month. I'm going to read that as 1000 per customer per month as a worse case scenario.  Thats not really a lot of data, especially early on.  Granted, if you have 1,000,000 customers all doing 1000 transactions you're going to have a lot of data.  I'm assuming a small number of customers too.

I wouldn't bother with an indexed view or a calculated table/column for the balance.  I'd simply query it on the fly to start with.  This way you can enhance and expand your schema and not worry about the impacts the other choices might have had.

Longer term, as the schema settled down and the application matured I would look at those other two options to see which suits the use case best.  I love what indexed views can give you, however, they are not without their downsides and can be quite a pain later on when you want to make changes.

This is approach follows the "premature optimisation" issue one can face in application programming.  You cannot always do this with database design, however, I think here you can as the optimisation you could later is pretty easy.  Especially if you abstract the balance retrival through a view in the beginning.  No matter what approach you take later, you simply update that view to retrive the data from the new object(s).

Slightly Off Topic:

Couple of tips for your schema (Im also aware you may have simplified it for the question)


Your schema shows a list of transactions per customer. This says each customer has one and only one account.  That may be the case today, however, I would thoroughly recommend designing your schema to accommodate many accounts per customer.
I wouldnt recommend using the money data type.  It doesn't have enough precision or scale.  Often when dealing with financial data you might need to go to 6 decimal places depending on the currency.  Or beyond the max size of the money datatype.