Most efficient method for adding leading 0's to an int in sql

That is pretty much the way: Adding Leading Zeros To Integer Values

So, to save following the link, the query looks like this, where #Numbers is the table and Num is the column:

   SELECT RIGHT('000000000' + CONVERT(VARCHAR(8),Num), 8) FROM #Numbers

for negative or positive values

declare @v varchar(6)
select @v = -5

SELECT case  when @v < 0 
then '-' else '' end + RIGHT('00000' + replace(@v,'-',''), 5) 

Another way (without CAST or CONVERT):

SELECT RIGHT(REPLACE(STR(@NUM),' ','0'),5)

If you can afford/want to have a function in your database you could use something like:

CREATE FUNCTION LEFTPAD
           (@SourceString VARCHAR(MAX),
            @FinalLength  INT,
            @PadChar      CHAR(1)) 
RETURNS VARCHAR(MAX)
AS
BEGIN
  RETURN
    (SELECT Replicate(@PadChar, @FinalLength - Len(@SourceString)) + @SourceString)
END