How can I make SQLSERVER auto increment in 5 digit format?

Question

In SQLSERVER my table has auto increment ID for each record, fairly straight forward. How do I set this ID number to be always 5-digit (I am never going to go over 99999 in this table)? So, record 1 is 00001, record 596 is 00596, 4871 is 04871,and so one... How can i set the 000001 formate on the Id Field.

possible duplicate of [SQL Identity with leading padded zeros](http://stackoverflow.com/questions/3429284/sql-identity-with-leading-padded-zeros) — Radu Gheorghiu, Aug 22 '13 at 07:26

score 5 · Accepted Answer · answered Aug 22 '13 at 07:28

5

CREATE TABLE T (
   _ID int IDENTITY(1,1) not null,
   ID as RIGHT('0000' + CONVERT(varchar(5),_ID),5),
   /* other columns */
)

Although generally, you'd want to keep formatting concerns outside of the database - especially since IDENTITY columns shouldn't be shown to users.

answered Aug 22 '13 at 07:28

Damien_The_Unbeliever

234,701
27
340
448

1

Massive +1 for **"keep formatting concerns outside of the database"** – gvee Aug 22 '13 at 09:55

How can I make SQLSERVER auto increment in 5 digit format?

1 Answers1