7 Пост
7 Пост
10 Пост
2 Пост
1 Пост
5 Новости
Returning a Table from a Function in SQL Server
In SQL Server, functions can directly return a table.
This is simpler than in Oracle because SQL Server has native table return types.
There are two types of table-returning functions:
1. Inline Table-Valued Function (iTVF)
This behaves like a parameterized view.
Concept:
- Contains a single SELECT statement
- Returns its result set as a virtual table
- Best performance
- Fully optimized by the SQL Server query engine
Usage Case:
When you only need to return a filtered or calculated dataset without procedural logic.
2. Multi-Statement Table-Valued Function (mTVF)
This one allows procedural logic.
Concept:
- You declare a table variable inside the function
- Insert rows into it
- Return it at the end
- More flexible, but not as fast as inline functions
Usage Case:
When you need:
- Loops
- Conditions
- Multiple steps before returning data
Key Differences vs Oracle
|
Feature |
SQL Server |
Oracle |
|
Return a table directly |
✅ Yes |
❌ No (must use collection types) |
|
Uses explicit row & table types |
❌ Only if needed |
✅ Required |
|
Pipelining (streaming) |
❌ Not used |
✅ Pipelined functions available |
|
Simplicity |
Very simple |
More formal structure, more steps |
|
Performance tuning |
Based on query optimizer |
Depends on normal vs pipelined implementation |
Practical Understanding
|
Function Type |
Best For |
Notes |
|
iTVF (inline) |
High-performance, simple filtering/joins |
Fastest, cleanest |
|
mTVF (multi-statement) |
Complex logic, multiple steps |
More flexible, but slower |
Summary
- SQL Server makes returning tables easy.
- Use inline functions whenever possible → they perform best.
- Use multi-statement functions only when procedural logic is required.
- Compared to Oracle, SQL Server’s approach is simpler, but Oracle’s pipelined functions scale better for very large datasets.