Table Functions

Hi,

Although there's a lot to hate about SQL Server not everything is bad there. In fact, there's quite a lot other database engines could implement... at least Oracle could. More on it later.
This time I'm more interested in the functionality that both SQL Server and Oracle have already in place but, from ease-to-implement point of view, SQL Server does it better (minus T-SQL syntax of course ;D) - Table Functions (or, Table Valued User Defined Function as Microsoft decided to call it).

What is the Table Function? This is the question we have to ask in the first place. Well, Table Function is a function that returns some kind of collection and can be used just like any table or view, as part of a typical DML select statement. From developer's point of view, it can be seen as a sort of parameterized view although, being a function, it allows a sort of random operations to be done in it's body. You can do whatever the RDBMS allows you to do in regular functions. What is widely used as an example is parsing a clob data, usually a kind of XML or JSON data resulting in multiple output rows. Generally, Table Functions are not amongst the most used tools in databases nowadays but when you get to the point when you need them, you'll be relieved it's there.

SQL Server allows for two different Table Function syntaxes. Both are useful but serve a little bit different purpose. First one, much easier to implement is an Inline Table-Valued Function. In this case the function really serves as a parameterized view. The syntax allows only for a select statement and goes like this (the syntax comes from official documentation):

-- Transact-SQL Inline Table-Valued Function Syntax  
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name  
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type  
    [ = default ] [ READONLY ] }  
    [ ,...n ] 
  ] 
) 
RETURNS TABLE 
    [ WITH <function_option> [ ,...n ] ] 
    [ AS ] 
    RETURN [ ( ] select_stmt [ ) ] 
[ ; ]

In practice you'll generally get whatever comes from the select, nothing more, nothing else. It's extremely easy to code but have a quite limited use.
That's how it can look in practice:

create or alter function MyInlineFunction ( @mp int )
returns table as
return
(
 select p.p*@mp as p, q.q*@mp as q
 from (
  select 1 as p union all
  select 2 as p union all
  select 3 as p
 ) p
 cross join
 (
  select 1 as q union all
  select 2 as q union all
  select 3 as q
 ) q
)
go

select * from MyInlineFunction( 2 )

p           q
----------- -----------
2           2
2           4
2           6
4           2
4           4
4           6
6           2
6           4
6           6

We can already see that no matter what we do, we will always get 9 rows in the output data set. How can we overcome it? By using the other, Multi-Statement Table-Valued Function syntax, obviously.

It's syntax goes like this:
-- Transact-SQL Multi-Statement Table-Valued Function Syntax 
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name  
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type  
    [ = default ] [READONLY] }  
    [ ,...n ] 
  ] 
) 
RETURNS @return_variable TABLE <table_type_definition> 
    [ WITH <function_option> [ ,...n ] ] 
    [ AS ] 
    BEGIN  
        function_body  
        RETURN 
    END 
[ ; ]

So... we could code such a function like this:

create or alter function MyMultiStatementFunction ( @pcount int, @qcount int, @mp int )
RETURNS @t TABLE ( 
 p int,
 q int 
)
AS
begin
 declare @p_iterator int = 1
 declare @q_iterator int = 1

 while @p_iterator <= @pcount
 begin
  while @q_iterator <= @qcount
  begin
   insert into @t (p, q) values ( @p_iterator*@mp, @q_iterator*@mp )
   set @q_iterator = @q_iterator + 1
  end
  set @q_iterator = 1
  set @p_iterator = @p_iterator + 1
 end

 return
end
go

select * from MyMultiStatementFunction( 3, 3, 2 )

This gives us exactly the same result as the previous one, but now, we can play around with first two parameters to get different number of rows.

The syntax still doesn't look bad. It's... surprisingly easy! You just define the output type as a table, insert rows inside the function and return it using a single return statement. It really can't get easier than this!

