Adding max(value)+1 in new row, can this be a problem?

In a SQL Server table I have the following 2 columns:

RowId: primary key, numaric, identity column and auto insert.

MailId: Non key, numaric, non identity and non auto开发者_运维问答 insert.

Mail Id can be duplicate. In case of new MailId I will check max(MailId)+1 and insert it in new row and in case of duplication value will be coming as parameter.

Logic looks fine but here is an issue, I was just considering (yet chacnes of accurance are ver low) In the same time there can be two different new MailId requests. Can this casue logical error ? For example when code checked max(MailId)+1 was 101 and I stored it in a variable but may be before next insert statment executs a new record inserted in table. Now max(MailId)+1 in table will be 102 but value in variable will be 101 ?

Any suggestion please I want to control this error chances as well.

EDIT

(I am not using identity(1,1) because I also have to pass custom values in it)

Why would you use a custom-rolled Identity field when there is such a great one already in SQL Server?

Just use INT Identity (1,1) for your ID field and it will automatically increment each time a row is inserted. It also handles concurrency much better than pretty much anything you could implement manually.

EDIT:

Sample of a manual ID value:

SET IDENTITY_INSERT MyTable ON

INSERT INTO MyTable (IdField, Col1, Col2, Col3,...)
VALUES
(1234, 'Col1', 'Col2', 'Col3',...)

SET IDENTITY_INSERT MyTable OFF

You need to include an explicit field list for the INSERT.

Use OUTPUT on your insert to be sure that you have the right value. If you insert and then select MAX, it is possible that someone could "sneak" in and end up with duplication. That is, you insert MAX + 1, at the same time someone else inserts MAX + 1 then you select MAX and they select MAX, you both have the same value. Whereas if you INSERT and use OUTPUT, you'll be sure that you're unique. This is rarely a problem, but if you have a lot of activity, it can happen (speaking from experience).

EDIT

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
OUTPUT inserted.BusinessEntityID,
       deleted.VacationHours,
       inserted.VacationHours,
       inserted.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO