RE: Identity/Seed Values
From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 02/21/05
- Next message: Agnes: "SP limitation ?"
- Previous message: JohnnyAppleseed: "Re: Identity/Seed Values"
- In reply to: Joe Williams: "Identity/Seed Values"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 21 Feb 2005 07:31:04 -0800
> I have need for a unique number...
What you absolutely do need is a proper natural key in your data. IDENTITY
is not a substitute for this. Logically you should always be able to remove
IDENTITY and its referencing columns and replace it with another key in your
table or with another artificial key without changing its meaning. Don't
expose the IDENTITY key to users otherwise you build business processes on
something over which you don't have complete control in all cases - for
example, if you need to integrate data from multiple tables with IDENTITY
keys or in some replication scenarios.
Don't make assumptions about the sequence of IDENTITY values, its continuity
(there may be gaps) or even its uniqueness (except when it's defined with a
PK or unique constraint).
Having said all that, SQL Server doesn't change the IDENTITY value
automatically for any reason once it's assigned and you cannot change an
IDENTITY yourself except by deleting and then inserting a row (another reason
why you shouldn't tie external meaning to an arbitrary IDENTITY value).
-- David Portas SQL Server MVP --
- Next message: Agnes: "SP limitation ?"
- Previous message: JohnnyAppleseed: "Re: Identity/Seed Values"
- In reply to: Joe Williams: "Identity/Seed Values"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
