Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
beanman
Starting Member
1 Post |
 Posted - 2007-09-13 : 22:55:50
|
| How do you return the unique auto/identity ID of each row in an sql server 2000 query??eg:select ID, name, phone from customer |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-09-13 : 23:24:01
|
create the column ID as identity in the table
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-13 : 23:24:35
|
It depends on what you assigned (or whoever created the table) named the identity column. If you know the name it's self explanatory from there. You can use sp_help [tablename] to find out which is the identity column.
Future guru in the making. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-14 : 02:03:11
|
| If you dont have identity column and want to show serial no when querying, you need to copy data to temp table having identity column. Or if you want to show data in front end application, you can easily do numbering thereMadhivananFailing to plan is Planning to fail |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-14 : 03:29:10
|
| just a simple query will do... no special operators unless you want to retrieve during insertselect @@identity--------------------keeping it simple... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-14 : 04:09:29
|
I prefer SCOPE_IDENTITY() since @@identity fetches the last id inserted across ALL CONNECTIONS and ALL SCOPES.Which means that if an after TRIGGER inserts a record in another table, @@identity returns that id, not the id of the insert you would expect.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-14 : 04:53:47
|
I think OP wants to generate serial no for the resultset resturned  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Returning the Id of each row??