SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Computed Columns


Computed Columns

Author
Message
JohnG69
JohnG69
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1907 Visits: 462
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
hrvoje.piasevoli
hrvoje.piasevoli
SSC Eights!
SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)

Group: General Forum Members
Points: 915 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 iPhoneWink
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
Paul White
Paul White
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80620 Visits: 11400
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 iPhoneWink
Paul could you quote yourself please?

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



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Toreador
Toreador
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5989 Visits: 8281
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 :-D
hakan.winther
hakan.winther
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4149 Visits: 615
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
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
rtelgenhoff
rtelgenhoff
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

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