SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 What are calculated columns?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

abuhassan
Posting Yak Master

105 Posts

Posted - 10/03/2006 :  08:37:08  Show Profile  Reply with Quote
hi

What are calculated colunms?

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 10/03/2006 :  08:42:47  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
There are virtual columns whose results are not stored in the table but computed at run-time. Only the expression text is stored as a part of table definition. For e.g.

Create Table tbl
(
a int,
b int,
c as a + b
)

here C is calculated column.

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

abuhassan
Posting Yak Master

105 Posts

Posted - 10/03/2006 :  08:46:34  Show Profile  Reply with Quote
thanks
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/03/2006 :  10:20:08  Show Profile  Reply with Quote
... and they sometime interfere with normal operations - requiring special flag settings, or the inability to do INSERTs into VIEWs (even with full column lists, and thus need INSTEAD OF TRIGGERs) and so on.

You might be better off using a VIEW instead:

Create Table tbl
(
a int,
b int
)

CREATE VIEW V_tbl
AS
SELECT a, b, 
       a + b AS c
FROM tbl

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/03/2006 :  11:55:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
or just use it as part of Select statement

SELECT a, b, 
       a + b AS c
FROM tbl



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/03/2006 :  15:50:11  Show Profile  Reply with Quote
look at BOL


quote:

computed_column_expression

Is an expression defining the value of a computed column. A computed column is a virtual column not physically stored in the table. It is computed from an expression using other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.

Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.
For example, if the table has integer columns a and b, the computed column a+b may be indexed, but computed column a+DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

A computed column cannot be the target of an INSERT or UPDATE statement.


Note Each row in a table can have different values for columns involved in a computed column, therefore the computed column may not have the same value for each row.

The nullability of computed columns is determined automatically by SQL Server based on the expressions used. The result of most expressions is considered nullable even if only non-nullable columns are present because possible underflows or overflows will produce NULL results as well. Use the COLUMNPROPERTY function (AllowsNull property) to investigate the nullability of any computed column in a table. An expression expr that is nullable can be turned into a non-nullable one by specifying ISNULL(check_expression, constant) where the constant is a non-NULL value substituted for any NULL result.





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000