SQL Server Identity Column Leading Zeros

Posted on 2010-08-27
Last Modified: 2012-05-10
Can I have my (int) identity column 6 numeric characters?

like this:
Question by:webdork
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 250 total points
ID: 33546768
a identity field is numerical data type (int ... )numerical data types don't allow leading "0", you need 2 columns ... 1 being int/identity, and the second one being a computed column, using the other column, and formatting it as you need it:
alter table yourtable add formatted_key = right( '000000' + cast(your_identity_key as varchar(10)), 6)

Open in new window


Author Comment

ID: 33546812
I see... kinda.

I'm guessing if i run the statement above (with correct table and column names) it will create a new column called formatted_key and populate with correct values at this point in tome. How do i keep the new column updated as new records are added?
LVL 30

Expert Comment

ID: 33546818
...or just format the data in your application when you display the value.
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 250 total points
ID: 33546823
leave it as identity (int) and use sql to get it formatted

select id, right('000000'+cast(id as varchar),6) as fid from myTable
LVL 58

Expert Comment

ID: 33546904
> it will create a new column called formatted_key

> and populate with correct values at this point in tome.
no- a COMPUTED column is a "virtual" column that is only calculated when used. nothing is stored *

> How do i keep the new column updated as new records are added?
a COMPUTED column is "virtual", and only calculated when used, so whenever you use

select identity_key, formatted_key ....

formatted_key will automatically expand to "right( '000000' + cast(your_identity_key as varchar(10)), 6)" and give you the result desired.

* You can make a computed column stored by creating an index on it.

Author Comment

ID: 33546997

Thank you so much for your detailed valuable response. I'd assigned points before I saw your input.

LVL 58

Expert Comment

ID: 33547597

It is no problem.  Just wanted to make sure you understood.


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Format Output of Select Statement 2 38
store vs query adhoc - no show rows 4 37
Does INTERSECT return opposite from FULL OUTER JOIN? 4 40
relocating SQL 2000 18 31
I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question