SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Computed Columns 1


Computed Columns 1

Author
Message
Revenant
Revenant
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12249 Visits: 5010
cengland0 (2/3/2012)
jlennartz (2/3/2012)
Good question as it led to alot of interesting discussion which is usually the most interesting part of the QoTD's.
I learn much more through the QOTD discussions than I do with the QOTD itself. Many of the experts show better ways of doing things, point out that the code doesn't work in their country, explains that it only works with specific default options set, and sometimes provides better URL references than the included BOL links.

Tha's why I do QotD every weekday, any why I could justify to my boss that it is productive time.
Peter Trast
Peter Trast
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1036 Visits: 655
Sometimes, having LESS experience is helpful. I took the question at face value with no regard for semantics.

"Can a non persisted computed column reference column(s) that are NOT in the same table as the computed column.?"

I saw no reason it could not, but the question got me thinking... WHY would it be bad to reference columns from another table? I began to think about normalization and the fact that maybe the computed column might belong somewhere else, especially if ALL the columns in the UDF came from the same table. I cannot think of a real world example right now where this would make sense. That is just the path my brain went down...

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18389 Visits: 12426
Peter Trast (2/7/2012)
I cannot think of a real world example right now where this would make sense.


Real-world examples are easy enough to find. For instance, the current balance of an account can be calculated from the opening balance (both in the accounts table) and the sum of all tranactions (different table).

But there are technical challenges for allowing this as a computed column. If it's not persisted, any access to the table would automatically involve access to several rows in the other table. And if it is persisted, any change to the table of transactions would involve changing the current balance in the accounts table. This would be a fairly simple change to propagate (which is why you could implement this as an indexed view), but there are lots of scenarios where propagating changes to persisted computed columns becomes a lot more complex. (I know - because I get paid to think about and solve this kind of issues).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Dineshbabu
Dineshbabu
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1430 Visits: 569
Since i'm not well versed with computed columns , referred BOL and answered NO. But SSC kicked me out saying Wrong.

But after reading all the threads posted here by experts says I'm correct.

--
Dineshbabu
Desire to learn new things..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search