Add new Column with Autonumber

[mattm31]

Hi

I have a SQL 2000 table with around 50,000 records, I have been asked if we can add a Auto Number to any new records added.

I have added a int Data Type column which works fine but i have now been told that it needs to be 8 digits so starting at 00000001.

Not sure how to do this if anyone can help.

Thanks

Matt

 

[bborissov]

Identity field is what you need. Int - can hold numbers up to 2 000 000 000 what is enough for you.
About leading zeroes that is a job for your front end how you will present that data to the customers.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Might be a good place to use a computed column.

Leave the new column as is, but add another computed column to the database. This new column will be computed from the value of the int column.

Alter Table [blue]TableName[/blue] Add [blue]ZeroPaddedColumnName[/blue] As Right('00000000' + Convert(VarChar(8), [blue]YourIntIdentityColumnName[/blue]), 8)

Computed columns are not actually stored in the table. Whenever you use this column, it will be calculated using whatever formula you specify. If you want to link this column to another table, don't. Instead, use the int column because your storage will be less and your performance will be better.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom