You may find some answers in this article: What is a table function? Why do we need table functions?
The most important differences between Table Functions and SQL programming can be summarized as follows:
- SQL is more appropriate for operational purposes, like recording new data, or updating existing data in tables. Table Functions are designed for analytical operations based on data tables, like forecasts, risk analysis, complex commissions, and so on.
- Table Functions can be compared to Matrix Functions with the difference that their input and output parameters are data tables instead of matrices. Data tables are taken as a whole as parameters.
- Complex analytical operations like financial plans, sales commissions etc. can be formulated much easier, faster and compacter (reduced complexity) with Table Functions.
- A function tree (or any analytical relationship) implemented with Table Functions has clearly defined input and output parameters. That's not always the case with lengthy SQL scripts, partially due to tight integration with the underlying data structure (or schema) of the database.
- Parametric Table Functions are generally much higher-level programming constructs than SQL statements. A table function can be compared to a well-designed Stored Procedure with clearly defined input and output tables without hidden interactions (or parameters) with a database.
So what? To summarize:
If you are developing a software which should record, query (for reports) or update data, conventional SQL programming is the better choice.
If you want to formulate analytical relationships based on data tables like some forecasts, sales or dealer commissions, simulations, optimizations etc. (analytical) table functions are the better choice.