Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2004-10-06 : 15:59:50
|
| I apologize if this has already been asked here many times, but my searches here keep timing out. What is the best way to create a varchar identity column? I need the data to be in the form 'ABC12345' and to increment only the numbers for each insertion. I think I can do a trigger so that the field gets updated for the newly inserted row (I'll have to turn off IDENTITY if I do it that way), by selecting max(timestamp) or something like that, but I'm hoping there's an easier/better way. |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-10-06 : 19:11:01
|
| Why not have 2 columns ? ABC in 1 and an identity in the otherbut if it is always ABC, then return it as a literalor am I missing the point ?Graham |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-10-07 : 10:29:43
|
| My partner in crime says it will be much easier for him to do his Access form if I make a column that has the full string. I guess I'll use an Identity column and set a trigger that appends the Identity value to the 'ABC' and puts it in a separate column. But then if we accidentally delete a row, we won't be able to add it back. I'll have to think about that. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-07 : 10:35:09
|
so don't accidently delete a row...  have a column Deleted which you set to true or some other value if it's an int when you delete a record...that way nothing will be deleted.Go with the flow & have fun! Else fight the flow  |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-10-07 : 10:43:51
|
| Good idea. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-07 : 11:34:24
|
| create a VIEW of the table which calculates and returns the VARCHAR identity.SELECT 'ABC' + RIGHT('00000' + CONVERT(VarChar(5), ID)) as Fancy_VarChar_ID, A.*FROM YourTable Athen base your form on that view instead of the table. never let "it will be a little easier on the presentation layer" sway you from a good database design. Normalization often makes presentation a little more complicated, too.- Jeff |
|
|
|
|
|
|
identity column with varchar