SQL Server/T-SQL/Table/Computed Column

Материал из SQL эксперт
Перейти к: навигация, поиск

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>