|
|
Posted by --CELKO-- on 10/13/05 20:36
Your whole mental model of RDBMS is wrong.
Exposed locators such as IDENTITY are not surrogate keys. A surrogate
is totally hidden from uers and maintained by the system -- think of
how an index works. If you change a natural key, the DRI actions will
cascade it for you. Hell does not break loose. But if your autonumber
and real key are out of synch,then you are screwed. Just enter the
same record several times to get different autonumbers on duplicate
rows. Drop all but one of the dups and then try to find all of the
referenced rows in other tables.
If you maintain the exposed locator by hand, you have extra work, extra
disk seeks and will eventually make a mistake, thus destroying your
data integrity. How would you validate and verify your data?
The UPC code just went from 10 to 13 digits. It does not matter if you
used an autonumber or the UPC, the UPC has to be updated. If you had
used a proper RDBMS design and SQL-92, you would do an ALTER DOMAIN and
a single update with CASCADE. This is one of the MANY reasons that
rows are not records. This is a problem in SQL Server because it is
still based on a contigous storage model, but other products are not,
so wind up using tools to write scripts.
Finally have you bothered to time the two approaches with small and
large tables? It does not make a difference until the table has to do
a lot of paging and redundant autonumbers accumulate. This is the
21-st century; we have 32 and 64 bit hardware, nano-second speeds,
multi-word instructions, parallel processing and really good disk
drives.
Navigation:
[Reply to this message]
|