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


Computed Columns


Computed Columns

Author
Message
JohnG69
JohnG69
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1091 Visits: 453
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
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 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-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35394 Visits: 11361
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3828 Visits: 8123
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3315 Visits: 612
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
Right there with Babe
Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)

Group: General Forum Members
Points: 748 Visits: 366
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