identity column start with 001 instead of 1

[fredong]

I have a field with identity column as below

create table A (
Rowcount identity int (3,1)
)

How can I start the identity column with 001 instead of 1
and I want it to look like the right column instead of the left column

001 1
002 2
003 3
004 4
005 instead of 5
006 6
007 7
008 8
009 9
010 10

Can sombody help. Thanks.

 

[jby1]

You can't have leading zeros on an integer.

 

[fredong]

but identity columns only takes in integer. What would you recommend? Thanks.

 

[swampBoogie]

Handle it when presenting data

select case when RowCount < 10 then '00'
            when RowCount < 100 then '0'
            else '' end +
       cast(rowCount as varchar(12)), <rest of query>

 

[SQLSister]

Personally I wouldn't handle it in the data as that introduces inefficiency iand increases the possibilities for introducing inconsistencies ( by forgetting to sdo it sometimes). I would tell the client that having nyumbers which act that way will slow down the system and suggest they start numbering at 100 if it is a big deal to them to not have 1, 2,3. etc.

The other alternative is to create the numbers yourself manually which is also not a process I recommend becasue it introduces a whole host of unnecessary issues that have to be resolved to ensure that a unique number is always created. PLus you would then have to store this as string data which is less efficient for joins.

A third possibility is to have two fields, one which is the true identity and used for joins and forefien keys and one which creates the display number. You could have this field automatically insert the proper string data based on the identity field in a trigger. at least this way you arenot using a function every time you select fromthe field. It ddoes take more storage space though and you would always want to join to the main table from any child tables to get this information, but its doable.

 

[SQLBill]

Why are you required to do it that way? If it's so the numbers are properly aligned, you could probably have your front-end application right-justify the column instead of adding the leading zeros.

Also, if you are going to add leading zeros, how will you know how many leading zeros you need? Today it might only be two, but in the future you might have 1000 rows and then you'll have:

001
...
1000

-SQLBill

 

[fredong]

this is for EDI and it required a fixed of 5 numbers so I need at least 4 leading zeros just like below

00001
00002
so on until 00010....00100...01000...10000 then it goes back to 00001 again.

 

[SQLSister]

YOu can't use an identity field for that because the numbers are not unique. You will have to come up with some wa to programmically assign the numbers and make them a charcter data type rather than a number.

 

[SQLBill]

As SQLSister said, can't do it with an identity field. When you get to 99999....it WON'T start over again because that number was already used. Even if you delete 00001 it can't reuse it. Use a CHAR(5) field.

-SQLBill

 

[fredong]

so what would you recommend.. what programming method should I use.. any example.Thanks

 

[SQLBill]

An option is to:

1. Create a column MyIdentity (or whatever name you want) of CHAR(5)
2. Create a second column TrueIdentity (or whatever) which is a real Identity column
3. Create a trigger that when data is inserted the trigger will fire and;
4. get MyIdentity WHERE TrueIdentity = MAX(TrueIdentity)
5. WHILE MyIdentity < '99999' insert new MyIdentity of that MyIdentity + 1.
6. When MyIdentity = '99999', start over at '00001'


So:

MyID TrueID
99998 99998

The MAX(TrueID) = 99998, so check that MyID against the WHILE. Yes it's less than 99999 so:

MyID TrueID
99998 99998
99999 99999

MAX(TrueID) = 99999, so check that MyID (99999) against the WHILE. It's equal to 99999, so:

MyID TrueID
99998 99998
99999 99999
00001 100000

MAX(TrueID) = 100000, so check that MyID (00001) against the WHILE. It's < than 99999, so.........

-SQLBill

 

[fredong]

Thanks.