SQL Server ID Starting with Zero's

Question

I am creating an invoice table and I'd like the ID to start with zero's because I will eventually concatenate the invoice # with a varchar that will stay the same. For example, here is my table so far:

create table MCE_IDTest_av
(
    invoice_id_num int not null Identity(100076,1) primary key,
    invoice_id as ('MCE' + CONVERT(varchar, invoice_id_num)),
);

The invoice_id will be listed as "MCE100077", "MCE100078", etc. But, I'd like it to be "MCE0010076". Is there a SQL number datatype that start with zero's?

Possible duplicate of [Formatting Numbers by padding with leading zeros in SQL Server](https://stackoverflow.com/questions/9520661/formatting-numbers-by-padding-with-leading-zeros-in-sql-server) — Igor, Sep 21 '18 at 15:43
Use `FORMAT` to get a formatted string from a number in SQL Server: `format(invoice_id_num, '0000000')`. — Thorsten Kettner, Sep 21 '18 at 16:06

score 1 · Accepted Answer · answered Sep 21 '18 at 15:44

This is no datatype that will pad leading 0s. That type of thing must be done with a character datatype. But you can accomplish this easily.

Also please be sure to always specify the length of varchar data. https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length

create table MCE_IDTest_av(
invoice_id_num int not null Identity(100076,1) primary key,
invoice_id as ('MCE' + right('00000000' + CONVERT(varchar(10), invoice_id_num), 7)),

);

SQL Server ID Starting with Zero's

1 Answers1