You want to know whether a field value is unique?

SQL Server forces uniqueness if you make the field a primary or candidate key or if you use a unique constraint. There are lots of opinions, but you are probably better not to use a human-entered field for a primary key. A unique constraint makes your intent immediately obvious, and a table can have more than one unique constraint as opposed to a single primary key. All of these techniques mean that if a value is saved that already exists in a different record, the record will not save and you can catch the resulting error.

However, IMHO it is better to check uniqueness as soon as the user enters the value- you can imagine the annoyance if a user enters lots of customer details and tries to save before discovering the customer is already there. You could use a SPT or SP to check for the value and let the user decide whether to pull up the existing details or choose a different value. There is still a chance that 2 users might start adding the same details at the same time, but users aren't as quick to blame the software if that happens.