Is there a better way to check the database for a unique value that works for both update and insert then below

Question

IF EXISTS(SELECT * FROM MyTable WITH (NOLOCK) WHERE MyTable.RecordId <> @RecordId AND MyTable.UniqueColumn = @UniqueValue)
        BEGIN
            SELECT 1
        END
        ELSE
        BEGIN
            SELECT 0
        END 

NOTE: I use the NOLOCK so it checks for pending inserts and update (i.e someone update / changing the UniqueColumn)

NOTE: I use the "IF EXISTS(" so it stops search after finding the first match

NOTE: I use the "MyTable.RecordId <> @RecordId" so that it does not find itself as a match.

NOTE: If inserting the recordId would be negative one or zero.

Answer 1

Don't know if it's "better", but it's more to-the-point:

select
  case 
    when
      exists ( select * from ... where ... )
    then 1 
    else 0 
  end as IsThereOrNot

...and can be used with other expressions (if you need 'em).

Note: select * is appropriate for existence checks, and nolock isn't going to change anything - there's an implicit transaction for the scope of the select.

I think it's better (easier to use the results by your consumers) if you name your columns (IsThereOrNot in the example)