Interestingly, by using a variable instead of 1000000 it was faster, but using a variable instead of 1000 makes it slower!

Here is the script I used for testing followed by my results (sorry my machine is not as fast as Jeff's!!!)

--===== Conditionally drop the test table to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

DROP TABLE #TestTable

;

--===== Create and populate the test table on-the-fly.

CREATE TABLE #TestTable ([C1] INT,

[C2] INT

);

INSERT INTO [#TestTable] ( [C1], [C2] )

SELECT TOP 1000000

C1 = ABS(CHECKSUM(NEWID()))%1000000

,C2 = ABS(CHECKSUM(NEWID()))%1000

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

;

--===== Create a target variable that will take the display time out of the picture.

DECLARE @Bitbucket CHAR(9);

PRINT '

========== Traditional RIGHT+RIGHT Method =========='

SET STATISTICS TIME ON;

SELECT @Bitbucket = RIGHT('000000'+CAST(C1 AS VARCHAR(6)),6) + RIGHT('000'+CAST(C1 AS VARCHAR(3)),3)

FROM #TestTable;

SET STATISTICS TIME OFF;

PRINT '

========== Double STUFF Method =========='

SET STATISTICS TIME ON;

SELECT @Bitbucket = STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))

FROM #TestTable;

SET STATISTICS TIME OFF;

PRINT '

========== Integer Math RIGHT(RIGHT) Method =========='

SET STATISTICS TIME ON;

SELECT @Bitbucket = RIGHT('000000000'+RIGHT(C1*1000+C2,9),9)

FROM #TestTable;

SET STATISTICS TIME OFF;

PRINT '

========== Alternate Math + Right Method =========='

DECLARE @Billion INT;

SET @Billion = 1000000000;

SET STATISTICS TIME ON;

SELECT @Bitbucket = RIGHT(@Billion+(C1*1000)+C2,9)

FROM #TestTable;

SET STATISTICS TIME OFF;

PRINT '

========== Second Alternate Math + Right Method =========='

DECLARE @Thousand INT;

SET @Thousand = 1000;

SET STATISTICS TIME ON;

SELECT @Bitbucket = RIGHT(@Billion+(C1*@Thousand)+C2,9)

FROM #TestTable;

SET STATISTICS TIME OFF;

(1000000 row(s) affected)

========== Traditional RIGHT+RIGHT Method ==========

SQL Server Execution Times:

CPU time = 3093 ms, elapsed time = 3341 ms.

========== Double STUFF Method ==========

SQL Server Execution Times:

CPU time = 3547 ms, elapsed time = 3559 ms.

========== Integer Math RIGHT(RIGHT) Method ==========

SQL Server Execution Times:

CPU time = 2391 ms, elapsed time = 2395 ms.

========== Alternate Math + Right Method ==========

SQL Server Execution Times:

CPU time = 2062 ms, elapsed time = 2070 ms.

========== Second Alternate Math + Right Method ==========

SQL Server Execution Times:

CPU time = 2235 ms, elapsed time = 2250 ms.