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 Friday, July 23, 2010 7: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
Very well said Hugo.

That's pretty much what I was trying to say although I wasn't thinking of Space (since today, disk space is pretty cheap)... your explanation is excellent.

Thank you for clarifying.
JGhali
Post #957954
Posted Friday, July 23, 2010 11:40 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:33 PM
Points: 361, Visits: 510
I guess now would be a good time to point to a great post by PaulWhiteNz about UDFs and SCHEMABINDING which I can not find right now. My read performance is quite good while write sucks as I am on my iPhone;)
Paul could you quote yourself please?

Here is the link to a discussion on SSC: http://www.sqlservercentral.com/Forums/Topic737008-360-1.aspx#bm738974


Hrvoje Piasevoli
Post #958141
Posted Friday, July 23, 2010 5:50 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 8:13 AM
Points: 9,926, Visits: 11,188
hrvoje.piasevoli (7/23/2010)
I guess now would be a good time to point to a great post by PaulWhiteNz about UDFs and SCHEMABINDING which I can not find right now. My read performance is quite good while write sucks as I am on my iPhone;)
Paul could you quote yourself please?

Will this do?
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596424.aspx




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #958336
Posted Monday, July 26, 2010 3:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:09 AM
Points: 1,777, Visits: 6,452
I hate "me too" replies, but I also failed on the "Will definitely use more resources" option, because it is just not true. Consider the alternative of a standard column populated by a trigger. A trigger will update the column on every insert and every relevant update; whereas the computed column will only be evaluated when it is selected. If you never include the column in a select statement then how can it use more resources? And yes I know that you wouldn't create a computed column that you don't intend to use, but a seldom-accessed computed column is still going to use fewer resources than a trigger.

I didn't choose "The engine tweaks to be performant." as I have no idea what it means!

Final whinge, it's a shame that these multiple-check-box question give the marks on an all-or-nothing basis; choosing 4 out of 5 correct options scores the same as choosing no correct options but every incorrect one
Post #958651
Posted Monday, August 16, 2010 6:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 2,604, Visits: 572
Nice question, and as everyone else pointed out, the computed columns doesn't need to user more resources. Hugo gave an excellent example on how it could use less resources.

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Post #969708
Posted Saturday, October 2, 2010 4:57 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 11:01 AM
Points: 703, Visits: 327
In the example,

create table a
(
col1 int,
col2 int,
col3 as col1,
)

col3 is technically a computed column, but uses only an extremely low amount of cpu time, especially since it does only a simple in-memory copy operation, but since it's not read from a disk buffer, is probably actually more efficient than a normal column. The statement 'definitely uses more resources' may be a stretch. I would agree that an expression utilizing a SELECT or UDF would use more resources and could potentially cause performance issues.
Post #997251
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse