Tom Moreau
3/30/2007 5:21:00 PM
What do you mean by "non incrementing identity"? Identities, by definition,
DO auto-increment, so if your identity is not doing that, you have found a
serious bug.
If you are doing things through code and avoiding an identity, then you have
the effect of serializing inserts into your table, which will hurt
performance. One example is:
INSERT MyTable (TblID, Col1, Col2)
select
max(TblID) + 1
, 'ABC'
, 123
from
MyTable
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Dan Rolfe" <drolfe@nelsonwatson.com> wrote in message
news:1175274251.362367.281190@d57g2000hsg.googlegroups.com...
Hello all and I thank you in advance for your responses;
Here is my scenario: I have table with a non incrementing identity
field. I have built a website so multiple people can add information
into this table simultaneously. The way my program looks up the next
record is by scalar:
select max(TblID) + 1 AS NEXTRECORD from table
The problem that occurred is that when two people do it at the exact
same time, they insert the same ID number.
What i have devised is to push the records into a temp table with auto
incrementing ID, and create a SP and trigger to push them onto the
main table.
My question is: Is there any possibility that the triggers will run
over each other the same way that the scalar statement does?
Any input would be sincerely appreciated, TYIA