Some excellent work
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';
PRINT 'Dwain'
SELECT @StartTime = GETDATE();
SELECT@TestVar = CA1.ProperCaseStr
FROM#ProperTest AS PT
CROSS
APPLYdbo.ProperCase2(PT.String) AS CA1;
PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR) + ' MilliSeconds';
PRINT 'Magoo varchar255'
SELECT @StartTime = GETDATE();
SELECT@TestVar = CA1.[text]
FROM#ProperTest AS PT
CROSS
APPLYdbo.ProperCase3(PT.String) AS CA1;
PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR) + ' MilliSeconds';
PRINT 'Luis V2'
SELECT @StartTime = GETDATE();
SELECT@TestVar = CA1.ProperCaseString
FROM#ProperTest AS PT
CROSS
APPLYdbo.ProperCase4(PT.String) AS CA1;
PRINT CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR) + ' MilliSeconds';
Here as the results:
300k
Old Method Scalar Function
47306 MilliSeconds
Dohsan
19113 MilliSeconds
Luis
5946 MilliSeconds
Dwain
54486 MilliSeconds
Magoo varchar255
5440 MilliSeconds
Luis V2
6433 MilliSeconds
1million
Old Method Scalar Function
160710 MilliSeconds
Dohsan
63750 MilliSeconds
Luis
15450 MilliSeconds
Dwain
175366 MilliSeconds
Magoo varchar255
15513 MilliSeconds
Luis V2
17843 MilliSeconds
How do they handle some special cases?
DECLARE @SingleTestString VARCHAR(255) = 'ándre, ándre, luís and o''brien had a low-budget 3d printer in washington d.c.',
@TestVar2 VARCHAR(255);
PRINT 'Original String'
PRINT @SingleTestString;
PRINT 'Old Method Scalar Function'
SELECT @TestVar2 = Utility.f_ProperCase(@SingleTestString);
PRINT @TestVar2
PRINT 'Dohsan'
SELECT @TestVar2 = ProperCaseString FROM dbo.ProperCase(@SingleTestString);
PRINT @TestVar2
PRINT 'Luis'
SELECT @TestVar2 = ProperCased FROM dbo.ProperCase1(@SingleTestString);
PRINT @TestVar2
PRINT 'Dwain'
SELECT @TestVar2 = ProperCaseStr FROM dbo.ProperCase2(@SingleTestString);
PRINT @TestVar2
PRINT 'Magoo varchar255'
SELECT @TestVar2 = [text] FROM dbo.ProperCase3(@SingleTestString);
PRINT @TestVar2
PRINT 'Luis V2'
SELECT @TestVar2 = ProperCaseString FROM dbo.ProperCase4(@SingleTestString);
PRINT @TestVar2
Results:
Original String
ándre, ándre, luís and o'brien had a low-budget 3d printer in washington d.c.
Old Method Scalar Function
Ándre, Ándre, Luís And O'Brien Had A Low-Budget 3d Printer In Washington D.c.
Dohsan
Ándre, Ándre, Luís And O'Brien Had A Low-Budget 3d Printer In Washington D.c.
Luis
Ándre, Ándre, Luís And O'brien Had A Low-budget 3d Printer In Washington D.c.
Dwain
Ándre, Ándre, Luís And O'Brien Had A Low-Budget 3D Printer In Washington D.C.
Magoo varchar255
Ándre, Ándre, Luís And O'Brien Had A Low-Budget 3D Printer In Washington D.C.
Luis V2
Ándre, Ándre, Luís And O'Brien Had A Low-Budget 3d Printer In Washington D.C.