Jeff Moden - Thursday, October 12, 2017 11:41 PM
The results I see follow a slightly different pattern. The CASE version is the slowest (and most variable), but the MATH OVERRUN method performs little differently from the remainder.
========== PRECAST Method ===============================================================
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 360 ms, elapsed time = 357 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 361 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 360 ms.
Batch execution completed 3 times.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
========== DATENAME Method ===============================================================
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 360 ms, elapsed time = 356 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 358 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 357 ms.
Batch execution completed 3 times.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
========== MATH OVERRUN Method ===============================================================
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 360 ms, elapsed time = 359 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 372 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 360 ms, elapsed time = 363 ms.
Batch execution completed 3 times.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
========== CASE Method ===============================================================
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 482 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 438 ms, elapsed time = 442 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 448 ms.
Batch execution completed 3 times.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
========== CONVERT CHAR(2) Method ===============================================================
Beginning execution loop
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 343 ms, elapsed time = 349 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 360 ms, elapsed time = 363 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 343 ms, elapsed time = 352 ms.
Batch execution completed 3 times.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.