Computed columns: SQL or Locally in Entity Framework?

Question

Hello!

I have some computed-columns of two types:

1. Computed columns based on columns in current table (i.e. Price * Tax)
2. Computed columns based on other columnes (i.e. Price * fn_GetTax(OrderId))

Would you say it's better to use these columns in CLR on the client only and then save from the server the calculation and transferring performance? Does it vary in the types listed above (and other types)?

Answer 1

I would try to make computed columns part of your SQL tables - if the effort needed to do so is not outrageous.

If you have fairly simple calculations, or lookups, or something along those lines - put those in the SQL table directly. This way, they're available even if you need to query the table(s) with T-SQL or another tool accessing your SQL Server database, and they're most likely a whole lot faster (especially if you can make them PERSISTED).

If you have more elaborate requirements, if you need extensive string, math, or date manipulation etc., you're probably better off putting those computations into a .NET language. You can either store that as a CLR assembly in SQL Server and still do the columns in SQL Server directly (if a reuse by other teams or projects is likely), or you could put those into your EF model if you're likely to be the only one interested in those computed columns.

Hope that sheds a bit of light on your question!