Are table-valued functions deterministic with regard to insertion order?

Question

Is insertion order deterministic in a TABLE-valued function which instantiates the table and populates it with successive inserts, assuming no ORDER BY clause is used?

I needed to take a look at the range of Unicode characters in use in a given dataset sent to us, in order to figure out why some spaces, or at least what appeared to the eye to be spaces in query results in SSMS, were not behaving like char(32) in some parsing routines. So I wrote a quick-and-dirty function to turn the string value in the column in question into a set of tuples that could be queried:

create function [foo].[AllChars]
(@in nvarchar(max))
returns @t TABLE (c nchar(1))
as
begin


declare @i int;
while len(@in)>0
begin
   insert @t(c) values (left(@in,1));
   set @in = substring(@in, 2, len(@in)-1)
end

return;
end

which would be called like this:

   select X.c theChar, unicode(X.c) uValue
   from myTable T
   cross apply foo.AllChars(T.myCol) X

and would return a set of tuples for each row in myTable:

t  116
h  104
e  101
   32
c  99
a  97
t  116
   10   <= culprit
i  105
n  110
   32
t  116
h  104
e  101
   32
h  104
a  97
t  116
   

The results came back in the order in which the characters appeared in T.myCol and thus the order in which they were inserted into @t. Is that order guaranteed, absent an ORDER BY clause?

Answer 1

Display order is only guaranteed by a top-level ORDER BY.

You could rewrite the function to return a position key and make it inline:

CREATE OR ALTER FUNCTION dbo.AllChars
(
    @in nvarchar(max)
)
RETURNS table
AS
RETURN
SELECT
    Position = S.[value],
    TheCharacter = 
        SUBSTRING
        (
            @in COLLATE Latin1_General_100_CI_AS_SC, 
            S.[value], 
            1
        )
FROM GENERATE_SERIES
(
    CONVERT(bigint, 1), -- Types must match exactly
    LEN(@in) - 1,       -- LEN returns bigint for max input
    CONVERT(bigint, 1)  -- Types must match exactly
) AS S;

I'm using a COLLATE clause there to demo supplementary characters. You'll probably be fine with using the default database collation.

Example usage:

DECLARE @in nvarchar(max) = 
    CONCAT(N'the 😎 cat in', NCHAR(10), N'the hat');

SELECT 
    AC.Position, 
    AC.TheCharacter,
    Bytes = DATALENGTH(AC.TheCharacter),
    Code = UNICODE(AC.TheCharacter)
FROM dbo.AllChars(@in) AS AC
ORDER BY
    AC.Position ASC;

Result:

Position	TheCharacter	Bytes	Code
1	t	2	116
2	h	2	104
3	e	2	101
4		2	32
5	😎	4	128526
6		2	32
7	c	2	99
8	a	2	97
9	t	2	116
10		2	32
11	i	2	105
12	n	2	110
13	␊	2	10
14	t	2	116
15	h	2	104
16	e	2	101
17		2	32
18	h	2	104
19	a	2	97
20	t	2	116

db<>fiddle

Related: Does MS SQL Server have generate_series function

See also Generate a set or sequence without loops by Aaron Bertrand.

Answer 2

No order is ever guaranteed without an order by clause. If you created an index on your table variable, you may get lucky and return data in index order depending on various optimizer choices, but it would still not be guaranteed in any way.

Since you are using a Unicode data type, you also need to be aware of various collation differences in sort order. It can happen with non-Unicode strings as well, but there are fewer variables around accents etc.