Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Computed Columns 1 Expand / Collapse
Author
Message
Posted Friday, February 3, 2012 4:01 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
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.
Post #1246807
Posted Tuesday, February 7, 2012 8:04 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, 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
Post #1248141
Posted Tuesday, February 7, 2012 12:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 6,133, Visits: 8,396
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
Post #1248420
Posted Friday, January 11, 2013 5:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 1, 2014 4:08 AM
Points: 987, Visits: 567
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..
Post #1405920
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse