How to auto increment with character + integer?

Hi,

Can anyone help me? I have created a table and with a primary key called "ID".
However, I want the "ID" be auto increment as well.

Here is my question, how can I do in the following format:

"PIC0001", "PIC0002", "PIC0003", ......... etc.

Anyone can help me?

Thanks.You can create a calculated field as part of a view, using the number from the IDENTITY column appended to the string "PIC".

SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID|||i'm a rookie...can u explain more detail for this one pls? SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID|||SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID

'PIC' is the literal you apparently want at the beginning of the string. ID would be a column of type INT, set to IDENTITY. REPLACE(STR(ID,4),' ','0') takes an int number, like 12, and changes it to '0012', thus for a row with an ID of 12, you would get a column called AlphaID with a value of 'PIC0012'.|||hello
thank you for the support

you mean that i have to make two fields one called ID of type int and the other called alphaid
of type nchar and put the default value of this field as you say?

or i have to put the statment SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID in the code of the application?

thank you again for the help|||If the prefix is ALWAYS 'PIC', then I would just create a VIEW that has as part of it that SELECT statement. If it varies, then I would create the table with the ID field and the Prefix field, and then combine them in a view.|||You could also implement this as a computed column within the tabl itself.

This approach might add more overhead though, so it depends on how many of your selects would actually be accessing this field directly.

One potential advantage to having the value as a computed column is that, assuming the computed column meets certain criteria, you can index the column. If you are planning on retrieving rows based on this field, this could work to your advantage.|||Actually, given triggers, you could certainly create a "real" field to hold the value and have it populated in the trigger.|||But would you gain anything by going the trigger route? Wouldn't that just add overhead to your inserts? Or would the overhead be comparable to the overhead assiociated with a computed column (at least when maintaining an index on that computed column).

To my mind, it is a LOT easier to configure a computed column for this solution than it is to program a trigger. But, then again, I rarely use triggers, so I might not be the best resource here. All things being equal though (and I'm NOT saying they are; you might have a better idea than me on that one), I'd go with the solution that's easier to implement.|||Presuming the ID number part of the computed column never changes, you would need only an INSERT trigger. In most applications, the number of INSERTs vs. the number of SELECTs is very small, and so the overhead of doing the insert trigger is really small.

I certainly agree that often easier is better. I have not compared performance of these two solutions (though I would likely do a column fed by a trigger).|||I might suggest you use IDENTITY as PK and add 'PIC' and '000..' to the layer(s) that require it.

This is based on the presumption that PIC000n... is a (surrogate) PK with no "real" business meaning and using only int will make the key more efficient.|||I agree, to an extent. If multiple systems, or layers, might be touching this data, then it CAN add some consistency to be able to define this formatting in one central location.

While you could easily add this computed "key" as a property of a class, for example, you might then need to write numerous report which use this key; some might be Crystal reports, some SQL Server reporting services. Some might be generated ad hoc by the end user. IF this is a business-level way of identifying these records, I don't necessarily see a problem with allowing that identifying characteristic to be stored with the record itself.|||hello
thank you for the support
but can you give me more details about how can i do that?

thank you again for th help anf for the time|||Hi,
I have similar question, I have created a table and with a primary key called "ID".
However, I want the "ID" be auto increment as well.
Here is my question, how can I do in the following format:
"1", "2", "3", ......... etc.
Anyone can help me?
Thanks.|||If you just need numbers to autoincrement, set the column to be an IDENTITY column.