SQL Server: Column Value with Leading Zeros

Till date I was using old CASE option to add zeros before a digit to get all values with same length for columns like social security number but today SQL Expert Shakeeb Younas shared a very simple method to achieve above mentioned goal.

DECLARE @Emp TABLE ( SSN VARCHAR(9) )

INSERT  INTO @Emp

        SELECT  '1'

        UNION ALL

        SELECT  '654'

        UNION ALL

        SELECT  '824741'

        UNION ALL

        SELECT  '123456789'

        UNION ALL

        SELECT  '37'

-- Get SSN with leading zeros       

SELECT  REPLACE(STR(SSN, 9), ' ', '0') AS SSN

FROM    @Emp