Computed columns

Hi,

Consider the following example

create table sample

(col1 int,

col2 int ,

col3 AS col1 + col2) PERSISTED NOT NULL)

basically col3 is a computed column. Now when ever a row in col1 or col2 is updated the computed column will reflect the new value. how does this happen in the background. does this use row level triggers or what other mechanism is used to maintain col3 - computed column

the value does not exist by default

when you select a record and you included the calculated column

the server reevaluates everything which

is one of the great disadvantage of the computed column.

computation is being done all over again when you select from this column

|||I have marked it PERSISTED ...meaning that the column is saved in the database. I think what you are talking about is the computed column without using the PERSISTED key word.|||

sorry i wasn't aware of that new feature

any way

Their values are updated when any columns that are part of their calculation change

|||I would say magic :) Seriously, it would be done at a physical implementation level below what we have access to, much like values in an index get maintained. I think if you thought of it sort of like a row-level trigger it wouldn't be "wrong," but it is not through any mechanism that we have direct access to for sure.|||the reason why i posted the question is : If i use a lot of computed columns in my database, will it cause any kind of performance problems. so far what ever i have read about computed columns, no where it is mentioned that using computed columns may cause performance problems. Please let me know if you have any information.|||I think the question is more about how you need the data. There will be a performance hit, whether you persist them, or not. The difference will be based on whether you modify data more, or read it more. If it is a frequently used column (or might be) try persisting it. If it doesn't slow you down too much then that would be the best idea. Unless your formula is tremendously complex, I doubt you will even notice.|||

one good thing in answering in this forum is that you

got to learn new things. anyway here's what BOL has to say

Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The SQL Server 2005 Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it.

In terms of performance therefore persisted columns, performs better than non-persisted. Except of course when we hard talking of harddisk consumption. persisted column may even outrun a column-trigger solution and the first is easier to maintain over the later.

|||Thanks for all your replies.

composing a reference from fields located in mutiple tables

Consider a situation. There is a table of submitted 'documents'. They have some attributes. There are assignments to process the things, which have a date they were created. Finally there is a price list which specifies the price according to document features and date, so that the assignment to process a document created at different time will have a different cost. In other words, there is a relation
(assignment->document.attribute(s) + assignment.date) -> pricelist.price

Creating relations has the integrity advantages: it is not possible to create an assignment, which price is not defined in the pricelist; precludes the pricelist entry removal if it is referred by any assignments.

Should a view, which combines all the foreign fields into one virtual table, be created to make establishing the reference possible?

Not quite sure I understand the entire request. However, it seems you should be able to enforce referential integrity via Instead Of trigger.

Perhaps, you could give us some sample DDL and desired output to better describe your issue. We might be able to help further then.

|||Normally, you have all the tables interrelated. The reference (a foreign key) points to an object in another table specifying the "container" it belongs to. For instance, many books refer a single author.

Sometimes, you need to establish a complex reference consisting of multiple fields. For instance, a job refers to pricelist. The options in the print job specify a "service id", which has a unique price in the pricelist.

Suppose now that the pricelist can be updated. When job is created, it fixes the latest service cost in int field, the pricelist date. So the cost is uniquely identified by the job options (some fields) and the date. This is a complex key.

What I have faced is that nobody addresses the possibility of having the job attributes fixed in a separate table (say, documents to be processed always have the same settings). A job refers a document, from which the attributes are derived and coupled with the pricelist date identifies the job cost in the pricelist table. Effectively, the complex key is composed from fields located in different tables. A record contains only a part of complex key plus a reference to another entity, which has a rest of the key.

One way to create a relation would be to produce a view joining the key field tables. However, views are not enabled in diagrams. I suppose the reason is because the views are not allowed to participate in data relations.

Actually, I have decided that in my case I do not need to fix the job settings in the referred document, so I'll have all the key fields in one table. Yet, the topic is quite general to be interesting for me and others.

complicated SQL help

Perhaps you can help with something that I consider kind of omplicated? (I am SO hoping that I am NOT going to have to do this manually!!!)

I have a table (about 3000 rows) where two of the columns have Domain User information.

COL1 has DOMAIN\Username and COL2 has (or SHOULD have) DOMAIN@.username.com

I need to look at each field in COL1 and if exists DOMAIN\username, I need to populate COL2 with username@.DOMAIN.com

Is this possible?very possibleupdate daTable
set COL2 = substring(COL1
,charindex('\',COL1)+1
,LEN(COL1)-charindex('\',COL1)
)
+ '@.' +
left(COL1,charindex('\',COL1)-1)
where coalesce(COL1,'') > ''tip: back up your data before trying this|||Thank you! That's a start! I learn so much from these forums!!

Problem is... I still do not have the .com part of the username@.domain.com in COL2

I also found out that once I populate COL2 correctly, I need to CLEAR any field in COL2 that does not adhere to "username@.domain.com" and then change domain to correctdomain so that all of the poplated fields in COL2 will read username@.correctdomain.com|||Problem is... I still do not have the .com part of the username@.domain.com in COL2well, that's easy, just concatenate '.com' onto the end! ;)|||Thanks.. what about the fields that got pulled over that don't adhere to the username@.Domain.com criteria? Is there a way to clear any fields that are not LIKE username@.Domain.com ?|||What do you have beside DOMAIN\username in the column?

select COl1, COL2
from daTable
where COL1 not like 'DOMAIN%'|||My COL2 is now correct. all fields have username@.DOMAIN.com

What I need to do is clear or delete any fields that do not have specially
username@.domain.com

in other words. I need to keep all fields in COL2 that are LIKE @.DOMAIN.com
and delete all others (some are username@.anotherdomain.com or username@.yetanotherdomain.com)

I tried
Delete from MYTABLE
Where COL2 LIKE '%@.anotherdomain.com%'

but that deleted the whole row.. I need to just clear the field in COL2|||Try this:

UPDATE myTable
SET COL2 = NULL
WHERE COL2 NOT LIKE '%@.theDomainRecordsToKeep.com'|||THANK YOU!!!

This is the code I ended up using..

UPDATE MYTABLE
SET COL2= PARSENAME(REPLACE(COL1, '\', '.'), 1) + '@.' +
PARSENAME(REPLACE(COL1, '\', '.'), 2) + '.com'
WHERE COL1 LIKE '%DOMAIN\%'
go
UPDATE MYTABLE
SET COL2 = Replace(COL2,'DOMAIN','CORRECTDOMAIN')
go

Seems to work..