|
Posted by David Portas on 10/19/05 23:14
IDENTITY is the proper name for an auto-incrementing surrogate key
column in SQL Server.
In principle you should avoid using IDENTITY in any way that is
meaningful outside the database. In fact, don't show the IDENTITY
column to your users AT ALL in your applications. The main reason why
we use IDENTITY columns is to provide an immutable surrogate key. We
know from experience that once we expose data to users it may become
significant in some way, at which point its notional immutability may
be at risk because it is no longer just an attribute of the system - it
belongs to the outside world as well. Then we may have a problem
because an IDENTITY column is not updateable and because sometimes (for
example when you integrate data or perform multiple row INSERTs) you
can't always dictate its value directly on INSERT either.
So for those reasons I'd say that you shouldn't concern yourself about
the order in which the IDENTITY values are assigned. The order doesn't
matter much if only the developers and admins can see it.
If you want the cosmetic reassurance of seeing the values in the
"right" order you can try creating a new table and using INSERT SELECT
with an ORDER BY clause to populate it. Be aware that that method is
definitely NOT always guaranteed to generate the values in the correct
order - so don't count on it. You may also find that the effort of
creating and populating a new table negates any slight advantage of
"fixing" the IDENTITY sequence.
--
David Portas
SQL Server MVP
--
Navigation:
[Reply to this message]
|