Q:

How do I preserve the leading zeros when I insert a number into this table?

I have inserted two records into a table.
create table num(id int)
insert into num values(0023)
insert into num values(23)
select * from num

When I query them, they all display as 23. It means that SQL Server ignores the leading 0s. What's the mechanism behind that? How can I have SQL Server return the values as I inserted them (i.e. 0023 and 23)?

A:

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.

A:

It was already said in other answers that 00023 is a number; I just want to add that you can use computed columns to show that number using custom format. For example, 
create table num_table(id int not null primary key identity(1,1),
num int, leading_zeros smallint,
constraint chk_leading_zero_nonnegative check (leading_zero>=0),
num_formatted as replicate('0',coalesce(leading_zeros,0)) +cast(num as varchar(10)));
insert into num_table(num,leading_zeros) values(23,2) ;
select num_formatted from num_table; -- output '0023'

