SQL Joins vs Computed Columns
Just wanted to get some feedback on a question that troubles me...
What are the pros & cons of using computed columns instead of traditional SQL joins?
For example, have the following SQL tables:
Client - ClientID (p.k.), ClientName
ClientContact - ClientContactID (p.k.), ClientID (f.k.), ClientContactName
I want to show a list of client contact names in a grid, along with their client names.
Is it better to have a dataset with a single table, filled by a JOIN?
Or have two tables filled individually, with a computed column on the ClientContact table?
The example is trivial, but hopefully you get the idea. I currently have datasets with about ten tables, most of which are only there to get the text from a lookup table. It seems cleaner to have a small number of tables and fill with JOINs, but I don't like that my stored procedures get complicated.
Thanks for your input!
Computed columns are columns that are derived from other columns in a SQL table.
http://msdn2.microsoft.com/en-us/library/ms191250.aspx
I think you meant to say a DataRelation between the two DataTable.DataColumns in the dataset.
SQL joins are always faster and use less client resources but if you need to manipulate data in your dataset, you may have to use a relationship between your data tables.
If it's just a matter of querying the database and displaying the data, do a SQL join.
I think a lot of the answer would depend on the actual data in your tables.
For example, if you ClientContact table had millions of records then you would probably not want the entire table loaded into the client to facilitate a client side join. It would be much better to have the database join the tables and send you the results.
On the other hand, if the tables had comparatively few records and you were going to repeatedly lookup into one or both of them, then it might be more efficient to download the table to the client.
In the general case, I would tend to favor having the database facilitate the join.
I see your point. When there are a large number of possible values, I tend to fill the lookup table with only the required values. This works ok, but adds complexity.
I think I will limit my use of Data Relations to situations such as Master/Detail, and use JOINs for lookup values.
Thanks for the feedback.
