InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 06:11:09 GMT20http://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxIn 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.Sat, 02 Oct 2010 16:57:54 GMTrtelgenhoffhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxNice 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.Mon, 16 Aug 2010 06:12:59 GMThakan.wintherhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxI 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 :-DMon, 26 Jul 2010 03:06:35 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx[quote][b]hrvoje.piasevoli (7/23/2010)[/b][hr]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?[/quote]Will this do?[url]http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596424.aspx[/url]Fri, 23 Jul 2010 17:50:07 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxI 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: [url]http://www.sqlservercentral.com/Forums/Topic737008-360-1.aspx#bm738974[/url]Fri, 23 Jul 2010 11:40:20 GMThrvoje.piasevolihttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxVery 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.JGhaliFri, 23 Jul 2010 07:33:41 GMTjghalihttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx[quote][b]jghali (7/21/2010)[/b][hr]Sorry about the ambiguous answer. What I was trying to get to is... Retrieving data from a normal column is much faster then having to calculate the value every time a computed field is called.Can you imagine, if you are calling 100 000 rows including a computed column? It would definitely take more resources specially if the computed column comes from other tables via UDFs...[/quote]Hi jghali,First, thanks for contributing a question, in spite of the harsh comments so often targeted at QotD contributers. Please submit more in the future!On your (rhetoric?) question: "Can you imagine, if you are calling 100 000 rows including a computed column? It would definitely take more resources (...)" (and I omitted the last part if this quote on purpose) - I have to disagree. SQL Server performance is almost always I/O bound; the CPU spends enormous amounts of time waiting for the next data to be available. Since a computed column is not stored, it takes less disk space, reducing I/O. Here is a very unlogical and exaggerated example:[code="sql"]CREATE TABLE Demo (KeyCol int NOT NULL PRIMARY KEY, SmallString varchar(10) NOT NULL, Repetitions smallint NOT NULL, LongString AS REPLICATE(SmallString, Repetitions));[/code]If the LongString column is computed and not persisted, rows take approximately 20-30 bytes (I don't have the exact numbers, as I'm on holiday); a single data page (8K) will store approximately 300 rows. The 100,000 rows you mention will be on less than 350 data pages.However, if LongString is a persisted computed column or a regular column, then the amount of bytes per row depends on the length of SmallString and the value of Repetitions. If we assume an average length of 5 bytes for SmallString and an average value of 200 Repetitions, the average length of LongString will be 1,000 bytes; the average row length then is 1,020-1,030 bytes, so we can squeeze only 7 or 8 rows in a data page. The same 100,000 rows now take over 13,000 data pages! This will take much longer for SQL Server to read and process.The last part of your post, the part I previously omitted from my quote, is "specially if the computed column comes from other tables via UDFs...". This is indeed true. SQL Server can't optimize this very well; it will execute the UDF 100,000 times, so if the UDF has to read from another table, that read will be repeated 100,000 times. Extremely bad for performance. This is but one of the many reasons why using a UDF in a computed column is not recommended.Thu, 22 Jul 2010 15:32:28 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxHow many check boxes are too many? :w00t:"The engine tweaks to be performant."I ticked that.PaulThu, 22 Jul 2010 13:09:44 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxNice question, made me think and I learned. I didn't know about the part of using udf's.Thu, 22 Jul 2010 09:32:33 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxThanks for the question. I learned something today!!Thu, 22 Jul 2010 08:37:18 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxagree completely, we use computed columns for getting the full name from firstname, lastname combination...[quote][b]wware (7/21/2010)[/b][hr][quote][b]forjonathanwilson (7/21/2010)[/b][hr]A calculated field is not going to be A+B, because thats just a stupid test case. More likely a computed field will be connected to a UDF and be gathering data from elsewhere. [/quote]Not in my experience! Computed columns are often used in data warehousing for mundane things like FullName and Age, two examples mentioned by others in this discussion.[/quote]Thu, 22 Jul 2010 01:10:10 GMTziangijhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx[quote][b]jghali (7/21/2010)[/b][hr]We use them here for Total Calculations using UDF functions.But since we are developping the application, we are beeing very careful that they are only used when there aren't too many columns returned... [/quote]I think it's more likely to be the UDFs (assuming they are scalar) that are the problem with many rows, since UDFs are calculated for each row, effectively making the query incur a cursor-like performance penalty. This is a problem I frequently run into when investigating slow queries on large numbers of rows.You can reduce ore eliminate the negative effect either by persisting and indexing the computed column (assuming the UDF is deterministic) or by using a different method. Using a view won't help if the view contains the same scalar UDF as the computed column.DuncanWed, 21 Jul 2010 15:30:25 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxGood one....got it wrong....I have to do some more reading.Wed, 21 Jul 2010 12:36:00 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxoops... meant number of rows returned... Wed, 21 Jul 2010 12:13:42 GMTjghalihttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxWe use them here for Total Calculations using UDF functions.But since we are developping the application, we are beeing very careful that they are only used when there aren't too many columns returned... For example, When calling an invoice.Otherwise we use views for reporting purposes.Not sure if this is recommended or not. But this is how it is done here and now they are starting to use LINQ... Not familiar with it. I might try to understand it and create a new question?!?!Wed, 21 Jul 2010 12:12:36 GMTjghalihttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx[quote][b]forjonathanwilson (7/21/2010)[/b][hr]A calculated field is not going to be A+B, because thats just a stupid test case. More likely a computed field will be connected to a UDF and be gathering data from elsewhere. [/quote]Not in my experience! Computed columns are often used in data warehousing for mundane things like FullName and Age, two examples mentioned by others in this discussion.Wed, 21 Jul 2010 12:04:39 GMTRose Budhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxThanks 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.Wed, 21 Jul 2010 11:06:13 GMTNoel McKinneyhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxI'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:[code="sql"]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()));[/code]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.)Wed, 21 Jul 2010 10:12:53 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx[quote][b]JF1081 (7/21/2010)[/b][hr]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.[/quote]Are you sure? :cool:Wed, 21 Jul 2010 09:54:52 GMTpjdillerhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx[quote][b]Nadrek (7/21/2010)[/b][hr]'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. [/quote]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.Wed, 21 Jul 2010 09:36:35 GMTjvanderberghttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx'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.Wed, 21 Jul 2010 09:27:36 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx[quote][b]Duncan Pryde (7/21/2010)[/b][hr][quote][b]Duncan Pryde (7/21/2010)[/b][hr]- There may be yet other alternatives of course, but I can't think what they might be off the top of my head.[/quote]Oh wait - there's the old standby of "Just let the client application sort it out" - forgot about that one!Duncan[/quote]Thanks, Duncan. Letting the client app sort it out is been my experience but I can see computed columns cleaning up code.Wed, 21 Jul 2010 09:11:04 GMTOCTomhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx[quote][b]Duncan Pryde (7/21/2010)[/b][hr]- There may be yet other alternatives of course, but I can't think what they might be off the top of my head.[/quote]Oh wait - there's the old standby of "Just let the client application sort it out" - forgot about that one!DuncanWed, 21 Jul 2010 08:52:55 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx[quote][b]OCTom (7/21/2010)[/b][hr]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.[/quote]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.DuncanWed, 21 Jul 2010 08:50:33 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxI hate the [i]Select all that apply[/i] questions.Never the less, it was interesting and I learned something.ThanksWed, 21 Jul 2010 08:39:35 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxperfect! 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... :-)Wed, 21 Jul 2010 08:33:01 GMTjghalihttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxThanks for the question Jghali. Didn't know about the UDF part, so I picked up something new today!ChadWed, 21 Jul 2010 08:20:06 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxI got it right, and I think that the question was pretty obviously referring to CPU resources on access of the field. A calculated field is not going to be A+B, because thats just a stupid test case. More likely a computed field will be connected to a UDF and be gathering data from elsewhere. So yes, it will be a hit on every access as opposed to initial write only.Wed, 21 Jul 2010 08:20:01 GMTforjonathanwilsonhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxIt's a great question and something that needed research on my part. Thanks for the question. Has anyone used computed columns and why? Thanks.Wed, 21 Jul 2010 08:18:40 GMTOCTomhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxLol, 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.Wed, 21 Jul 2010 07:59:49 GMTJF1081http://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx[quote][b]jghali (7/21/2010)[/b][hr]I also would like to add.The QoD is a great way to learn... I've learned so much answering and reading answers.I would like to thank SQLServerCentral for this great service.I say this because many people are very fast on the trigger in blasting the questions and answers not reading what has already been written in the comments section... It isn't easy to write a question and far more difficult to please everyone... I don't mind the Instructive comments I actually like them very much as we learn a lot more there too... So I would like to thank all of your comments... they were instructive to some point.In my case, I don't really care about points, there's nothing more interesting than feeding the brain... ;-)I love it!!! :w00t:Thanks again for all your comments and take care :-)[/quote]I agree with pretty much all of this. Thanks again for the question, and I hope you'll be encouraged to do more. I have to say that personally, I prefer this type of question to the "What will be the result of this query" type questions, and it may be that this type is more difficult to compose. I'll have to try doing a couple myself one day.Keep up the good work.DuncanWed, 21 Jul 2010 06:56:10 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxHow can one get it right without taking all morning...? I have other things to do too. :-)Percentage that got it right when I took it was a handsome 3%. Though, I think most know what a computed column is all about.Take it with a grain of salt. I still respect anyone who takes the time to put a question together.Wed, 21 Jul 2010 06:52:56 GMTpjdillerhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxThat's one of the biggest challenges when writting a question. Everything depends in sql server when talking about performance. And if you give out too many details then the right answer(s) becomes extremely obvious.Wed, 21 Jul 2010 06:49:32 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxI also would like to add.The QoD is a great way to learn... I've learned so much answering and reading answers.I would like to thank SQLServerCentral for this great service.I say this because many people are very fast on the trigger in blasting the questions and answers not reading what has already been written in the comments section... It isn't easy to write a question and far more difficult to please everyone... I don't mind the Instructive comments I actually like them very much as we learn a lot more there too... So I would like to thank all of your comments... they were instructive to some point.In my case, I don't really care about points, there's nothing more interesting than feeding the brain... ;-)I love it!!! :w00t:Thanks again for all your comments and take care :-)Wed, 21 Jul 2010 06:45:35 GMTjghalihttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxSorry about the ambiguous answer. What I was trying to get to is... Retrieving data from a normal column is much faster then having to calculate the value every time a computed field is called.Can you imagine, if you are calling 100 000 rows including a computed column? It would definitely take more resources specially if the computed column comes from other tables via UDFs... I have to admit that the "Always" in the answer might have made the answer ambiguous... maybe it should have said "in most cases"We now use the computed columns here with caution... We only reference computed columns in procedures for which we know will never return many many rows.Wed, 21 Jul 2010 06:32:50 GMTjghalihttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxOne more vote for the definitly use more ressource "issue thingie".Using C or a+b in the query will have no difference at all. There will be a small hit if you have a udf that does (a+b) without selecting from the base tables. But you'll get a massive hit if you query data from other tables.So, again, definitly is iffie at best without context.Other than that awesome question.Wed, 21 Jul 2010 06:23:13 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxoverall a good question, just the bit about resource usage that appears incomplete, Stating "Will definately use more resources" is the same as asking how long a piece of string is. Lack of equable context renders it vague.Wed, 21 Jul 2010 04:39:19 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspxA good question, apart from the ambiguity surrounding the resources question. Since it's calculated at runtime, it doesn't use any disk resources, and I can't see how having a select statement along the lines of SELECT a + b AS C, <other_columns> FROM <table> would use less resources than SELECT C, <other_columns> FROM <table> when C is a computed column from a + b.Obviously, if you're using scalar UDF's in non-persisted computed columns, there's a pretty good chance you'll be hit by performance problems somewhere down the line - as I've just discovered on one of our systems, so perhaps that was it?DuncanWed, 21 Jul 2010 01:19:59 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx[quote]I'm curious did you test and find that using non-persisted columns was faster than pre-calculating and storing all the permutations that you needed? (I guess it would probably depend on the storage system speed and a number of other variables.)[/quote]I find the aproach more manageable than using an SP and cleaner from the design point of view. And I can always make some other column persisted by a simple alter statement. (well not realy - has to be deterministic which is not the case with all columns). It is used primarily in warehouse solutions and therefore performance was never really an issue as it is loaded in an SSAS cube and persisted there. It also has a neat effect of being culture and language aware out of the box.Wed, 21 Jul 2010 00:57:03 GMThrvoje.piasevolihttp://www.sqlservercentral.com/Forums/Topic956061-2744-1.aspx...And off it goes posted unfinished (sorry about that).I think it might be a good idea to have a peer-review of ones Q and A before submitting a QoD to avoid frustration we've all whitnessed when people encounter inprecise or ambiguous answers. I personaly don't mind having some points not awarded, but find that questionable QoDs yield more contention in quality of the discussion that follows.Regards,HrvojeWed, 21 Jul 2010 00:31:44 GMThrvoje.piasevoli