Q:

SQL Server and duplicate inserts prevention

I need to be sure that in the concurrent environment I'll avoid 2 duplicate inserts into the same SQL Server table.
In order to do this, first of all, I try to execute SELECT with: 
WITH (UPDLOCK, ROWLOCK)

For example:
SELECT * 
FROM Products p WITH (UPDLOCK, ROWLOCK) 
WHERE p.groupId = ?1 AND p.classId = ?2 

Right after that if the select above returns 0 records at the same transaction I do the INSERT statement.
Will this approach prevent duplicates on the empty table for the products with the same p.groupId = ?1 AND p.classId = ?2 ?
If no, how it should be implemented in order to avoid the duplicates?

A:

If you are making inserts into a table and you want to ensure that no more than 1 row will have the same groupId and classId I would suggest that you use the Unique constraint in the creation of your table. eg:
CREATE TABLE Products (
    groupId int NOT NULL,
    classId int NOT NULL,
    ...
    CONSTRAINT UC_GroupClassID UNIQUE (groupId, classId)
);

Alternatively you can alter the table as:
ALTER TABLE Products
ADD CONSTRAINT UC_GroupClassID UNIQUE (groupId, classId)

You can find more info on:
https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-unique-constraints
https://www.w3schools.com/sql/sql_unique.asp

