What If I already have a Identity field in the table as the PK, which is need as a Foreign key on a Join within the table? I know I could build a trigger to do max(field), but this might be slow, not sure, am I able to have two identity fields in the table?
I know I could build a trigger to do max(field), but this might be slow, not sure, am I able to have two identity fields in the table?
I did this once and I think the trigger pulled it from the PK's identity field, but in that case they were functionally related. Or maybe it was procedural code... Another option is to create a dummy table with its own identity field, and just immediately delete the useless rows. It's silly, but it'll work.
...and now that I think about, why would you ever want to order items by an identity?
Your business requirements sound totally f'd up.
I include myself in this indictment because I've done it plenty of times and I was initially going to say "get Oracle and use sequences": Why do we answer a "how do I do X with Y" with "X is a stupid idea" or "don't use Y, use Z"? I mean, we're engineers here, why can't we give a body a straight answer?
You can't have two identites on a single table since identity is a table property. I too would ask why you need a second one - why not use the PK? Perhaps some DDL, sample data and expected return might help us understand what you are trying to do - there might be a better way.