SQL Identity with leading padded zeros

Question

I have marked a column as Identity in my table

create table Identitytest(
    number int  identity(1,001) not null,
    value varchar(500)
)

I need the identity column to be incremented as 001,002,003, etc.

The database shows that it is inserting as 1,2,3, etc.

How can this be done?

Answer 1

As the others have already rightfully pointed out - an INT never has leading zeroes - it just holds the value, that's all (and that's good that way).

If you need some additional formatting, you could always add a computed column to your table, something like:

ALTER TABLE dbo.Identitytest
  ADD DisplayNumber AS  RIGHT('000' + CAST(number AS VARCHAR(3)) , 3) PERSISTED

This way, your INT IDENTITY will be used as an INT and always contains the numerical value, while DisplayNumber contains 001, 002, ... 014, 015, ..... and so forth - automagically, always up to date.

Since it's a persisted field, it's now part of your table, and you can query on it, and even put an index on it to make queries faster:

SELECT value FROM dbo.IdentityTest WHERE DisplayNumber = '024'

And of course, you could use just about any formatting in the definition of your computed column, so you could also add a prefix or something:

ALTER TABLE dbo.Identitytest
  ADD DisplayNumber 
      AS  'ABC-' + RIGHT('000' + CAST(number AS VARCHAR(3)) , 3) PERSISTED

So in this case, your DisplayNumber would be ABC-001, ABC-002, ... and so on.

You get the best of both worlds - you keep your INT IDENTITY which is numerical and automatically increased by SQL Server, and you can define a display format any way you like and have that available at any time.

Answer 2

If you want to display your number column with leading zeros, just pad it in your SELECT statement. It's a number, it will NOT store with leading zeros as an integer.

SELECT RIGHT('00000' + CAST([number] AS varchar(5)) , 3)
FROM IdentityTest

The 3 is the number of characters you want total in the output display.