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 |
|
majkinetor
Yak Posting Veteran
55 Posts |
 Posted - 2008-10-31 : 09:48:39
|
| HelloI have computed column which expression uses functions. Expression itself reference several other columns. Some of those columns are foreign keys. Expression contains functions that get values from other tables based on those foreign keys. So I have something likeCalculate( GetValue1( FK_COLNAME1 ), COLNAME1, GetValue2( FK_COLNAME2 ))GetValue functions just return appropriate column value based on key, from specific table.Since calculation does several selects from different tables I am set it up as PERSISTENT to avoid recalculation every time.However I am not sure when will that column actually be updated. Column should be recalculated if any of the COLNAME_N change or any of the RELATED columns (or rows) change.In my case, related rows will never change, and this also will work on part of the database where ALL referenced columns are static.So, I want to know if PERSISTED is enough in this case to avoid recalculation or does this setup has some side-efects. I am particualary conserned because I use functions that depend on other tables so I am not sure if MS SQL can figure out when it should update value correctly. All the examples and articles on the net I found don't use functions that reference other tables.Thanks.www.r-moth.com http://r-moth.deviantart.com |
|
|
|
|
|
Persistent computed columns and functions