How can I determine if a database record already exists?

The most efficient way to determine if a record exists is to use the SQL Server EXISTS function. For example, the following SQL statement determines if a record exists in the Users table with a UserID value of MM:

SELECT CASE WHEN EXISTS
   (SELECT *
   FROM Users
   WHERE UserID = 'MM') THEN 1 ELSE 0 END AS UserIDExists