IDENTITY on non-PK

Is there a way to have an "autonumber" on a non-Primary Key column in SQL
Server?
Thanks,
Ericyou can use IDENTITY on any field, regardless of whether it is a PK
<Eric> wrote in message news:OitsJtRQFHA.576@.TK2MSFTNGP15.phx.gbl...
> Is there a way to have an "autonumber" on a non-Primary Key column in SQL
> Server?
> Thanks,
> Eric
>
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Sure, as long as it's the only one in the table.
Thomas
<Eric> wrote in message news:OitsJtRQFHA.576@.TK2MSFTNGP15.phx.gbl...
> Is there a way to have an "autonumber" on a non-Primary Key column in SQL
> Server?
> Thanks,
> Eric
>|||Sorry...I worded that poorly.
Suppose you already have a PK column that is an identity column. It also
has the identity seed and increment specified.
As Thomas said, you can only have one identity column per table. Is there
an easy way, then, to mimic the identity seed and identity increment
properties on a different column besides the PK?
Thanks,
Eric
<Eric> wrote in message news:OitsJtRQFHA.576@.TK2MSFTNGP15.phx.gbl...
> Is there a way to have an "autonumber" on a non-Primary Key column in SQL
> Server?
> Thanks,
> Eric
>|||You can generate an incrementing key with an INSERT statement but that isn't
the same as an IDENTITY.
http://www.google.co.uk/groups?selm...r />
roups.com
Please explain your requirement. Why do you want to generate two artificial
keys?
David Portas
SQL Server MVP
--|||Thanks for the reply. It turns out my colleauge just needed to have a
sequential way to order search results while ignoring the primary key. He
just added a date/time column with a default of GETDATE(), and used "ORDER
BY myDate" in the SELECTs.
Eric
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:x8-dnQ-Q1IkZI8PfRVn-2g@.giganews.com...
> You can generate an incrementing key with an INSERT statement but that
isn't
> the same as an IDENTITY.
>
http://www.google.co.uk/groups?selm...ooglegroups.com

> Please explain your requirement. Why do you want to generate two
artificial
> keys?
> --
> David Portas
> SQL Server MVP
> --
>|||OK. Ordering by date makes much more sense than sorting on an artificial
key.
David Portas
SQL Server MVP
--

Identity In SQL VS Autonumber In Access

Hi

I wonder if anyone can help with the following:

when using an autonumber in access when you use .addnew you automatically
get the field in an autonumber field i.e.

rs.addnew
jno=autonofld
rs.update

jno will have a value. however in SQL you have to update first and then find
the record (I may be wrong) is there a way to get
the field value before doing the update in SQL Server ?

TIA
SteveNormally you use SCOPE_IDENTITY to return the IDENTITY value after the
INSERT. Could you explain why you want the IDENTITY value before
insertion? How do you intend to use the returned value? There are some
strategies you could use, such as generating a value first and then
INSERTing it. If you want to use an IDENTITY column though I'm not sure
what benefit you would gain by knowing the value beforehand.

Serializing INSERTs isn't recommended because that approach doesn't
scale well. It shouldn't be necessary with an IDENTITY column anyway.

--
David Portas
SQL Server MVP
--|||Thanks for the response

I need the Identity value at the time of insertion so I can display a Job
Number to the user as soon as the record is
added. The reason I used the Access example is to illustrate the retrieving
of the value before .update was to show what
I wanted to do (sorry it gave the wrong idea).

I will try the SCOPE_IDENTITY.

Thanks Again
Steve

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110275881.484473.10290@.z14g2000cwz.googlegro ups.com...
> Normally you use SCOPE_IDENTITY to return the IDENTITY value after the
> INSERT. Could you explain why you want the IDENTITY value before
> insertion? How do you intend to use the returned value? There are some
> strategies you could use, such as generating a value first and then
> INSERTing it. If you want to use an IDENTITY column though I'm not sure
> what benefit you would gain by knowing the value beforehand.
> Serializing INSERTs isn't recommended because that approach doesn't
> scale well. It shouldn't be necessary with an IDENTITY column anyway.
> --
> David Portas
> SQL Server MVP
> --|||SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I
forgot to mention which version of
SQL Server I am using) do you have any other suggestions ?

Steve

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110275881.484473.10290@.z14g2000cwz.googlegro ups.com...
> Normally you use SCOPE_IDENTITY to return the IDENTITY value after the
> INSERT. Could you explain why you want the IDENTITY value before
> insertion? How do you intend to use the returned value? There are some
> strategies you could use, such as generating a value first and then
> INSERTing it. If you want to use an IDENTITY column though I'm not sure
> what benefit you would gain by knowing the value beforehand.
> Serializing INSERTs isn't recommended because that approach doesn't
> scale well. It shouldn't be necessary with an IDENTITY column anyway.
> --
> David Portas
> SQL Server MVP
> --|||Steve,

Select Max(IdentityField)+1 from TableName

Madhivanan|||Madhivanan (madhivanan2001@.gmail.com) writes:
> Select Max(IdentityField)+1 from TableName

That's not a good thing, since the value you get may not actually be
that value, if another process comes in between.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Steve (stevej@.ufrmsa1.uniforum.org.za) writes:
> SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I
> forgot to mention which version of
> SQL Server I am using) do you have any other suggestions ?

Use @.@.identity instead. If you have a trigger on the table that inserts
into a secont identity table, @.@.identity will have the value from that
table. This is why scope_identity() is usually recommended, since it
returns the most recently used identity value in the current scope.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, but I have to agree with Erland

Steve
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1110279816.145987.160610@.f14g2000cwb.googlegr oups.com...
> Steve,
> Select Max(IdentityField)+1 from TableName
> Madhivanan|||Thanks

I am going this route, luckily I do not need to use triggers on the table.

Steve

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns961380405DD2Yazorman@.127.0.0.1...
> Steve (stevej@.ufrmsa1.uniforum.org.za) writes:
> > SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I
> > forgot to mention which version of
> > SQL Server I am using) do you have any other suggestions ?
> Use @.@.identity instead. If you have a trigger on the table that inserts
> into a secont identity table, @.@.identity will have the value from that
> table. This is why scope_identity() is usually recommended, since it
> returns the most recently used identity value in the current scope.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp