How do I preserve the leading zeros when I insert a number into this table?
0023 is not a number. It's a string. 23 is the number. SQL Server is able to recognize that those extra zeros are not needed to define the number, so it ignores them. If you want to dispaly a value as 0023 to the application, I'd suggest doing the formatting on the application side. That way the number stored in SQL Server is still a number if you want do addition, aggregation, or other calculations. If you really have to store it as '0023', you need to convert it to a character field; char, varchar, nvarchar, nchar.