Applying A Computed Column To SQL Server Table

There are many reasons as to why you would prefer to use a computed column and one of them is its ability to assign a field to hold a specific total for something.

While others prefer not to use it and carry on with their own preferences,    we can go ahead and play with it a bit and see how this can help us.

First, let's see what a computed column is. From MSDN, it says:

"A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.'

Therefore, one can create a column which can hold a result of a computation from an expression concerning other columns.

For example, if I want to have a field that auto-computes the values of other columns, I would use a computed column. See the table below.

CLIENT CODE	BEG BAL	QTY IN	QTY OUT	BALANCE
1	0	100	0	100

The highlighted column is our target column which we would like to have a computed field. What we want for this example is to get the balance by summing up 'beginning balance' and 'quantity in' minus the 'quantity out.' Let's create the table first.

CREATE TABLE [dbo].[TestTable](

[CLIENTCODE] [int] NULL,

[BEGBAL] [int] NULL,

[QTYIN] [int] NULL,

[QTYOUT] [int] NULL,

[BALANCE]  AS ((coalesce([BEGBAL],(0))+coalesce([QTYIN],(0)))-coalesce([QTYOUT],(0)))

) ON [PRIMARY]

go

Notice that the field "BALANCE" is described as an expression which contains the formula. Now, let's insert the above said record.

INSERT into TestTable (CLIENTCODE, BEGBAL, QTYIN, QTYOUT) values (1,0,100,0)

go

Let's pretend that client 1 has given us 100 pieces of shirts for printing. He expects that he can take back half of the shirts after a week. After 1 week, we were able to finish the printing of 50 shirts. So let's update the database and enter the line of code below.

UPDATE TestTable SET QTYOUT = QTYOUT + 50 WHERE CLIENTCODE = 1

go

CLIENT CODE	BEG BAL	QTY IN	QTY OUT	BALANCE
1	0	100	50	50

Take note that after updating the column QTYOUT by adding 50 to it, the field BALANCE is automatically changed to 50.

When I said at the beginning that others may choose their own preference in doing this, I mean that they would  rather update the field BALANCE by themselves like running the line below after updating the QTYIN or QTYOUT fields provided that the field 'BALANCE' is not a computed column.

UPDATE TestTable SET BALANCE = (BEGBAL+QTYIN)-QTYOUT WHERE CLIENTCODE = 1

go

Personally, I like the feature of the computed column. Also, it's value cannot be altered easily as it cannot be a target of INSERT and UPDATE statement. What do you think?