SQL Server 2005 Identity with customized values.

Posted on 2010-01-06
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 142

    Accepted Solution

    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 92

    Assisted Solution

    by:Patrick Matthews
    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.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This article describes some very basic things about SQL Server filegroups.
    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.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now