Best way prevent duplicate records in a SQL Server database

Use unique constraints on one or more columns in the table. Example:

CREATE TABLE Persons
(
  P_Id int NOT NULL UNIQUE,
  LastName varchar(255) NOT NULL UNIQUE,
  FirstName varchar(255) NOT NULL UNIQUE, 
  Address varchar(255),
  City varchar(255)
)

Alter existing table as below

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

If you are using a front-end application to populate the table in the database. Do your validation select query from the application on the database to check for duplicates before inserting into the database. Using constraints will prevent duplicates by throwing an exception.