unique id generator

  • Krishna Roopa

    Old Hand

    Points: 393

    The identity column provides the value as integers. We need to generate id similar to 'AAA0001' where the last four digits has to be numeric and unique..

    Any possibilities with identity column

  • Chris Hedgate

    One Orange Chip

    Points: 25041

    Use a computed field. Something like:

    CREATE TABLE foobar

    (

    a int IDENTITY(1,1)

    , b char(3)

    , c AS (b + REPLICATE('0', 4 - LEN(CONVERT(varchar(4), a))) + CONVERT(varchar(4), a))

    )

    Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

  • Andy Warren

    SSC Guru

    Points: 119655

    Could you tell us why you need to do this? Im a firm believer in integer keys, faster and simpler. If you're concerned about key conflicts from multiple locations, uniqueidentifers are the ticket.

    Andy

  • don1941

    SSCarpal Tunnel

    Points: 4183

    Sounds like you're trying to create an "intelligent key." In data modeling this is discouraged. Your key values should be something that is really unique and not "made up of" other attributes. Combining attributes into a single column violoates 1st normal form. If you want to create this an attribute for display only, that's probably ok. You could create the column as part of a view so it's only generated when selected.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply