How do I place leading zeros for numbers less than 10 without affecting those 10+?

The safest way is probably to only add zeroes when the length of the column is 1 character:

UPDATE
   Table
SET
   MyCol = '0' + MyCol
WHERE
   LEN(MyCol) = 1;

This will cover all numbers under 10 and also ignore any that already have a leading 0.

EDIT

To just select the data try:

SELECT
   MyColPadded = CASE WHEN LEN(MyCol) = 1 THEN '0' + MyCol ELSE MyCol END
FROM
   MyTable;