0

I have a preexisting sqlserver table. Among other fields it has an identity column called as ID which is also a primary key and a RecordNumber column which is a required field. The int value in the RecordNumber column has to be unique. So before inserting a row, I get the max value of the ID column, add 1 to it and then inserting the row with the RecordNumber field = ID + 1. The problem is when two users try to save at the same time, they may get the same ID value and hence will save the same value in the RecordNumber field. Please let me know how to resolve this.

Thanks

user466663
  • 815
  • 4
  • 18
  • 40
  • 2
    Why have you got both an `ID` field and a `RecordNumber` field? As the ID is the PK this is guaranteed unique already and avoids the concurrency issues. – Martin Smith Aug 26 '12 at 18:31
  • Did you find anything useful **[here](http://stackoverflow.com/questions/12132770/how-to-get-unique-value-from-sqlserver/12136072#12136072)** – testing Aug 30 '12 at 17:32

3 Answers3

1

The simplest and most efficient way to do this is to define that column as *auto increment *

refer

http://www.w3schools.com/sql/sql_autoincrement.asp

knightrider
  • 2,063
  • 1
  • 16
  • 29
  • SQL Server only allows one `identity` column per table. The OP already has an identity column called `ID` so how will this help with the `RecordNumber` column? – Martin Smith Aug 26 '12 at 18:42
  • @MartinSmith There is no proper way, but there are other ways http://stackoverflow.com/questions/4612832/how-to-set-two-columns-in-sql-server-database-as-auto-increment-int-with-increme . All the solutions depends on the why a separate column is needed – knightrider Aug 26 '12 at 18:47
0

You can use the Transaction concept, using Commit and Rollback operations.

Very interesting link on MSDN : http://msdn.microsoft.com/en-gb/library/ms190295.aspx

Bridge
  • 29,818
  • 9
  • 60
  • 82
Aghilas Yakoub
  • 28,516
  • 5
  • 46
  • 51
  • Transactions by themselves don't guarantee anything. At default read committed level two transactions could both read the same maximum value. Needs more detail as to exactly how they can be used. – Martin Smith Aug 26 '12 at 18:36
0

Alternative #1 - Auto Increment a Field

CREATE TABLE SampleTable
(
    P_Id int NOT NULL Identity(1,1),
    FirstName varchar(255),
    PRIMARY KEY (P_Id)
)

Alternative #2 - SequentialID as Default Constraint

CREATE TABLE SampleTable
(
    P_Id uniqueidentifier NOT NULL,
    FirstName varchar(255),
    PRIMARY KEY (P_Id)
)
ALTER TABLE [dbo].[SampleTable] 
ADD CONSTRAINT [DF_SampleTable_P_Id]  
DEFAULT newsequentialid() FOR [P_Id]

Alternative #3 - NewID as Default Constraint

CREATE TABLE SampleTable
(
    P_Id Varchar(100) NOT NULL,
    FirstName varchar(255),
    PRIMARY KEY (P_Id)
)
ALTER TABLE [dbo].[SampleTable] 
ADD  CONSTRAINT [DF_SampleTable_P_Id]  
DEFAULT (newid()) FOR [P_Id]

Sample recommended Stored Proc should be used in case of Muti User Transaction

BEGIN TRY
    SET NOCOUNT ON
    SET XACT_ABORT ON
    Begin TRAN
        --Your Code
    COMMIT TRAN
END TRY

BEGIN CATCH
    ROLLBACK TRAN
END CATCH
testing
  • 179
  • 3
  • 16