Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Computed Columns


Computed Columns

Author
Message
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
One more vote for the definitly use more ressource "issue thingie".

Using C or a+b in the query will have no difference at all. There will be a small hit if you have a udf that does (a+b) without selecting from the base tables. But you'll get a massive hit if you query data from other tables.

So, again, definitly is iffie at best without context.



Other than that awesome question.
JohnG69
JohnG69
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 452
Sorry about the ambiguous answer.

What I was trying to get to is...

Retrieving data from a normal column is much faster then having to calculate the value every time a computed field is called.
Can you imagine, if you are calling 100 000 rows including a computed column? It would definitely take more resources specially if the computed column comes from other tables via UDFs...

I have to admit that the "Always" in the answer might have made the answer ambiguous... maybe it should have said "in most cases"

We now use the computed columns here with caution... We only reference computed columns in procedures for which we know will never return many many rows.
JohnG69
JohnG69
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 452
I also would like to add.

The QoD is a great way to learn... I've learned so much answering and reading answers.
I would like to thank SQLServerCentral for this great service.

I say this because many people are very fast on the trigger in blasting the questions and answers not reading what has already been written in the comments section... It isn't easy to write a question and far more difficult to please everyone... I don't mind the Instructive comments I actually like them very much as we learn a lot more there too... So I would like to thank all of your comments... they were instructive to some point.

In my case, I don't really care about points, there's nothing more interesting than feeding the brain... ;-)

I love it!!! w00t

Thanks again for all your comments and take care :-)
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
That's one of the biggest challenges when writting a question. Everything depends in sql server when talking about performance. And if you give out too many details then the right answer(s) becomes extremely obvious.
pjdiller
pjdiller
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 291
How can one get it right without taking all morning...? I have other things to do too. :-)

Percentage that got it right when I took it was a handsome 3%. Though, I think most know what a computed column is all about.

Take it with a grain of salt. I still respect anyone who takes the time to put a question together.
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 1552
jghali (7/21/2010)
I also would like to add.

The QoD is a great way to learn... I've learned so much answering and reading answers.
I would like to thank SQLServerCentral for this great service.

I say this because many people are very fast on the trigger in blasting the questions and answers not reading what has already been written in the comments section... It isn't easy to write a question and far more difficult to please everyone... I don't mind the Instructive comments I actually like them very much as we learn a lot more there too... So I would like to thank all of your comments... they were instructive to some point.

In my case, I don't really care about points, there's nothing more interesting than feeding the brain... ;-)

I love it!!! w00t

Thanks again for all your comments and take care :-)


I agree with pretty much all of this. Thanks again for the question, and I hope you'll be encouraged to do more. I have to say that personally, I prefer this type of question to the "What will be the result of this query" type questions, and it may be that this type is more difficult to compose. I'll have to try doing a couple myself one day.

Keep up the good work.

Duncan
JF1081
JF1081
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 322
Lol, I saw the word "definitely" and immediately thought: nope, it depends. I have no substantial backing for that thought as I know nothing about computed columns, but I definitely know that nothing is definite.
OCTom
OCTom
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2813 Visits: 4152
It's a great question and something that needed research on my part. Thanks for the question.

Has anyone used computed columns and why?

Thanks.
forjonathanwilson
forjonathanwilson
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 33
I got it right, and I think that the question was pretty obviously referring to CPU resources on access of the field. A calculated field is not going to be A+B, because thats just a stupid test case. More likely a computed field will be connected to a UDF and be gathering data from elsewhere. So yes, it will be a hit on every access as opposed to initial write only.
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2438 Visits: 18649
Thanks for the question Jghali. Didn't know about the UDF part, so I picked up something new today!

Chad
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