How to get max value from table

SELECT STUFF(MAX(ID), 1, 0, REPLICATE('0', 4 - LEN(MAX(ID)))) FROM YourTable;

-- Direct from Table 
DECLARE @Test TABLE (ID INT)

INSERT INTO @Test(ID)
SELECT 10 UNION ALL SELECT 16 UNION ALL
SELECT 20 UNION ALL SELECT 15 UNION ALL
SELECT 5 UNION ALL SELECT 7

SELECT MAX(ID) , RIGHT('0000'+CAST(MAX(ID)AS VARCHAR(10)),4)
FROM @Test

-- Using Variables
DECLARE @MaxValue INT
SELECT @MaxValue=20
SELECT @MaxValue, RIGHT('0000'+CAST(@MaxValue AS VARCHAR(10)),4)