In the Oracle world however, all is of the latter type but there's so many details a developer have to think of that it requires quite a lot of reading and education. First of all, you have an easy (which is still harder and more cumbersome than MS SQL) way of creating table functions but in this case... well... you need to know how does it work internally. The easy version creates a collection inside a function, caches it and returns the whole thing once the function is finished. This behaviour may require substantial amounts of memory and leads to the situation when query appears to be hung up since no rows are produced during processing. The general cumbersomness of PL/SQL table function comes from the fact that, unlike MS SQL which declares return type in function declaration, PL/SQL requires those types to exist in the database. Duh... although, 12.1 allows them to exist in a package so, there's a light..
How would the obove function look like in PL/SQL then?

create or replace type t_pq_r is object 
( 
    p number,
    q number 
)
/

create or replace type t_pq is table of t_pq_r;
/

create or replace function MyOracleTableFunction ( pcount number, qcount number, mp number ) 
return t_pq
is
    piterator number := 1;
    qiterator number := 1;
    rw t_pq_r;
    rv t_pq;
begin
    rv := t_pq();
    while piterator <= pcount loop
        while qiterator <= qcount loop
            rw := t_pq_r( piterator*mp, qiterator*mp );
            rv.extend( 1 );
            rv( rv.count ) := rw;
            qiterator := qiterator + 1;
        end loop;
        qiterator := 1;
        piterator := piterator + 1;
    end loop;
    return rv;
end;
/

select * from TABLE ( MyOracleTableFunction( 3, 3, 2 ) );

Although not overly complicated it doesn't look nearly as nice as SQL Server version. It is very similar to what you'd probably see in other languages (Java, C++, etc) when building collections, though.

So, what can we do to make it more interactive in terms of getting rows? We could pipeline it.

create or replace function MyOracleTablePipelinedFunction ( pcount number, qcount number, mp number ) 
return t_pq pipelined
is
    piterator number := 1;
    qiterator number := 1;
    rw t_pq_r;
begin
    while piterator <= pcount loop
        while qiterator <= qcount loop
            rw := t_pq_r( piterator*mp, qiterator*mp );
            PIPE ROW(rw);
            qiterator := qiterator + 1;
        end loop;
        qiterator := 1;
        piterator := piterator + 1;
    end loop;
    return;
end;
/

select * from TABLE ( MyOracleTablePipelinedFunction( 3, 3, 2 ) );

Now, we get results instantanously. It's so much quicker all in all...

I did some research and own testing and... initially I said SQL Server is better in this regards, now I'm drifting towards another no, it isn't.

I run Oracle jobs on the small db.t2.micro (1 CPU, 1GB RAM) instance. On the other hand, SQL Server is executed locally on 8 CPU, 32GB RAM machine... What are the times of executing each of those functions (the ones using 3 parameters)?

And mind you, Oracle does not cache results of executions of Table Functions: "Multiple invocations of a table function, either within the same query or in separate queries result in multiple executions of the underlying implementation. That is, in general, there is no buffering or reuse of rows."

It's what I got:

On the x axis is the value of the p/q parameters (both got the same value), on the y axis is the elapsed time (the scale is logarithmic). What does it tell us? That Oracle pipelined execution is unbeatable here and MS SQL is roughly comparable (in terms of performance) to the basic Oracle way of doing this stuff however, after certain threshold (~1000 for p and q) Oracle is becoming unusable whereas MS didn't experience any problems.

So, all in all, I dare to say, Oracle wins once again. It's easier to do in SQL Server, yes. Do I wish Oracle did not need explicit type definitions? Yes, I do. But despite all of it the performance difference is so much favorable for Oracle it's a no brainer.

One final disclaimer. I haven't read all the books, whitepapers, didn't do courses and who knows what, so there's a chance that there are some magic tricks that make table functions to perform better in MS SQL Server. If so, don't hesitate to clear that. I know also that those functions could have been written in trillion other ways. If you wish to present better (in any terms) versions - be my guest!

Best