In the interest of science...

--===== 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.

SELECT TOP 1000000

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

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

INTO #TestTable

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 '========== Less Traditional RIGHT+RIGHT Method =========='

SET STATISTICS TIME ON;

SELECT @Bitbucket = RIGHT(REPLICATE('0',6)+CAST(C1 AS VARCHAR(6)),6) + RIGHT(REPLICATE('0',3)+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 '========== 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 '========== Integer Math RIGHT(CAST) Method =========='

SET STATISTICS TIME ON;

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

FROM #TestTable;

SET STATISTICS TIME OFF;

PRINT '========== REPLACE(STR) Method =========='

SET STATISTICS TIME ON;

SELECT @Bitbucket = REPLACE(STR(C1*1000+C2,9),' ','0')

FROM #TestTable;

SET STATISTICS TIME OFF;

Results:

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

SQL Server Execution Times:

CPU time = 515 ms, elapsed time = 505 ms.

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

SQL Server Execution Times:

CPU time = 546 ms, elapsed time = 547 ms.

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

SQL Server Execution Times:

CPU time = 718 ms, elapsed time = 718 ms.

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

SQL Server Execution Times:

CPU time = 452 ms, elapsed time = 456 ms.

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

SQL Server Execution Times:

CPU time = 452 ms, elapsed time = 457 ms.

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

SQL Server Execution Times:

CPU time = 390 ms, elapsed time = 389 ms.

========== REPLACE(STR) Method ==========

SQL Server Execution Times:

CPU time = 1482 ms, elapsed time = 1472 ms.