Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Computed Columns Expand / Collapse
Author
Message
Posted Wednesday, July 21, 2010 6:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
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.
Post #956288
Posted Wednesday, July 21, 2010 6:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:16 AM
Points: 434, Visits: 202
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.

Post #956296
Posted Wednesday, July 21, 2010 6:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:16 AM
Points: 434, Visits: 202
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!!!

Thanks again for all your comments and take care
Post #956303
Posted Wednesday, July 21, 2010 6:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
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.
Post #956309
Posted Wednesday, July 21, 2010 6:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, March 15, 2014 1:45 PM
Points: 405, Visits: 286
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.
Post #956310
Posted Wednesday, July 21, 2010 6:56 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:31 AM
Points: 3,352, Visits: 1,490
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!!!

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
Post #956315
Posted Wednesday, July 21, 2010 7:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 8:13 AM
Points: 436, 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.
Post #956359
Posted Wednesday, July 21, 2010 8:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 2,667, Visits: 4,091
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.
Post #956378
Posted Wednesday, July 21, 2010 8:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 8:24 AM
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.
Post #956379
Posted Wednesday, July 21, 2010 8:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:03 PM
Points: 2,396, Visits: 18,068
Thanks for the question Jghali. Didn't know about the UDF part, so I picked up something new today!

Chad
Post #956380
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse