SQL Server 2005 Identity with customized values.

Posted on 2010-01-06
Medium Priority
Last Modified: 2012-05-08
I have 1 column with Identity = true (int)
Normally, I will increase like this: 1,2,3,4,5,6,7...

But how can I make it to increase like this:  code1, code2, code3, code4....

Question by:Mr_Bach
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1600 total points
ID: 26188704
you cannot in the identity field.
however, you can use a computed field to append 'code' with the identity field:

ALTER TABLE yourtable ADD new_field = 'code' + cast(your_identity as varchar(1000)
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 400 total points
ID: 26190048
Hello Mr_Bach,

angelIII has correctly shown you how to do it, but IMHO it is a really bad idea:

1) If you are always using the same text prefix, then why bother storing that text prefix in the database at all?

2) If you ever sort on that calculated field, you can get anomalous results such as code10 showing up before
code2.  Unless, of cource, you format the codes to look like code000010 and code000002, but then you would
have to make guesses on how many digits you will need over the life of your application.



Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

839 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