Jeff Moden (8/30/2013)
If you're interested in speed, STR isn't the way to do it.
It's unlikely that the speed difference would be significant. Yes, if you need to do this on 10 million rows on straight SELECT from a single table, you might see something. If you instead query umpteen tables with a suboptimal query plan, this will be one your smallest problem.
And since it does a FLOAT conversion in the process, it can slip into the same approximation "problems" as FLOAT (although not likely with only 13 digits).
True, the argument will be converted to float, and this could pose problems with you are working with 16-digit bigint numbers you want to pad to 18 digits. An integer value is typically converted to float with the exponent as 0, meaning that there is no loss of precision.
I like str() + replace() because is short and concise.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]