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 8:33 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
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...
Post #956403
Posted Wednesday, July 21, 2010 8:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #956408
Posted Wednesday, July 21, 2010 8:50 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: Sunday, August 17, 2014 2:48 PM
Points: 3,352, Visits: 1,481
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
Post #956421
Posted Wednesday, July 21, 2010 8:52 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: Sunday, August 17, 2014 2:48 PM
Points: 3,352, Visits: 1,481
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
Post #956424
Posted Wednesday, July 21, 2010 9:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 2,555, Visits: 3,809
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.
Post #956444
Posted Wednesday, July 21, 2010 9:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:54 AM
Points: 865, Visits: 2,381
'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.

Post #956459
Posted Wednesday, July 21, 2010 9:36 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:31 AM
Points: 419, Visits: 725
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.
Post #956471
Posted Wednesday, July 21, 2010 9:54 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
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?
Post #956487
Posted Wednesday, July 21, 2010 10:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.)
Post #956502
Posted Wednesday, July 21, 2010 11:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
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.
Post #956545
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse