SET STATISTICS TIME OFF;
SET NOCOUNT ON;
GO
RAISERROR('starting preparation',0,1) WITH NOWAIT;
GO
USE master;
GO
EXECUTE AS LOGIN='SA';
GO
IF DB_ID('ExcPlnTst') IS NOT NULL
BEGIN
  ALTER DATABASE ExcPlnTst SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE ExcPlnTst;
END
GO
CREATE DATABASE ExcPlnTst;
GO
USE ExcPlnTst;
GO
CREATE FUNCTION [dbo].[f_num](@i INT)
RETURNS TABLE
AS
RETURN
  WITH NA( n ) AS (SELECT 1 UNION ALL SELECT 1 ),
       NB( n ) AS (SELECT 1 FROM NA A CROSS JOIN NA B),
       NC( n ) AS (SELECT 1 FROM NB A CROSS JOIN NB B),
       ND( n ) AS (SELECT 1 FROM NC A CROSS JOIN NC B),
       NE( n ) AS (SELECT 1 FROM ND A CROSS JOIN ND B),
       NF( n ) AS (SELECT 1 FROM NE A CROSS JOIN NE B),
       NG( n ) AS (SELECT 1 FROM NF A CROSS JOIN NF B)
SELECT TOP(@i) ROW_NUMBER() OVER (ORDER BY n) no
		FROM NG;

GO
RAISERROR('creating testdata',0,1) WITH NOWAIT;
GO

CREATE TABLE dbo.TstData(
  id INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
  val1 INT NOT NULL,
  val2 VARCHAR(MAX)
);
INSERT INTO dbo.TstData(val1,val2)
SELECT A.no,REPLICATE(CHAR(65+(B.no % 26)),A.no)
  FROM dbo.f_num(10) AS A
 CROSS JOIN dbo.f_num(50) AS B;
GO
CREATE CLUSTERED INDEX TstData_CI ON dbo.TstData(val1);
GO

CREATE VIEW dbo.TstVw1
AS
SELECT DISTINCT A.val1,C.Cnct
  FROM dbo.TstData AS A
 CROSS APPLY(SELECT (SELECT B.val2 [text()] 
                       FROM dbo.TstData AS B 
                      WHERE B.val1 = A.val1 
                      ORDER BY B.id 
                        FOR XML PATH('')
                     ) AS Cnct
            ) AS C;

GO

CREATE FUNCTION dbo.TstDataConcat(
                @GrpVal INT
                )
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @Cnct VARCHAR(MAX);
  DECLARE @Val1 INT;
  DECLARE @Val2 VARCHAR(MAX);
  
  SET @Cnct = '';
  DECLARE cur CURSOR LOCAL FAST_FORWARD 
      FOR SELECT val1,val2
            FROM dbo.TstData
           ORDER BY id;
  OPEN cur;
  FETCH NEXT FROM cur INTO @Val1, @Val2;
  WHILE(@@FETCH_STATUS = 0)
  BEGIN
    IF(@Val1 = @GrpVal)
    BEGIN
      SET @Cnct += @Val2;
    END
    FETCH NEXT FROM cur INTO @Val1, @Val2;
  END
  CLOSE cur;
  DEALLOCATE cur;  
  RETURN @Cnct;
END

GO

CREATE VIEW dbo.TstVw2
AS
SELECT val1,dbo.TstDataConcat(val1) AS Cnct
  FROM dbo.TstData
 GROUP BY val1;
GO
---------------------------------------------------------------------------------
USE ExcPlnTst;
GO
IF OBJECT_ID('tempdb..#tst1') IS NOT NULL DROP TABLE #tst1;
IF OBJECT_ID('tempdb..#tst2') IS NOT NULL DROP TABLE #tst2;
GO  
SET STATISTICS TIME OFF;
SET NOCOUNT ON;
GO
RAISERROR('starting tst1 (CROSS APPLY)',0,1) WITH NOWAIT;
GO

SET NOCOUNT OFF;
GO

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT * INTO #tst1 FROM dbo.TstVw1;
SET STATISTICS TIME OFF;

GO
RAISERROR('starting tst2 (FUNCTION)',0,1) WITH NOWAIT;
GO

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
SET STATISTICS TIME ON;
SELECT * INTO #tst2 FROM dbo.TstVw2;
SET STATISTICS TIME OFF;
GO
SET NOCOUNT ON;  
---------------------------------------------------------------------------------
GO
RAISERROR('cleanup',0,1) WITH NOWAIT;
GO

SELECT * FROM #tst1;

SELECT * 
  FROM #tst1 AS A
  JOIN #tst1 AS B
    ON A.val1 = B.val1
   AND A.Cnct <> B.Cnct;
   
GO

DROP TABLE #tst1;
DROP TABLE #tst2;
GO
USE master;
GO
REVERT;
GO
RAISERROR('finished',0,1) WITH NOWAIT;
GO