Identity/Seed Values

I have a question regarding using the Identity/Seed values. I have migrated
a number of tables form Access to SQL. In Access the identity/seed values
were called autonumbers.
The big problem with autonumbers in Access was that they could change at any
time on you, getting renumbered or sometimes even becoming negative values,
etc.
Now, in my application I have need for a unique number as the primary key AS
LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
formatted a special way, I do not need it to be sequential, etc. But I do
need it to stay the same after it has been entered.
So, my question is this: I know in Access i could not rely on the autonumber
to stay the same. Does the identity/seed value propoerties of a SQL table
have this same issue? Or can I plan on this value staying the same over
time?
Thanks
JoeIdentity values in SQL Server start at the seed value. By default it
increments by 1 and cannot be manually inserted or updated.
"Joe Williams" <joe@.anywhere.com> wrote in message
news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> I have a question regarding using the Identity/Seed values. I have
migrated
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at
any
> time on you, getting renumbered or sometimes even becoming negative
values,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the
autonumber
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>|||Hi
Once the Identity value is stored in a table, it reamains the same and does
not change.
Regards
Mike
"Joe Williams" wrote:

> I have a question regarding using the Identity/Seed values. I have migrate
d
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at a
ny
> time on you, getting renumbered or sometimes even becoming negative values
,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the autonumb
er
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>
>|||When you say that the autonumbers change in Access, do you mean that
previously inserted values change (with no intervention on the user or
programmer) or just that the values inserted do not always increment
sequentially?
"Joe Williams" <joe@.anywhere.com> wrote in message
news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> I have a question regarding using the Identity/Seed values. I have
migrated
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at
any
> time on you, getting renumbered or sometimes even becoming negative
values,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the
autonumber
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>|||> 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 reaso
n
why you shouldn't tie external meaning to an arbitrary IDENTITY value).
David Portas
SQL Server MVP
--|||you need see "SET IDENTITY_INSERT" en SQL libray
"JohnnyAppleseed" wrote:

> When you say that the autonumbers change in Access, do you mean that
> previously inserted values change (with no intervention on the user or
> programmer) or just that the values inserted do not always increment
> sequentially?
> "Joe Williams" <joe@.anywhere.com> wrote in message
> news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> migrated
> any
> values,
> AS
> autonumber
>
>