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


Computed Columns 1


Computed Columns 1

Author
Message
bitbucket-25253
bitbucket-25253
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11567 Visits: 25280
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
Koen Verbeeck
Koen Verbeeck
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43828 Visits: 13288
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Ramesh Velayudhan
Ramesh Velayudhan
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1847 Visits: 914
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. "
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15606 Visits: 12326
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6546 Visits: 3369
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?

I run on tuttopodismo
Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2025 Visits: 1248
Iep, tricky question. I like it.
I should have thought about using a function.

Thank you,
Iulian
paul s-306273
paul s-306273
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3102 Visits: 1147
Tricky question - only 46% correct!
Mighty
Mighty
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4761 Visits: 1721
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.
Daniel Fountain
Daniel Fountain
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1143 Visits: 890
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
Koen Verbeeck
Koen Verbeeck
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43828 Visits: 13288
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
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