When are the corresponding columns calculated?

Question

I am considering the possibility of creating a table with a calculated column in Microsoft SQL Server 2008. This will be a simple calculation, for example (ISNULL (colA, (0)) + ISNULL (colB, (0))) - as a general, Our application uses Entity Framework 4 .

I am not completely familiar with computed columns, so I am curious what others have to say about when they are suitable for use, unlike other mechanisms that achieve the same result, such as views or a computed Entity column.

Are there any reasons why I don't want to use a computed column in a table?

If I use a computed column, should it persist or not? I read about various performance results using persistent rather than constant, with indexed and not indexed computed columns here . Given that my calculations seem simple, I am inclined to say that this should not be preserved.

Answer 1

In my experience, they are most useful / useful when they can be used in other places, such as an index or control constraint, which sometimes requires saving a column (physically stored in a table). For more information, see Computed Columns and Creating Indexes on Computed Columns .

Answer 2

If your computed column is not saved, it will be computed every time you access it, for example. a SELECT . If data based on changes is often, this may be good.

If the data does not change often, for example. if you have a calculated column to turn your numeric OrderID INT into human ORD-0001234 readable ORD-0001234 or something like that, then definitely make your computed column persistent - in this case the value will be calculated and physically stored on disk, and any subsequent access it is similar to reading any other column in your table - recalculating again and again.

We also came to use (and appreciate!) Computed columns to extract certain pieces of information from XML columns and lay them on the table as separate (saved) columns. This makes querying these elements much more efficient than constantly using XML with XQuery to retrieve information. For this use case, I think constant computed columns are a great way to speed up your queries!

Answer 3

Say you have a calculated ProspectRanking column that is the result of evaluating values ​​in several columns: ReadingLevel, AnnualIncome, Gender, OwnsBoat, HasPurchasedPremiumGasolineRecently.

Let's also say that many decentralized departments in your large mega-corporation use this data, and they all have their own programmers on staff, but you want the ProspectRanking algorithms to be managed centrally by IT at the corporate headquarters, which maintain close contact with vice president of marketing. Let them also say that the algorithm is often adjusted to reflect some changing conditions, such as interest rate or inflation rate.

You want the calculation to be part of the internal database engine, and not the client’s clients, if managing external clients is like herding cats.

If you can avoid nomadic cats, do it.

Answer 4

Make sure you query only the columns you need

I found that using computed columns is very useful, even if it is not saved, especially in the MVVM model, where you get only the columns needed for this particular view. Until you put in logic that is less efficient in the computed code column, you should be fine. The bottom line for those calculated (non-persistent columns), as a rule, you need to look in any case, if you use this data.

When it comes to performance

For performance, you reduce your query to rows and computed columns. If you put an index in a computed column (if it is checked Checked and it is not allowed ), I would be careful because the execution mechanism may decide to use this index and damage performance by calculating these columns. In most cases, you simply get a name or description from connection tables, so I think this is normal.

Do not be rude by force

The only time it would be pointless to use a lot of calculated columns is used if you use one class of a view model that captures all the data in all columns, including calculated ones. In this case, your performance will degrade depending on the number of calculated columns and the number of rows in the database you selected.

Computed columns for ORM Works Great.

An object relational mapper, such as EntityFramework, allows you to query a subset of the columns in your query. This works especially well with LINQ to EntityFramework. With computed columns, you don’t need to clutter up your ORM class with mapped views for each type of model.

 var data = from e in db.Employees select new NarrowEmployeeView { Id, Name }; 

Only Id and Name are requested.

 var data = from e in db.Employees select new WiderEmployeeView { Id, Name, DepartmentName }; 

Assuming DepartmentName is a computed column, you then get your calculated result for the last query.

Profiler

If you use the profiler and filter sql queries, you can see that actually computed columns are ignored if they are not specified in the select statement.

Answer 5

Computed columns may be appropriate if you plan on requesting this information.

For example, if you have a dataset that you intend to present in the user interface. Having a computed column allows you to display the view while maintaining the ability to sort and filter on the computed column. if this computed column is only in code, then it will be much harder to intelligently sort or filter the data set to display based on this value.

Answer 6

The calculated column is a business rule, and it is more expedient to implement it on the client, and not in the storage. The database is intended for storing / retrieving data, and not for processing business rules. The fact that he can do something does not mean that you should do so. You can also freely jump from the Eiffel tour, but it will be a bad decision :)