Lets say i have a multi-threaded web service to look for an old Identity record in order to create a new one based on it, and to remove the old one if it existed in the first place by using 2 Public methods: - CreateIdentity - DeleteIdentity
even tough this scenario should be avoided since we all can think of the problems that will come from this, i was wondering what would happen if i send the create and then the delete right after it?
things i would like to know specifically: - Has 1 of the 2 a higher priority so that for example all creates must precede deletes on a table? - Is a delete action always faster than a create when we rule out prioritizing?
I can't tell how familiar you are with multi-threading so I'm not sure at what level to target this answer. The simple answer to your questions are;
SQL Server manages concurrency, so you don't need to worry about being able to try and do both actions at the same time. If two actions on the same object were to happen at the same time the lock manager would handle any conflicts.
As to why either could be faster, I'm sure that one operation could be theoretically faster than the other, but in practice faster probably means 'finishes first', in which case real world delays like waiting, blocking and scheduling will make the difference.
SQL Server doesn't have the facility to prioritise commands, even if it did the point above still applies.
If you need the create to happen before the delete then you must manage this yourself. A single call to a stored procedure would be ideal, but if you have to use 2 calls then you must wait for the first to return before making the second. If you need both actions to be atomic then you will have to use a transaction.