May 13, 2008 at 11:15 pm
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
May 15, 2008 at 7:39 am
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.
May 15, 2008 at 9:02 am
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.
May 21, 2008 at 10:22 pm
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