Computed Columns

Question

Computed Columns

Viewing 15 posts - 16 through 30 (of 45 total)

You must be logged in to reply to this topic. Login to reply

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 1

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 SSC Eights! Points: 982 More actions · Answer 2

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 SSChampion Points: 11785 More actions · Answer 3

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 SSC Enthusiast Points: 139 More actions · Answer 4

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 SSChampion Points: 11610 More actions · Answer 5

Thanks for the question Jghali. Didn't know about the UDF part, so I picked up something new today!

Chad

JohnG69 Hall of Fame Points: 3114 More actions · Answer 6

perfect! that was my goal.

Since I learned about it, I wanted to share the knowledge.

Since at least one person has learned something, my goal has been reached... 🙂

Tom Garth SSCertifiable Points: 6193 More actions · Answer 7

I hate the Select all that apply questions.

Never the less, it was interesting and I learned something.

Thanks

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 8

OCTom (7/21/2010)
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.

Frequently, and for general ease-of-use reasons mostly.

Say for (a very simple) example you have FirstName and LastName columns, and often need to produce a results set that contains a FullName field of the format FirstName + ' ' + LastName.

You can either:

- Use a view, but that has the disadvantage that you need to rewrite all queries to use the view instead of the table.

- Do it in the select statement each time you need the FullName, but that has the disadvantage that you might have to repeat code in lots of places.

- Create the FullName column and keep it populated with a trigger on the other two columns, but that has an overhead for INSERTs and UPDATEs and perhaps a little for management.

- Use a computed column. Doesn't affect any existing queries, doesn't affect inserts or updates (unless persisted I think, but that's another story) and is a one-time-only operation to set up.

- There may be yet other alternatives of course, but I can't think what they might be off the top of my head.

Horses for courses as they say.

Duncan

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 9

Duncan Pryde (7/21/2010)
- There may be yet other alternatives of course, but I can't think what they might be off the top of my head.

Oh wait - there's the old standby of "Just let the client application sort it out" - forgot about that one!

Duncan

OCTom SSChampion Points: 11785 More actions · Answer 10

Duncan Pryde (7/21/2010)
Duncan Pryde (7/21/2010)
- There may be yet other alternatives of course, but I can't think what they might be off the top of my head.
Oh wait - there's the old standby of "Just let the client application sort it out" - forgot about that one!
Duncan

Thanks, Duncan. Letting the client app sort it out is been my experience but I can see computed columns cleaning up code.

Nadrek SSC-Insane Points: 20039 More actions · Answer 11

'Will definately use more resources', as everyone else has said, is just wrong. CPU resources? Disk resources? Bandwidth, memory, or network resources? At design time, at runtime, compared to nothing or compared to what.

I have experimented with computed columns; in write-once, read-many reporting tables on SQL Server 2000 I found a permanent column with a CHECK constraint to enforce the computation's integrity was a superior solution from a performance standpoint, given adequate disk IO.

In one case, the business users concatenated valuable data into a character type field, so SUBSTRING became very important; changing the core columns was a nonstarter, but I could add one.

jvanderberg SSCertifiable Points: 6762 More actions · Answer 12

Nadrek (7/21/2010)
'Will definately use more resources', as everyone else has said, is just wrong. CPU resources? Disk resources? Bandwidth, memory, or network resources? At design time, at runtime, compared to nothing or compared to what.

I'm wondering if this is accurate as well. Assuming C = A + B, would "SELECT C FROM [Table]" be slower than "SELECT A + B FROM [Table]"? Would "SELECT A,B FROM [Table]" be any slower just because of the presence of a computed column. I don't think they would, but I have nothing to substantiate it. I'd like to see some proof either way, I'm fairly curious.

pjdiller SSC Eights! Points: 977 More actions · Answer 13

JF1081 (7/21/2010)
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.

Are you sure? 😎

UMG Developer SSChampion Points: 13482 More actions · Answer 14

I'm less worried about the points, though who doesn't like to have more points? 😉

What I am more worried about is that people who answer the question and get it wrong, or right, might take the answers at face value and not look at the discussion, so they will assume that non-persisted computed columns always use more resources, which means you probably shouldn't use them.

It, also, means that there is a different solution to the problem that does use fewer resources, and it would be nice if someone can share what the other solution(s) is/are.

For example as someone else mentioned your non-persisted computed column may use a non-deterministic formula and so you can't persist it.

For example:

CREATE TABLE dbo.Sample

(ID INTEGER PRIMARY KEY,

Post_Date DATETIME,

Ins_Aging_Date DATETIME NULL,

Pat_Aging_Date DATETIME NULL,

Age AS DATEDIFF(DAY, COALESCE(CASE WHEN Ins_Aging_Date < Pat_Aging_Date THEN Ins_Aging_Date ELSE Pat_Aging_Date END, Post_Date), GETDATE()));

Yes, you could let the client calculate the age, but then you are sending three date fields over the network instead of a single integer if all the client wants is the age. (And you have to maintain the formula in a different and possible multiple places.)

Noel McKinney Hall of Fame Points: 3451 More actions · Answer 15

Thanks jghali and keep them coming! I saw this question and knew I'd get it wrong because some parts were vague, but vague questions are what we deal with everyday. To me the important thing about these questions is to make the reader think, perhaps learn something new, and get a discussion going. Good job.