identity insql server

  • i want to put identity to a column bt tht column datatype must be varchar and tht identity must be starts with P001.pls help me

    thank u

  • First, be careful of your terms. "Identity" means something specific in sql server. In this case, you can't use it directly, since it must be a number or a guid. It exists because to rapidly assign unique values to a table with little overhead. It was not intended to be a user visible field or hold updatable information. Avoid exposing it. What you want is a unique key.

    Some options:

    1) Insert trigger. Create a column of any type, make it a unique index (possibly the Primary Key) and you can assign a value programatically, or get the last with a lookup. You can store the last value somewhere, for example in a table. I worked on an "Enterprise" application that had 1 table with 1 row for each application table to hold the last value. Not suggesting that, since I thought it silly, but you can go that route. I've also seen another that had a table with 1 row per table with the last value. Lock the record to enforce integrity. It does serialize inserts.

    2) make a compound key, with say (assuming the value changes), a char field containing the prefix, and an int identity field. Then build a view that casts them together as a char.

    There's more, but this is off the top of my head. Both have some performance impacts, and the shape of your data and usage patterns will indicate the best path.

  • I'd use an int identity column and then a computed column or a view that concatenates them.

    You can't do a varchar auto-incrementing field.

  • thank u sir

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

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