Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Identity Column Leading Zeros

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

like this:
000001
000002
000003
000010
000011
000100
0
Comment
Question by:webdork
[X]
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
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 33546768
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
 

Author Comment

by:webdork
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?
0
 
LVL 31

Expert Comment

by:MlandaT
ID: 33546818
...or just format the data in your application when you display the value.
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 59

Assisted Solution

by:HainKurt
HainKurt earned 1000 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
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33546904
> 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
 

Author Comment

by:webdork
ID: 33546997
cyberkiwi:

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

D
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33547597
webdork,

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

Cheers
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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