Trying to piece together some test data, some Uppercase letters with some spaces inserted:
IF OBJECT_ID('tempdb..#ProperTest','U') IS NOT NULL
DROP TABLE #ProperTest;
SELECTSTUFF(STUFF(CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),N%16+1,1,' '),N%3+1,1,' ') AS String
INTO #ProperTest
FROMdbo.GetNums(1,300000);
Run the Code
PRINT 'Old Method Scalar Function'
SELECTPT.String,
Utility.f_ProperCase(PT.String) AS Proper
FROM#ProperTest AS PT;
PRINT 'Dohsan'
SELECTPT.String,
CA1.ProperCaseString
FROM#ProperTest AS PT
CROSS
APPLYdbo.ProperCase(PT.String) AS CA1;
PRINT 'Luis'
SELECTPT.String,
CA1.ProperCased
FROM#ProperTest AS PT
CROSS
APPLYdbo.ProperCase1(PT.String) AS CA1;
DECLARE @TestVar VARCHAR(16);
DECLARE @StartTime DATETIME;
PRINT 'Old Method Scalar Function'
SELECT @StartTime = GETDATE();
SELECT@TestVar = Utility.f_ProperCase(PT.String)
FROM#ProperTest AS PT;
PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR) + ' MilliSeconds';
PRINT 'Dohsan'
SELECT @StartTime = GETDATE();
SELECT@TestVar = CA1.ProperCaseString
FROM#ProperTest AS PT
CROSS
APPLYdbo.ProperCase(PT.String) AS CA1;
PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR) + ' MilliSeconds';
PRINT 'Luis'
SELECT @StartTime = GETDATE();
SELECT@TestVar = CA1.ProperCased
FROM#ProperTest AS PT
CROSS
APPLYdbo.ProperCase1(PT.String) AS CA1;
PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR) + ' MilliSeconds';
Results:
Old Method Scalar Function
47843 MilliSeconds
Dohsan
18906 MilliSeconds
Luis
4710 MilliSeconds