---========================================================
-- create a Jeff Moden style string split function
-- using a inline Numbers table
IF (OBJECT_ID('SplitStringInlineNumbers') IS NULL)
   EXECUTE ('CREATE FUNCTION SplitStringInlineNumbers () RETURNS TABLE AS RETURN SELECT 1 A');
GO
ALTER FUNCTION SplitStringInlineNumbers (
   @text VARCHAR(MAX)
   ,@separator CHAR(1)
)
RETURNS TABLE
AS
RETURN
   WITH
   n1 (Num) AS (SELECT 1 UNION ALL SELECT 1),      -- 2
   n2 (Num) AS (SELECT 1 FROM n1 CROSS JOIN n1 b), -- 4
   n3 (Num) AS (SELECT 1 FROM n2 CROSS JOIN n2 b), -- 16
   n4 (Num) AS (SELECT 1 FROM n3 CROSS JOIN n3 b), -- 256
   n5 (Num) AS (SELECT 1 FROM n4 CROSS JOIN n4 b), -- 65536
   -- 4294967296
   Numbers (Num) AS (
      SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))
      FROM n5 CROSS JOIN n5 b
      )
   SELECT
      SUBSTRING(
         @text
         ,t.Num + 1
         ,CHARINDEX(@separator, @text, t.Num + 1) - t.Num - 1
         ) AS Item
   FROM Numbers t
   WHERE 
      t.Num < LEN(@text)
      AND SUBSTRING(@text, t.Num, 1) = @separator
GO

---========================================================
-- create a Jeff Moden style string split function
-- using a database Numbers table
IF (OBJECT_ID('SplitStringDbNumbers') IS NULL)
   EXECUTE ('CREATE FUNCTION SplitStringDbNumbers () RETURNS TABLE AS RETURN SELECT 1 A');
GO
ALTER FUNCTION SplitStringDbNumbers (
   @text VARCHAR(MAX)
   ,@separator CHAR(1)
)
RETURNS TABLE
AS
RETURN
   SELECT
      SUBSTRING(
         @text
         ,t.Num + 1
         ,CHARINDEX(@separator, @text, t.Num + 1) - t.Num - 1
         ) AS Item
   FROM Numbers t
   WHERE 
      t.Num < LEN(@text)
      AND SUBSTRING(@text, t.Num, 1) = @separator

SET NOCOUNT ON;
USE tempdb;
GO

---========================================================
-- drop existing test tables
IF (OBJECT_ID('TestReferences') IS NOT NULL)
   DROP TABLE TestReferences;
IF (OBJECT_ID('TestData') IS NOT NULL)
   DROP TABLE TestData;
IF (OBJECT_ID('Numbers') IS NOT NULL)
   DROP TABLE Numbers;
GO

---========================================================
-- create test tables

-- we need a numbers table
CREATE TABLE Numbers
(
   Num INT NOT NULL
      PRIMARY KEY CLUSTERED
      WITH (FILLFACTOR = 100)
);

-- any sample data
CREATE TABLE TestData
(
   Id INT NOT NULL IDENTITY
      PRIMARY KEY CLUSTERED
   ,SomeInt INT
);

-- several references which relate to the test data
CREATE TABLE TestReferences
(
   Id INT NOT NULL IDENTITY
      PRIMARY KEY CLUSTERED
   ,DataId INT NOT NULL
      REFERENCES TestData (Id)
   ,Reference VARCHAR(36)
);
-- reference column is indexed
CREATE INDEX IX_TestReferences_Reference ON TestReferences (Reference);
GO

---========================================================
-- data

-- create a numbers table with 50,000 rows
INSERT INTO Numbers
   SELECT TOP(50000)
      ROW_NUMBER() OVER (ORDER BY (SELECT 1))
   FROM master.sys.all_columns c1
      CROSS JOIN master.sys.all_columns c2;
-- create 10,000 data rows
INSERT INTO TestData
   SELECT TOP(10000)
      Num
   FROM Numbers
-- create two references for each data row
INSERT INTO TestReferences
   SELECT 
      d.Id
      ,CONVERT(VARCHAR(36), NEWID())
   FROM TestData d
      CROSS JOIN (SELECT 1 A UNION ALL SELECT 2) blah
GO

SET NOCOUNT ON;
USE tempdb;
GO
--DBCC FREEPROCCACHE;

---==================================================================
-- call the inline-numbers function joined to the data table

-- get some references from TestReferences table to search
DECLARE @keys VARCHAR(MAX);
SELECT @keys = 
   CHAR(9) 
   + (SELECT TOP(1000) Reference + CHAR(9) FROM TestReferences FOR XML PATH(''));


--SET STATISTICS XML ON;
--SET STATISTICS TIME ON;
-- select all TestData related to the specified keys
SELECT DISTINCT 
      d.*
   FROM SplitStringInlineNumbers(@keys, CHAR(9)) keys
      JOIN TestReferences r ON keys.Item = r.Reference
      JOIN TestData d ON r.DataId = d.Id;
--SET STATISTICS TIME OFF;
--SET STATISTICS XML OFF;
GO

---==================================================================
-- call the database-numbers function joined to the data table

-- get some references from TestReferences table to search
DECLARE @keys VARCHAR(MAX);
SELECT @keys = 
   CHAR(9) 
   + (SELECT TOP(1000) Reference + CHAR(9) FROM TestReferences FOR XML PATH(''));


--SET STATISTICS XML ON;
--SET STATISTICS TIME ON;
-- select all TestData related to the specified keys
SELECT DISTINCT 
      d.*
   FROM SplitStringDbNumbers(@keys, CHAR(9)) keys
      JOIN TestReferences r ON keys.Item = r.Reference
      JOIN TestData d ON r.DataId = d.Id;
--SET STATISTICS TIME OFF;
--SET STATISTICS XML OFF;
GO

---==================================================================
-- call the database-numbers function into a table variable and 
-- join this to the data table

-- get some references from TestReferences table to search
DECLARE @keys VARCHAR(MAX);
SELECT @keys = 
   CHAR(9) 
   + (SELECT TOP(1000) Reference + CHAR(9) FROM TestReferences FOR XML PATH(''));

-- copy result of split function into a table variable
DECLARE @lookup TABLE (Item VARCHAR(36) PRIMARY KEY CLUSTERED);
INSERT INTO @lookup
   SELECT
      Item
   FROM SplitStringDbNumbers(@keys, CHAR(9));


-- select all data by joining the lookup table
SELECT DISTINCT 
      d.*
   FROM @lookup keys
      JOIN TestReferences r ON keys.Item = r.Reference
      JOIN TestData d ON r.DataId = d.Id;

PS to the regulars: Splitting will never end