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

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?

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.

StackExchange Database Administrators Q#326103, answer score: 7