You say Non-Intelligent Key .. I say Abstract Key.
The ID values should still be in temporal order (smaller ID # is earlier) so
its not truly without meaning.
The benefit of having a single pool of next id numbers to pull from across
all tables, is the ability to prevent bad joins by separating the domains of
each attribute.
If you have an employee and orders table both with an identity column the
following join will work even though its meaningless
select *
from Emp
join Order on Emp.EmpID = Order.OrderID
A single sequence generator that feeding both would mean that there never
would be an Order ID with the same value as an employee ID.
Also an Employee ID can be placed in other columns as an attribute without
causing confusion
select *
from Order
join Emp E1 on Emp.EmpID = Order.OrderExpediterEmpID
join Emp E2 on Emp.EmpID = Order.QualityControlEmpID
In this case a bad join would not bring back any rows (e.g. QC emps are not
listed in the Emp Table).
As for performance, I sort of see your point, but there are ways around that
if you really hit the wall (preallocate ranges for bulk operations, etc.)
We must not sacrifice the quality of the data in exchange for speed (in my
little value system anyway).
"David Portas" <RE****************************@acm.org> wrote in message
news:ja********************@giganews.com...
It isn't too difficult to use IDENTITY or even ROWVERSION as a
table-independent sequence generator. Just use a redundant table to
generate the sequence and then INSERT the keys where you need them.
I would much prefer to use a numbers table for this sort of thing. Making
a surrogate key serializable across multiple tables seems like the worst of
both worlds to me: a non-intelligent key plus a potential bottleneck and
resource hog.
--
David Portas
SQL Server MVP
--
