Nice question, but not "totally" deterministic in my opinion.

What If I ensure consistency through the transaction isolation level already?

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION

IF NOT EXISTS... INSERT...

COMMIT

Edit: Deleted wrong information from me regarding Performance without indexes