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
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29541 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 Guru
SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)

Group: General Forum Members
Points: 122847 Visits: 13344
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.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1913 Visits: 933
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
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29650 Visits: 12832
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10904 Visits: 3476
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?
Iulian -207023
Iulian -207023
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3829 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
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5057 Visits: 1239
Tricky question - only 46% correct!
Mighty
Mighty
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5733 Visits: 1837
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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2291 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 Guru
SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)

Group: General Forum Members
Points: 122847 Visits: 13344
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