[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

SQL Server Identity Column Leading Zeros

Can I have my (int) identity column 6 numeric characters?

like this:
000001
000002
000003
000010
000011
000100
0
webdork
Asked:
webdork
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
a identity field is numerical data type (int ... )numerical data types don't allow leading "0" ...so, 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

0
 
webdorkAuthor Commented:
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?
0
 
MlandaTCommented:
...or just format the data in your application when you display the value.
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
HainKurtSr. System AnalystCommented:
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
0
 
cyberkiwiCommented:
> it will create a new column called formatted_key
yes

> 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.
0
 
webdorkAuthor Commented:
cyberkiwi:

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

D
0
 
cyberkiwiCommented:
webdork,

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

Cheers
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now