Get identity column value before phisically insert a row in SQL Server 2000

Get identity column value before phisically insert a row in SQL Server 2000

Post by Bil » Wed, 22 Jan 2003 05:21:04




value after a row is inserted.  But I need to know the auto-generated
value before phisically insert the row into the table.  I know Oracle
can do that.  Is there a way to do it in SQL Server 2000?  I am using
ADO.NET with SQL Server.
 
 
 

Get identity column value before phisically insert a row in SQL Server 2000

Post by Michael MacGrego » Wed, 22 Jan 2003 05:50:35


Why do you need to do that?

Michael MacGregor
Database Architect
SalesDriver

 
 
 

Get identity column value before phisically insert a row in SQL Server 2000

Post by Aaron Bertrand [MVP » Wed, 22 Jan 2003 05:54:32


Sure, but it's not reliable.  What is the purpose?

You could do:

SELECT nextIdentity = MAX(identityColumn)+1 FROM table

However this identity could be consumed by another user before you do the
insert... unless you put the insert into the same transaction and make it
block all other inserts (in which case, your ADO.NET page isn't going to be
able to tell the difference).

Ask yourself: are you going to insert a record, or not?  If so, then insert
it, and you have your identity value.  If only maybe, then how could its
identity value be relevant until after you've inserted it.

Are you sure you're using IDENTITY as a key for the right reason(s)?

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.



> value after a row is inserted.  But I need to know the auto-generated
> value before phisically insert the row into the table.  I know Oracle
> can do that.  Is there a way to do it in SQL Server 2000?  I am using
> ADO.NET with SQL Server.

 
 
 

Get identity column value before phisically insert a row in SQL Server 2000

Post by Nikhil Pate » Wed, 22 Jan 2003 07:26:28


Use DBCC CHECKIDENT



> value after a row is inserted.  But I need to know the auto-generated
> value before phisically insert the row into the table.  I know Oracle
> can do that.  Is there a way to do it in SQL Server 2000?  I am using
> ADO.NET with SQL Server.

 
 
 

Get identity column value before phisically insert a row in SQL Server 2000

Post by Tibor Karasz » Wed, 22 Jan 2003 18:31:34


But that is not multi-user safe. Someone else might get "in-between".

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> Use DBCC CHECKIDENT




> > value after a row is inserted.  But I need to know the auto-generated
> > value before phisically insert the row into the table.  I know Oracle
> > can do that.  Is there a way to do it in SQL Server 2000?  I am using
> > ADO.NET with SQL Server.

 
 
 

Get identity column value before phisically insert a row in SQL Server 2000

Post by Nikhil Pate » Wed, 22 Jan 2003 22:52:03


Quote:> But that is not multi-user safe. Someone else might get "in-between".

I agree. If you want to consider this issue then best place to put your code
would be the trigger. Do you agree?



Quote:> But that is not multi-user safe. Someone else might get "in-between".

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...




> > Use DBCC CHECKIDENT




> > > value after a row is inserted.  But I need to know the auto-generated
> > > value before phisically insert the row into the table.  I know Oracle
> > > can do that.  Is there a way to do it in SQL Server 2000?  I am using
> > > ADO.NET with SQL Server.

 
 
 

Get identity column value before phisically insert a row in SQL Server 2000

Post by Tibor Karasz » Wed, 22 Jan 2003 23:40:46


A trigger can be workable, but if this is the PK then you have to set aside some special value
for the insert to use and the trigger then changes the value to "the next". This will not return
the "counter" before the insert, however.

Another option is to have another table which drives the "next" value and serialize access
through the table...

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> > But that is not multi-user safe. Someone else might get "in-between".

> I agree. If you want to consider this issue then best place to put your code
> would be the trigger. Do you agree?



> > But that is not multi-user safe. Someone else might get "in-between".

> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...



> > > Use DBCC CHECKIDENT




> > > > value after a row is inserted.  But I need to know the auto-generated
> > > > value before phisically insert the row into the table.  I know Oracle
> > > > can do that.  Is there a way to do it in SQL Server 2000?  I am using
> > > > ADO.NET with SQL Server.

 
 
 

Get identity column value before phisically insert a row in SQL Server 2000

Post by Bil » Sat, 25 Jan 2003 04:25:31


When you are porting Oracle database to SQL Server, this becomes a
problem.  In Oracle it is very easy to get the next identity before
the row is inserted.

> Why do you need to do that?

> Michael MacGregor
> Database Architect
> SalesDriver

 
 
 

Get identity column value before phisically insert a row in SQL Server 2000

Post by Bil » Sat, 25 Jan 2003 04:44:04


Thank you for the reply.

"SELECT nextIdentity = MAX(identityColumn)+1 FROM table" is not going
to work because if you roll back before a row is inserted with
ADO.NET, there will be a gap in identity column.  Identity only
gaurantees that the value in this column is unique, but not necessary
in sequence.

Getting the next identity value before inserting a row is very easy to
do in Oracle (and most likely UDB as well).  When porting from Oracle
to SQL Server, it becomes a problem.

Bill


> Sure, but it's not reliable.  What is the purpose?

> You could do:

> SELECT nextIdentity = MAX(identityColumn)+1 FROM table

> However this identity could be consumed by another user before you do the
> insert... unless you put the insert into the same transaction and make it
> block all other inserts (in which case, your ADO.NET page isn't going to be
> able to tell the difference).

> Ask yourself: are you going to insert a record, or not?  If so, then insert
> it, and you have your identity value.  If only maybe, then how could its
> identity value be relevant until after you've inserted it.

> Are you sure you're using IDENTITY as a key for the right reason(s)?

> --
> Aaron Bertrand, SQL Server MVP
> http://www.aspfaq.com/

> Please reply in the newsgroups, but if you absolutely
> must reply via e-mail, please take out the TRASH.




> > value after a row is inserted.  But I need to know the auto-generated
> > value before phisically insert the row into the table.  I know Oracle
> > can do that.  Is there a way to do it in SQL Server 2000?  I am using
> > ADO.NET with SQL Server.

 
 
 

Get identity column value before phisically insert a row in SQL Server 2000

Post by Aaron Bertrand [MVP » Sat, 25 Jan 2003 05:05:35


Who cares if there are gaps?  It's an artifical key and meaningless.

There are workarounds anyway...

If you enter a state where you're going to issue a rollback, then you can --
at that point -- DBCC CHECKIDENT and reseed the table at the current max
value.  Viola, no gaps.

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


Quote:> Thank you for the reply.

> "SELECT nextIdentity = MAX(identityColumn)+1 FROM table" is not going
> to work because if you roll back before a row is inserted with
> ADO.NET, there will be a gap in identity column.  Identity only
> gaurantees that the value in this column is unique, but not necessary
> in sequence.

> Getting the next identity value before inserting a row is very easy to
> do in Oracle (and most likely UDB as well).  When porting from Oracle
> to SQL Server, it becomes a problem.

> Bill

 
 
 

Get identity column value before phisically insert a row in SQL Server 2000

Post by Michael MacGrego » Sat, 25 Jan 2003 22:48:30


That doesn't say why you need it only that you use that function in Oracle:
So why do you need to do that?

MTM

 
 
 

Get identity column value before phisically insert a row in SQL Server 2000

Post by Michael MacGrego » Sat, 25 Jan 2003 22:49:12


Quote:> Viola, no gaps.

Viola? Do you play?

MTM