Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
abuhassan
105 Posts |
Posted - 2006-10-03 : 08:37:08
|
hi What are calculated colunms? |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-10-03 : 08:42:47
|
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 AthalyeIndia."Nothing is Impossible" |
|
|
abuhassan
105 Posts |
Posted - 2006-10-03 : 08:46:34
|
thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-03 : 10:20:08
|
... 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_tblASSELECT a, b, a + b AS cFROM tbl Kristen |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-10-03 : 11:55:41
|
or just use it as part of Select statementSELECT a, b, a + b AS cFROM tbl MadhivananFailing to plan is Planning to fail |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-10-03 : 15:50:11
|
look at BOLquote: computed_column_expressionIs 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.
Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
|
|
|
|