February 11, 2019 at 10:18 pm
Comments posted to this topic are about the item Using Computed Columns
February 12, 2019 at 7:56 am
Why not use a view for computed columns? Is this not clearer?
February 12, 2019 at 8:07 am
Good article and great topic. I'm a big fan of computed columns so it's nice to see a detailed article on the topic. I would warn, however, against using a T-SQL scalar UDF for a computed column if performance is important. Once a scalar UDF's (pre-SQL 2019) becomes part of the table queries that involve that table will become slower. For starters, queries involving that table can no longer get a parallel execution plan even when that column is not referenced! Even index rebuilds must be performed with a serial plan. Based on my testing, non-inline Scalar UDFs bring these kind of problems when used for computed columns or as constraints (Check & Default). Note these two articles by Eric Darling:
Another Hidden Parallelism Killer: Scalar UDFs In Check Constraints
Another reason why scalar functions in computed columns is a bad idea
Again - I enjoyed the article and am looking forward to the next one. I'm not being critical of it in any way, I just wanted to make people aware of the performance implications of using scalar UDFs for this. A correctly developed CLR, on the other hand, does not have this problem. Fortunately, too, SQL 2019 adds inline scalar UDFs which solves this problem on the T-SQL side.
-- Itzik Ben-Gan 2001
February 12, 2019 at 8:48 am
Hi Mike,
There is no universal solution for every situation. Computed columns are another posibility like views.
When you use computed columns you can persist the information so you will have it stored whith the rest of the table. This way you get better performance when accesing the column and also you reduce the number of objects to administer. Computed columns can be used in some constraints what can also be an advantage.
As usual, neither of the two options can always be used, so it will be necessary to see each particular case to find which of the two options is the one that best fits.
February 12, 2019 at 9:10 am
Hi Alan,
Thank you very much for the clarification. The idea was mainly to show the possibilities of the use of calculated columns, but you are right that it's necessary to be careful with the performance according to the solution that we are going to adopt.
February 12, 2019 at 11:14 am
Mike D - Tuesday, February 12, 2019 7:55 AMWhy not use a view for computed columns? Is this not clearer?
Our software vendor uses a lot of integer columns for status levels. For example, in the Customer table, the CustomerStatus can be 0,1,2 or 3. They then have a CustomerStatusComputed column for the convenience of anyone that's writing queries or reports. This column shows 'Open','Hold','Closed' or 'Prospect' respectively. This column acts as a sort of anchor or index location for human eyes. Yes, a view can be used and many views already exist with this exact information but, to me, it's much more convenient to look at the table for this information.
February 12, 2019 at 11:51 am
Mike D - Tuesday, February 12, 2019 7:55 AMWhy not use a view for computed columns? Is this not clearer?
I think that they're both equally clear. Views are good for complex computations, e.g., ones involving sub-queries, but I think they're overkill if you are just doing simple computed columns.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 12, 2019 at 11:57 am
We have a database that is filled with tables utilizing an integer column for things like 'Status'. For example, Customer table has a Status column and 0-3 are the integers used in this column. It also has a StatusComputed column that's computed as (case [Status] when 0 then 'Open' when 1 then 'Hold' when 2 then 'Closed' when 3 then 'Prospect' else 'Unknown' end). Without doing any testing I'd have to guess that the query optimizer would perform better with a query that has WHERE Status = 0 rather than WHERE StatusComputed = 'Open'. Does anyone have any opinions?
February 12, 2019 at 12:26 pm
thisisfutile - Tuesday, February 12, 2019 11:57 AMWe have a database that is filled with tables utilizing an integer column for things like 'Status'. For example, Customer table has a Status column and 0-3 are the integers used in this column. It also has a StatusComputed column that's computed as (case [Status] when 0 then 'Open' when 1 then 'Hold' when 2 then 'Closed' when 3 then 'Prospect' else 'Unknown' end). Without doing any testing I'd have to guess that the query optimizer would perform better with a query that has WHERE Status = 0 rather than WHERE StatusComputed = 'Open'. Does anyone have any opinions?
I prefer using lookup tables for this. It's much easier to add new rows to a lookup table than it is to modify an existing table if you need to add another status, especially if all code changes need to go through a change control process. (Adding a row isn't usually considered a code change, so it doesn't need to go through the change control process.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 12, 2019 at 12:54 pm
drew.allen - Tuesday, February 12, 2019 12:26 PMthisisfutile - Tuesday, February 12, 2019 11:57 AMWe have a database that is filled with tables utilizing an integer column for things like 'Status'. For example, Customer t has a Status column and 0-3 are the integers used in this column. It also has a StatusComputed column that's computed as (case [Status] when 0 then 'Open' when 1 then 'Hold' when 2 then 'Closed' when 3 then 'Prospect' else 'Unknown' end). Without doing any testing I'd have to guess that the query optimizer would perform better with a query that has WHERE Status = 0 rather than WHERE StatusComputed = 'Open'. Does anyone have any opinions?I prefer using lookup tables for this. It's much easier to add new rows to a lookup table than it is to modify an existing table if you need to add another status, especially if all code changes need to go through a change control process. (Adding a row isn't usually considered a code change, so it doesn't need to go through the change control process.)
Drew
I'm actually not aware of what a lookup table is. Are you referring to a normalized table? Like status is the key field in the Customer table and they reference a lookup table called "CustomerStatus"?
For reference, the DB I'm referring to is maintained by our software developer. It's their design and the ERP software references the Status column (0-3) and displays Open, Hold, Closed, or Prospect based on its C# code. They decided to put the computed columns in for us customers simply for reference. No more need to write a case statement in the select clause, instead just select the StatusComputed column.
I'm interested to know more about lookup tables though. I searched it and my first hit was a Joe Celko article. Since he's not for the faint of heart (and since I fainted) I figured I'd let you respond to point me in the right direction. 😀
February 12, 2019 at 2:05 pm
thisisfutile - Tuesday, February 12, 2019 12:54 PMdrew.allen - Tuesday, February 12, 2019 12:26 PMthisisfutile - Tuesday, February 12, 2019 11:57 AMWe have a database that is filled with tables utilizing an integer column for things like 'Status'. For example, Customer t has a Status column and 0-3 are the integers used in this column. It also has a StatusComputed column that's computed as (case [Status] when 0 then 'Open' when 1 then 'Hold' when 2 then 'Closed' when 3 then 'Prospect' else 'Unknown' end). Without doing any testing I'd have to guess that the query optimizer would perform better with a query that has WHERE Status = 0 rather than WHERE StatusComputed = 'Open'. Does anyone have any opinions?I prefer using lookup tables for this. It's much easier to add new rows to a lookup table than it is to modify an existing table if you need to add another status, especially if all code changes need to go through a change control process. (Adding a row isn't usually considered a code change, so it doesn't need to go through the change control process.)
Drew
I'm actually not aware of what a lookup table is. Are you referring to a normalized table? Like status is the key field in the Customer table and they reference a lookup table called "CustomerStatus"?
For reference, the DB I'm referring to is maintained by our software developer. It's their design and the ERP software references the Status column (0-3) and displays Open, Hold, Closed, or Prospect based on its C# code. They decided to put the computed columns in for us customers simply for reference. No more need to write a case statement in the select clause, instead just select the StatusComputed column.
I'm interested to know more about lookup tables though. I searched it and my first hit was a Joe Celko article. Since he's not for the faint of heart (and since I fainted) I figured I'd let you respond to point me in the right direction. 😀
It's just what it sounds like. You use one value to look up another. Normalization is a separate, but related concept. Lookup tables are usually normalized, but a normalized table isn't necessarily considered a lookup table.
Generally, you have a single code that you look up in a table to get the description. Joe mentions the "One True Lookup Table" in which case, you will need two (or more) codes which will give you a unique combination, but otherwise the idea is the same--you look up the unique combination to get a description.
Also, you have your terminology a little mixed up. The key field of a table MUST be unique. So, status cannot be the "key field in the Customer table," because it is not unique in the customer table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 13, 2019 at 1:34 pm
drew.allen - Tuesday, February 12, 2019 2:05 PMAlso, you have your terminology a little mixed up. The key field of a table MUST be unique. So, status cannot be the "key field in the Customer table," because it is not unique in the customer table.Drew
I'm sorry, I thought 'foreign' was understood, just as 'primary' is in your statement.
February 14, 2019 at 1:50 pm
thisisfutile - Wednesday, February 13, 2019 1:34 PMdrew.allen - Tuesday, February 12, 2019 2:05 PMAlso, you have your terminology a little mixed up. The key field of a table MUST be unique. So, status cannot be the "key field in the Customer table," because it is not unique in the customer table.Drew
I'm sorry, I thought 'foreign' was understood, just as 'primary' is in your statement.
The word primary is optional, because it's the default kind of key. The word foreign is required, because it's not the default kind of key. At least, that is my understanding of how the language works, and I haven't seen anything to indicate otherwise (although that could just be observational bias).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply