Adding function to sql column to compute sum of columns in that row

Question

I have a sql table which has five columns

1. Count1 float
2. Count2 float
3. Total float
4. Positive float
5. Negative float

When I insert data in Count1 and Count2, Total should be automatically populated with the total of count1 + count 2 and positive column should represent how many values are positive in that row and negative column should represent how many values are negative in that row.

Im using sql server 2008.

How should I add a function to the 3 columns to compute the data when I insert values in column 1 and column 2.

Could someone please provide a code snippet on how to do this.

Thanks.

Answer 1

Just use some computed columns:

CREATE TABLE [dbo].[Table](
[Count1] [float] NOT NULL,
[Count2] [float] NOT NULL,
[Total]  AS ([Count1]+[Count2]),
[Positive]  AS (case when [count1]>(0) then when [count2]>(0) then (2) else (1) end  case when [count2]>(0) then (1) (0) end end),
[Negative]  AS (case when [count1]<(0) then case when [count2]<(0) then (2) else (1)     end else case when [count2]<(0) then (1) else (0) end end)
) ON [PRIMARY]

Answer 2

You can use a SQL FOR INSERT trigger like in the example here: SQL Server 2008 - Help writing simple INSERT Trigger

Or you can do this calculation in you application logic and then write everything to the database.

Answer 3

Rather than have a table with calculated values you should create a View (with the additional calculated columns). Selecting from the view is for the most part identical to a doing the same with an actual table.

Views are easily accessible from the items under the database in Sql Server Management Studio and here is the CREATE VIEW statement documentation.