开发者

sql - retain calculated result in calculated field

开发者 https://www.devze.com 2023-03-15 03:51 出处:网络
certain fields in our database contain calculated functions e.g. select lastname + \', \' + firstname as fullname from contact where contact.id =$contact$

certain fields in our database contain calculated functions e.g.

select lastname + ', ' + firstname as fullname from contact where contact.id =$contact$

when viewing the field the correct data is shown (i assume this is because when you open the record, the calculation is executed). however, the data is not 'stored' to the field, and therefore is null until the record is opened. is it possible to 'store' the result to the field, making it possible to search the data?

many thanks

james

EDIT

it is not possible for me to create computed_columns using our software.

the above field is a text feild where either 1) a user can manual type in the required data or 2) the database can generate the answer for you (but only whilst you are looking at the record开发者_如何学Go). i know that if I run the following:

Select * from contact where contact.id =$contact$ for xml auto

i only get lastname, firstname - so i know that the fullname field does not retain its information.


If you are using computed columns in sql server, the column is already searchable regardless of whether the calculation result is stored or not. However, if you would like to make it so that the calculation is not run each time you read the row, you can change that under row properties in your Modify Table GUI.


Use the PERSISTED key word when you create the column

From BOL:

PERSISTED Specifies that the SQL Server Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Marking a computed column as PERSISTED lets you create an index on a computed column that is deterministic, but not precise. For more information, see Creating Indexes on Computed Columns. Any computed columns that are used as partitioning columns of a partitioned table must be explicitly marked PERSISTED. computed_column_expression must be deterministic when PERSISTED is specified.


This isn't the way computed columns work in SQL Server, so I suspect this is something your client application is doing. How are you looking at the data when the value is computed correctly? Does it work when you view the data in SSMS?

Take a look at http://msdn.microsoft.com/en-us/library/ms191250(v=SQL.90).aspx to see how to create computed columns properly.

eg.

create table TestTable
    (a int,
    b int,
    c as a + b)

insert into TestTable (a,b)
values (1,2)

select * from TestTable
where c = 3

This query is based on the computed column and it returns the row that's been inserted.


You need to use the PERSISTED option on a column when you use CREATE TABLE e.g.

CREATE TABLE test (col_a INT, col_b INT, col_c AS col_A * col_B PERSISTED)
0

精彩评论

暂无评论...
验证码 换一张
取 消