how to create a id=rownumber - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to create a id=rownumber

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-11-07 : 14:48:53

I have table t1 which does not have a primary key. I want to create an id column which equals row number, how can I do that?
thanks.

Jeff

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 14:56:27

is a unique field you can use? can you add an identity field to the table or put the table if it is small into a table variable with an identity field?

jeff06
Posting Yak Master

166 Posts

Posted - 2007-11-07 : 15:06:53

There no unique field in the table.
Thanks

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-07 : 15:17:28

use an identity column

select identity(int,1,1) as UniqueID,* into NewTable from OldTable

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 15:20:17

if there is no unique field you will need to generate one, either in the base table or if you need to only temporarily have a row identifier you could use a temp table/table variable. the best choice is to create and identity field, cluster it and make it the primary key.

ALTER TABLE dbo.Table_1 ADD row_identifier int identity(1,1) NOT NULL
ALTER TABLE dbo.Table_1 ADD CONSTRAINT PK_Table_1 PRIMARY KEY CLUSTERED (row_identifier)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-07 : 15:22:35

quote:
Originally posted by evilDBA

use an identity column

select identity(int,1,1) as UniqueID,* into NewTable from OldTable

I agree that an identity should be used here, but the syntax used here to create it should not be used as you will be losing all constraints and indexes unless you move this over to. The easiest way to see the best approach in adding an identity column is to make the change in SSMS, but do not save the change to the table. Instead, look at the change script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-11-07 : 15:30:48

What if you delete a row?? Row number changes, identity won't, right?

---------------------------------------------------------
SSRS Kills Kittens.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-07 : 15:35:47

Why does that matter? Let's say you have 1,2,3,4,5 for identity values then you delete row 3. So now you have 1,2,4,5. The next row that you insert will be 6. Does it matter that you have gaps? Almost certainly NO!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-11-07 : 15:39:46

I just thought he was asking for id=rownumber, instead of a simple identity. So, if 500 rows, the last has id=500. To me that seems very hard and illogical criteria, but I could have understood question wrong.

---------------------------------------------------------
SSRS Kills Kittens.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-07 : 15:50:33

tkizer, I know, I've just oversimplified that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources