dwain.c (7/2/2013)

---

Erik - Just because your Prod system doesn't have a million rows to test against, doesn't mean you can't test against a million rows!

The test harness in the article demonstrates the generic approach for this (which I do not take credit for by the way).

This would directly compare PatternSplitCM against your SVF.

CREATE TABLE #Phones (phone VARCHAR(12));

WITH Tally (n) AS (

SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT 1))

FROM sys.all_columns a CROSS JOIN sys.all_columns b)

INSERT INTO #Phones

SELECT 1000000+ABS(CHECKSUM(NEWID()))%1000000

FROM Tally;

DECLARE @Phone VARCHAR(12), @StartDT DATETIME;

PRINT 'PatternSplitCM';

SELECT @StartDT = GETDATE();

SET STATISTICS TIME ON;

SELECT @Phone=Item

FROM #Phones

CROSS APPLY dbo.PatternSplitCM(phone, '[0-9]')

WHERE [Matched]=1;

SET STATISTICS TIME OFF;

SELECT ElapsedMS=DATEDIFF(millisecond, @StartDT, GETDATE());

PRINT 'extractinteger';

SELECT @StartDT = GETDATE();

SET STATISTICS TIME ON;

SELECT @Phone=dbo.extractinteger(phone)

FROM #Phones;

SET STATISTICS TIME OFF;

SELECT ElapsedMS=DATEDIFF(millisecond, @StartDT, GETDATE());

GO

DROP TABLE #Phones;

Edit: I modified it to SELECT the ElapsedMS of each run, which is needed due to a bias that is introduced by applying STATISTICS TIME to an SVF as demonstrated here: http://www.sqlservercentral.com/articles/T-SQL/91724/

Msg 156, Level 15, State 1, Procedure PatternSplitLoop, Line 42

Incorrect syntax near the keyword 'WHEN'.

Msg 102, Level 15, State 1, Procedure PatternSplitLoop, Line 54

Incorrect syntax near 'a'.