Here's a different method that's faster. Of course, I can't claim performance improvements unless I can prove it. To do that, we'll setup a 100,000 row test table, like this...
--===== Conditionally drop and repopulate the test table
-- to make reruns in SSMS easier. We're just building
-- test data here. THIS IS NOT A PART OF THE SOLUTION.
IF OBJECT_ID('tempdb..#HOST0140','U') IS NOT NULL
DROP TABLE #HOST0140
;
WITH
cteTally AS
(
SELECT TOP 100000
N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT NAME = 'LastName'+CAST(N AS VARCHAR(10))
+ ', '
+ 'FirstName'+CAST(N AS VARCHAR(10))
INTO #HOST0140
FROM cteTally
;
Here's an iTVF being used as an iSF (see http://www.sqlservercentral.com/articles/T-SQL/91724/
for more on those)...
CREATE FUNCTION dbo.ReverseName
(@pString VARCHAR(8000),@pDelimiter VARCHAR(5))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH cteFindDelimiter AS
(
SELECT DelimiterPosition = CHARINDEX(@pDelimiter,@pString)
)
SELECT ReversedName = CASE
WHEN DelimiterPosition > 0
THEN LTRIM(SUBSTRING(@pString,DelimiterPosition+LEN(@pDelimiter),8000))
+ ' '
+ SUBSTRING(@pString,1,DelimiterPosition-1)
ELSE @pString
END
FROM cteFindDelimiter
;
Now, a test to compare the two. The @Bitbucket variable takes the display time out of the picture.
--===== Declare a timer variable.
DECLARE @StartTime DATETIME;
--===== Create variable to take display times out of the picture.
DECLARE @Bitbucket VARCHAR(8000);
RAISERROR('========== ReverseName ========================================',0,1)
SELECT @StartTime = GETDATE();
SELECT @Bitbucket = r.ReversedName
FROM #HOST0140 h
CROSS APPLY dbo.ReverseName(h.Name,',') r;
PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10));
RAISERROR('========== udf_ReverseNames ===================================',0,1)
SELECT @StartTime = GETDATE();
SELECT @Bitbucket = dbo.udf_ReverseNames(h.Name,',')
FROM #HOST0140 h;
PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10));
Here are the results from that test. "ReverseName" is new function with no While Loop.
========== ReverseName ========================================
Duration (ms): 450
========== udf_ReverseNames ===================================
Duration (ms): 2123
--Jeff Moden
Change is inevitable... Change for the better is not.