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


Computed Columns


Computed Columns

Author
Message
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
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
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 1499
I hate the Select all that apply questions.

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

Thanks

Tom Garth
Vertical Solutions

"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
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
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
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
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
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: 2817 Visits: 4152
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
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1047 Visits: 2673
'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
jvanderberg
SSC-Addicted
SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)

Group: General Forum Members
Points: 422 Visits: 746
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
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
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? Cool
UMG Developer
UMG Developer
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: 2280 Visits: 2204
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
Noel McKinney
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2017 Visits: 796
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.
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