в 13:46, 26 мая 2010

2010-05-26T13:46:06Z

Admin: 1 версия

2010-05-26T10:19:56Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL/Table/Computed Column - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия