Split two delimited strings in same order without function
Create the function in msdb or somewhere else.

CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder
(
    @List1  varchar(50),
    @List2  varchar(50),
    @Delim  varchar(10)
)
RETURNS TABLE
AS
    RETURN
    (
      WITH src(r) AS 
      (
        SELECT 1 UNION ALL SELECT r + 1 FROM src WHERE r &lt; 10
      ),
      Numbers(Number) AS 
      (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
        FROM src AS s1, src AS s2 -- add more if you need longer strings
      ),
      parsed(s1,s2,r1,r2)
      AS
      (
        SELECT
          SUBSTRING(@List1, n1.Number, CHARINDEX(@Delim, @List1 
            + @Delim, n1.Number) - n1.Number),
          SUBSTRING(@List2, n2.Number, CHARINDEX(@Delim, @List2 
            + @Delim, n2.Number) - n2.Number),
          r1 = ROW_NUMBER() OVER (ORDER BY n1.Number),
          r2 = ROW_NUMBER() OVER (ORDER BY n2.Number)
        FROM Numbers AS n1, Numbers AS n2
        ON  n1.Number &lt;= LEN(@List1)
        AND n2.Number &lt;= LEN(@List2)
        AND SUBSTRING(@Delim + @List1, n1.Number, LEN(@Delim)) = @Delim
        AND SUBSTRING(@Delim + @List2, n2.Number, LEN(@Delim)) = @Delim
      )
      SELECT s1, s2, r1, r2 FROM parsed WHERE r1 = r2
    );


Then, as @gbn noted, reference it by 3-part name wherever your query has to run.

CREATE TABLE #temp
(id   INT,
 keys VARCHAR(50),
 vals VARCHAR(50)
);

INSERT INTO #temp
VALUES
(1, '1,2,3', 'one,two,three'),
(2, '4,5,6', 'four,five,six'),
(3, '7,8,9', 'seven,eight,nine');

SELECT t.id, f.s1, f.s2 FROM #temp AS t
  CROSS APPLY msdb.dbo.SplitTwoStringsWithSameOrder(keys, vals, ',') AS f
  ORDER BY t.id, f.r1;
GO

DROP TABLE #temp;


Results:


  


The resulting plan, shown in Plan Explorer (disclaimer: I'm the Product Manager), is not the prettiest thing I've ever seen (click to enlarge a little bit):


  


But there is exactly one scan of #temp (4% cost). The biggest costs are two sorts and a spool, and there is some I/O due to a worktable which I am not sure is avoidable. 

If you KNOW you will only ever have 50 characters in either of these strings, then you can get a much simpler plan with a built-in Numbers table (people object to these, but they're very useful, and they are almost always in memory if you reference them enough). This doesn't help I/O but removing the recursive CTE and other constructs of building the numbers inside the function is quite helpful for CPU etc.

First, the numbers table:

DROP TABLE dbo.Numbers;

;WITH n AS
(
    SELECT
        TOP (50) rn = ROW_NUMBER() OVER
        (ORDER BY [object_id])
    FROM sys.all_columns 
    ORDER BY [object_id]
)
SELECT [Number] = rn - 1
INTO dbo.Numbers
FROM n;

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers([Number]);


Then a second version of the function:

CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder2
(
    @List1  varchar(50),
    @List2  varchar(50),
    @Delim  nvarchar(10)
)
RETURNS TABLE
AS
    RETURN
    (
      WITH parsed(s1,s2,r1,r2)
      AS
      (
        SELECT
          SUBSTRING(@List1, n1.Number, CHARINDEX(@Delim, @List1 
            + @Delim, n1.Number) - n1.Number),
          SUBSTRING(@List2, n2.Number, CHARINDEX(@Delim, @List2 
            + @Delim, n2.Number) - n2.Number),
          r1 = ROW_NUMBER() OVER (ORDER BY n1.Number),
          r2 = ROW_NUMBER() OVER (ORDER BY n2.Number)
        FROM dbo.Numbers AS n1
        INNER JOIN dbo.Numbers AS n2
        ON  n1.Number &lt;= LEN(@List1)
        AND n2.Number &lt;= LEN(@List2)
        AND SUBSTRING(@Delim + @List1, n1.Number, LEN(@Delim)) = @Delim
        AND SUBSTRING(@Delim + @List2, n2.Number, LEN(@Delim)) = @Delim
      )
      SELECT s1, s2, r1, r2 FROM parsed WHERE r1 = r2
    );
GO


Here is the simpler plan that results (again, click to enlarge):


  


The plan still has two sort operations, but the spool is gone, there is still only one scan of #temp, and in my limited tests the cost numbers (absolute cost numbers, not %) were better every time.

I don't know precisely either of these will scale with a lot more rows, but it's worth testing, and if you weigh this against other solutions and it can't scale well, that may be a point your reconsider the design (store these relationally instead of as comma-separated sets).