SQL Clone
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 Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66373 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
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: 1083 Visits: 453
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
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: 1083 Visits: 453
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 Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66373 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 (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4156 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 (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)

Group: General Forum Members
Points: 490 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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4115 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
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4323 Visits: 18732
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