SQL Server/T-SQL/Table/Computed Column
Add value from two columns as the computed column
<source lang="sql">
1> 2> CREATE TABLE T ( 3> int1 int, 4> bit1 bit, 5> varchar1 varchar(3), 6> dec1 dec(5,2), 7> cmp1 AS (int1 + bit1) 8> ) 9> GO 1> INSERT T (int1, bit1) VALUES (1, 0) 2> GO (1 rows affected) 1> INSERT T (int1, varchar1) VALUES (2, "abc") 2> GO (1 rows affected) 1> INSERT T (int1, dec1) VALUES (3, 5.25) 2> GO (1 rows affected) 1> INSERT T (bit1, dec1) VALUES (1, 9.75) 2> GO (1 rows affected) 1> --Average of dec1 values 2> SELECT CAST(AVG(dec1) AS dec(5,2)) "Avg of dec1" 3> FROM T 4> WHERE dec1 IS NOT NULL 5> GO Avg of dec1
7.50
(1 rows affected) 1> 2> 3> drop table t 4> GO 1>
</source>
Computed columns are (by default) virtual columns not physically stored in the table
<source lang="sql">
1> -- Computed columns are (by default) virtual columns not physically stored in the table. 2> SET QUOTED_IDENTIFIER ON 3> GO 1> 2> CREATE TABLE orders 3> (id INT NOT NULL, 4> price MONEY NOT NULL, 5> quantity INT NOT NULL, 6> orderdate DATETIME NOT NULL, 7> total AS price * quantity PERSISTED, 8> shippeddate AS DATEADD (DAY, 7, orderdate)) 9> GO 1> 2> insert into orders (id, price, quantity, orderdate) 3> values (1, 100, 2, "1997.10.1") 4> GO (1 rows affected) 1> 2> select * from orders 3> GO id price quantity orderdate total shippeddate
--------------------- ----------- ----------------------- --------------------- -----------------------
1 100.0000 2 1997-10-01 00:00:00.000 200.0000 1997-10-08 00:00:00.000
(1 rows affected) 1> 2> drop table orders 3> GO
</source>