Best way to create next sequence number

Hi,
Can you tell me the best way to get a next unique sequence
integer number which fits in a table. My application supports multi users
and at a single point of time users may add a new row to the table. But it
should fit to generate a unique next available integer value.
For eg: my table contains EMPID int, EMP_Name varchar(50)
EMPID is a primary key.
The main constraint here is i am using Ado.net. So i need to
know the the integer (primary key) value before accpeting the changes to
database. I need to get the next sequence number as there are some child
objects for which i need to use this id. Once all the data is set the user
can select to save the changes which commits the data in the data table with
the db.
Can any one suggest me the best way to do it?
Thanks in advance.
VenkatHi
CREATE PROC Get_nextID
@.ID char(20),
@.nextID numeric(18) output
as
update NextIDs
set @.nextID = NextID = NextID + 1
where ID = @.ID
"Venkat" <stammana@.palantirsolutions.com> wrote in message
news:ucR8AgPSGHA.5908@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can you tell me the best way to get a next unique sequence
> integer number which fits in a table. My application supports multi users
> and at a single point of time users may add a new row to the table. But it
> should fit to generate a unique next available integer value.
>
> For eg: my table contains EMPID int, EMP_Name varchar(50)
> EMPID is a primary key.
> The main constraint here is i am using Ado.net. So i need to
> know the the integer (primary key) value before accpeting the changes to
> database. I need to get the next sequence number as there are some child
> objects for which i need to use this id. Once all the data is set the user
> can select to save the changes which commits the data in the data table
> with the db.
> Can any one suggest me the best way to do it?
>
> Thanks in advance.
>
> --
> Venkat
>
>|||You can use IDENTITY wich is the internal MS SQL Server mechanism and
had recently been normalize in the SQL:2003 ISO standard.
Or you can use your own concept working with a key table like this :
CREATE PROCEDURE P_SYS_DB_CALC_NEW_KEY
@.NOM_TABLE SYSNAME,
@.NEW_VAL INT OUTPUT
AS
-- ****************************************
***************
-- PROCDURE STOCKE DE CALCUL DE CLEF AUTO INCRMENT //
--///////////////////////////////////////////////////////
-- Frdric BROUARD - 2001-09-14 //
--///////////////////////////////////////////////////////
-- chane de requte
DECLARE @.STR_QRY VARCHAR(8000)
-- valeur devant tre retourne
DECLARE @.maxcle integer
SET @.NEW_VAL = NULL
SET NOCOUNT ON
-- ON DEMARRE LA TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION SET_KEY
-- on regarde si la table des clefs existe
IF NOT EXISTS(SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'SYS_DB_MASTERKEY')
BEGIN
-- si elle n'existe pas il faut la crer
CREATE TABLE SYS_DB_MASTERKEY
(MSK_TABLE_NAME CHAR(128) NOT NULL,
MSK_LAST_VALUE INTEGER NOT NULL DEFAULT 0,
CONSTRAINT MSK_KEY PRIMARY KEY
(MSK_TABLE_NAME))
IF @.@.ERROR <> 0
GOTO LBL_ERROR
-- cration des index
CREATE INDEX MSK_PK ON SYS_DB_MASTERKEY
(MSK_TABLE_NAME)
END
-- on regarde si la table concerne existe
IF NOT EXISTS(SELECT *
FROM SYS_DB_MASTERKEY
WHERE MSK_TABLE_NAME = @.NOM_TABLE)
BEGIN
-- elle n'existe pas, on l'y insre
INSERT INTO SYS_DB_MASTERKEY VALUES (@.NOM_TABLE, 0)
IF @.@.ERROR <> 0
GOTO LBL_ERROR
END
-- calcule de la nouvelle clef
SELECT @.NEW_VAL = MSK_LAST_VALUE +1
FROM SYS_DB_MASTERKEY
WHERE MSK_TABLE_NAME = @.NOM_TABLE
IF @.@.ERROR <> 0
GOTO LBL_ERROR
-- mise jour de la nouvelle clef dans la table des clefs
UPDATE SYS_DB_MASTERKEY
SET MSK_LAST_VALUE = @.NEW_VAL
WHERE MSK_TABLE_NAME = @.NOM_TABLE
IF @.@.ERROR <> 0
GOTO LBL_ERROR
COMMIT TRANSACTION SET_KEY
GOTO LBL_RESUME
LBL_ERROR:
ROLLBACK TRANSACTION SET_KEY
LBL_RESUME:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET NOCOUNT ON
GO
A +
Venkat a crit :
> Hi,
> Can you tell me the best way to get a next unique sequence
> integer number which fits in a table. My application supports multi users
> and at a single point of time users may add a new row to the table. But it
> should fit to generate a unique next available integer value.
>
> For eg: my table contains EMPID int, EMP_Name varchar(50)
> EMPID is a primary key.
> The main constraint here is i am using Ado.net. So i need to
> know the the integer (primary key) value before accpeting the changes to
> database. I need to get the next sequence number as there are some child
> objects for which i need to use this id. Once all the data is set the user
> can select to save the changes which commits the data in the data table wi
th
> the db.
> Can any one suggest me the best way to do it?
>
> Thanks in advance.
>
> --
> Venkat
>
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Uri Dimant a crit :
> Hi
> CREATE PROC Get_nextID
> @.ID char(20),
> @.nextID numeric(18) output
> as
> update NextIDs
> set @.nextID = NextID = NextID + 1
> where ID = @.ID
>
Never do this ! You will fatally have some deadlock / telescoping key
values over the time due to a lack in the isolation principles.
A +

>
> "Venkat" <stammana@.palantirsolutions.com> wrote in message
> news:ucR8AgPSGHA.5908@.TK2MSFTNGP10.phx.gbl...
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SQLpro
This is only one of many options, I have seen that by using a technique from
my previous post and and having an index on the column we don't get any
deadlocks. But I agree that the OP may use this below much better script
DECLARE @.maxid INT
BEGIN TRAN
SELECT @.maxid =COALESCE(MAX(Mycol),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
WHERE.....
INSERTI INTO Table(mycol) VALUES (@.maxid +1)
COMMIT TRAN
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:u4Ncq2PSGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Uri Dimant a crit :
> Never do this ! You will fatally have some deadlock / telescoping key
> values over the time due to a lack in the isolation principles.
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||Uri Dimant a crit :
> SQLpro
> This is only one of many options, I have seen that by using a technique fr
om
> my previous post and and having an index on the column we don't get any
> deadlocks. But I agree that the OP may use this below much better script
>
> DECLARE @.maxid INT
> BEGIN TRAN
> SELECT @.maxid =COALESCE(MAX(Mycol),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
> WHERE.....
> INSERTI INTO Table(mycol) VALUES (@.maxid +1)
> COMMIT TRAN
effectyvly this lock or an isolation level as SERIALIZABLE can garanty that.
Wich in term of cost is pretty ugly since the table grow !!!
A +

> "SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
> news:u4Ncq2PSGHA.1728@.TK2MSFTNGP11.phx.gbl...
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************