January 30, 2012 at 8:56 pm
Comments posted to this topic are about the item Computed Columns 1
January 30, 2012 at 11:28 pm
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2012 at 12:14 am
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. "
January 31, 2012 at 12:53 am
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. :crying:
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.
January 31, 2012 at 1:19 am
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. :crying:
+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?
January 31, 2012 at 1:44 am
Iep, tricky question. I like it.
I should have thought about using a function.
Thank you,
Iulian
January 31, 2012 at 2:01 am
Tricky question - only 46% correct!
January 31, 2012 at 2:05 am
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.
January 31, 2012 at 2:07 am
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
January 31, 2012 at 2:19 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2012 at 2:22 am
In my reading, you can't reference a column, you can reference an UDF only. :angry:
January 31, 2012 at 2:28 am
Hugo Kornelis (1/31/2012)
"Is this a question about the official rules and limitations, or about workarounds?" -- I guessed wrong. :crying:
As the question is worded it seems about the official rules:
- referencing column in another table as such is not possible
- getting values from column in another table somehow is possible.
I knew about latter but wording got me :doze:
January 31, 2012 at 2:33 am
I agree wholeheartedly with Daniel Fountain. SQLServerCentral's Question of the Day really should not be about trickery.
I entered "No", but at the back of my mind, I was considering whether there might be a relatively obscure trick here that I had missed.
Get rid of the tricksters in our business, that's what I say.
Ken.
You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer
January 31, 2012 at 2:45 am
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. :crying:
+1
Guess it's down to language subtleties again--while BOL uses 'compute' to describe the behavior, the author of the question used 'reference' (with 'not persisted' being irrelevant for the scope of the question). So from a language perspective, that's two different things/meanings...
Thanks for an interesting question that sparked an even more interesting and enlightening discussion.
PS: And no, I do not want my point back.
January 31, 2012 at 2:59 am
The way I interpreted the question, it was all about whether or not computed column definitions can contain references to columns of other tables. The answer, according to Books On Line is NO.
Yes, there is a workaround, but it's just that - a workaround. It's not really a valid answer to this question because the computed column definition referenced a UDF - not an external column.
I have an issue when the whole purpose of the question is to trick the reader. A rephrasing of the question could have avoided the second-guessing of intentions.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply