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?