Alan.B (7/14/2014)

---

Jeff Moden (7/10/2014)

---

Alan.B (7/10/2014)

---

Phil, you can make this more than twice as fast by turning it into an inline table value function like this:

To that, I say... are you sure? Please read the following article before you answer that question.

http://www.sqlservercentral.com/articles/T-SQL/91724/

I should have posted a different test harness but I was basing that statement on the fact that - on my system the original function runs for 10-11 seconds (11 with results on, 10 with results off). The itvf version runs for 3-5 seconds (3 with results off). Those were the times I got just by just highlighting the code and hitting F5.

If I measure the results like this:

DECLARE @Result char(4);

--===== Begin measuring duration using GETDATE

DECLARE @StartTime DATETIME;

SELECT @StartTime = GETDATE();

--===== Test the code using the "throw-away" variable

PRINT char(13)+'lefttrim:'

SELECT @result = dbo.lefttrim(string)

FROM #test;

--===== "Stop the timer" and report the duration

PRINT DATEDIFF(ms,@StartTime,GETDATE());

GO

DECLARE @Result char(4);

--===== Begin measuring duration using GETDATE

DECLARE @StartTime DATETIME;

SELECT @StartTime = GETDATE();

--===== Test the code using the "throw-away" variable

PRINT char(13)+'itvf_lefttrim:'

SELECT @result = x.string

FROM #test

CROSS APPLYdbo.itvf_LeftTrim(string) x;

--===== "Stop the timer" and report the duration

PRINT DATEDIFF(ms,@StartTime,GETDATE());

GO

Results:

lefttrim:

10703

itvf_lefttrim:

3466

This is on my laptop (4X) 2.6 ghz, 16gb ram, SQL 2014 Ent.