SQL Server Performance Forum – Threads Archive
duplicate entries created even it is autogenerate
duplicate entries created even it is autogenerate or autoincrement? how did this happenedscenario: the field entryNo is programmed to generate new entry number every time a user adds a new record. ‘the statement used is… … select max(entryNo) from <table>… ‘the formula used is… entryNo = entryNo + 1 the application is being used on a local network. it is possible that the cause could be 2 users added a new record exactly at the same time and the application created the same entryNo? any help will do. thanks rizbon
in such scenario what u should do is enable Identity on EntryNo column. Read IDENTITY (Property) (Transact-SQL) and Create table syntax in BOL Madhu
ok! but sir, can i ask if it the caused I posted be possible? 2 users created new record at the same time. maybe i can work on the codes again and the one you suggested but im really curious about the cause. please advise thanks! rizbon
it is possible if 2 users adds a new record at the same time ie.
user A tries to add a record at 9:00:00am
user B also tries to add a record at 9:00:00am both connections could run the following command at the same time
select max(entryNo) from <table>
this results in the same entryNo selected for user A and user B (because none of them have done an insert yet), thus duplicate record is added in the database this can be solved with proper locking, but the better solution is to use identity column as madhuottapalam suggested
And make sure you also add a unique constraint to the identity column.
THANK YOU SO MUCH PEOPLE! that is truly a big issue for me. thank you so much, wish i could help you too someday! Blessed Be! rizbon
Select MAX() doesn’t scale too well when tables get large…
And as noted, you risk more than one reading the same MAX() value. If you want to roll your own counter that is stored in a table (not an identity), then here’s a way to do that.
CREATE proc dbo.getNextID @tabname sysname, @nextid int OUTPUT
as
–file:getNextID.sql
–why:generic proc that returns the next avilable id-counter for the specified table
–Usage:
–declare @varForNewId int
–exec getNextID ‘tableName’, @varForNewId OUTPUT
[email protected] now contains the new id…. –table:uniqueIdxxUx- table to keep the counter in – 1 row for each counter and table
—
–by:Kenneth Wilhelmsson / Cybernetics
–when:2001-02-09- first version
set nocount on
declare @err int,
@rc int
— check that counter for this table exists
if not exists ( select * from dbo.uniqueId where tablename = @tabname )
begin
set @err = 1
goto errhandler
end — get the next id
update uniqueId
set @nextid = nextId = nextId + 1
where tablename = @tabname select @err = @@error, @rc = @@rowcount
if (@err <> 0) goto errhandler
if (@rc <> 1) goto errhandler return @err errhandler:
if ( @@trancount > 0 ) ROLLBACK TRANSACTION
declare @errmsg varchar(255)
[email protected] = ‘procedure: ‘ + object_name(@@procid) + ‘ *** FATAL ERROR *** ‘
raiserror(@errmsg, 16, -1) with log return @err
GO /Kenneth
whoa! this is totally a different way to do it.
so how does it work! every time i add a new record, it fetches the new ID from the separate table that produces a unique ID. How can it limit from producing the same ID from 2 users. *i’m sorry your solution is way too advance for me, but i want to try this, can you give mefurther explanation if it is not too much for you. thanks rizbon
every time i add a new record, it fetches the new ID from the separate table that produces a unique ID. How can it limit from producing the same ID from 2 users
It won’t. The unique ID is stored in table uniqueId.
KH
]]>