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 Monday, January 30, 2012 8:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
Comments posted to this topic are about the item Computed Columns 1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1244022
Posted Monday, January 30, 2012 11:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
Hmmmm, this is arguable.
MSDN says that it can only use columns for the same table.
And a computed column can't reference a column outside another table, it can reference a UDF which on his part references columns from other table.

So the question is a bit ambigous, as it is not clear if direct or indirect reference is meant.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1244054
Posted Tuesday, January 31, 2012 12:14 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 3:10 AM
Points: 1,470, Visits: 708

The questions should have been a little more specific in it's intent. The BOL and msdn clearly says that the computed columns cannot explicitly refer columns outside the source table.

http://msdn.microsoft.com/en-us/library/ms191250.aspx

"A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery. "
Post #1244066
Posted Tuesday, January 31, 2012 12:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
Meh, I don't like questions where people who really understand the subject have a 50/50 chance of getting it right because they have to second-guess the author's intention.

"Is this a question about the official rules and limitations, or about workarounds?" -- I guessed wrong.


To add to the explanation: just because you can doesn't mean you should. There are good reasons why the only way to reference another table is through a workaround that's complicated enough to fool SQL Server. Querying a table with such a computed column is dog-slow, since the UDF will be evaluated once for each row. This does not show on the execution plan or in the SET STATISTICS IO output, but you can see it when using Profiler. I checked the sample code that the explanation references, and Profiler shows that the CCTest table is scanned once (obvously) - and that there are five full table scans of the LeaveBalance table. Proper indexing would change that to five index seeks, but it will still have a drastic effect on performance if the row count goes above five.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1244077
Posted Tuesday, January 31, 2012 1:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:57 AM
Points: 2,587, Visits: 2,443
Hugo Kornelis (1/31/2012)
Meh, I don't like questions where people who really understand the subject have a 50/50 chance of getting it right because they have to second-guess the author's intention.

"Is this a question about the official rules and limitations, or about workarounds?" -- I guessed wrong.


+1

Please, my points back.
The question should be:
Can a non persisted computed column call an UDF that reference column(s) that are NOT in the same table?
Post #1244091
Posted Tuesday, January 31, 2012 1:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 13, 2014 3:36 AM
Points: 1,012, Visits: 1,000
Iep, tricky question. I like it.
I should have thought about using a function.

Thank you,
Iulian
Post #1244111
Posted Tuesday, January 31, 2012 2:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 24, 2014 5:46 AM
Points: 1,416, Visits: 804
Tricky question - only 46% correct!
Post #1244122
Posted Tuesday, January 31, 2012 2:05 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:01 AM
Points: 3,329, Visits: 1,323
I agree on the opinion of quite a few others that the question is not clear enough, and that the answer is incorrect (even though I answered it "correctly").
You can indirectly make use of values from other tables, but in order to do so you have make a reference to a UDF.
Post #1244124
Posted Tuesday, January 31, 2012 2:07 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, November 7, 2014 7:31 AM
Points: 769, Visits: 854
I must admit i dont like the questions which contain trickery.

However on the flip side - its interesting to see how people have got round these limitations. However i dont really think they should get round them.

Dan
Post #1244126
Posted Tuesday, January 31, 2012 2:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
Mighty (1/31/2012)
You can indirectly make use of values from other tables, but in order to do so you have make a reference to a UDF.


That is a good way to describe what happens, unambigously.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1244134
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse