i want to have two identity columns in my table .one is Primary key ObjectID.2nd one is ObjectName,should like R0000123.i want both these columns as Auto Generated values. i dont want to use any triggers,Sequences on the table. Can you please suggest me how to create the table.
Only one column per table can have an IDENTITY column. If you were in SQL Server 2012 or better, you could take advantage of the SEQUENCE object, but since you're in 2008, that's not possible. Your best bet is either a trigger or a computed column. You can have a table that stores the current max value. Read from that to get the latest for your query and update it after you're done. I know you say you don't want a trigger, but a trigger would work better in this situation. Another option is to break the table apart so you can have an identity column in each table.