Having ported both I wouldn't dismiss identity that easily.
An identity column is conceptually nothing else than a sequence tied to a table
(i..e a special case).
I'm fairly sure that MS SQL Server does cache IDENTITY, no advantage there.
So you've got one runtime operator in SQL Server's p-code for the insert
statement vs. pathlength to invoke a trigger plus the operator to serve a
sequence value.
Naturally I don't know much about Oracle triggers, but pushing a context for
error handling, switching into the module,.. adds all up, even if everything is
cached.
Then all this happens either for every row or - if standard compliant - a
transition table has to be built: collect all rows for the insert, pass them to
the trigger, execute it for every row and pass the transition table to the
insert.
Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada
Received on Sun Apr 28 2002 - 19:58:51 CDT
