Generating sequence number

Question

Based on the following table A

Data
--------
Dummy1
Dummy2
Dummy3
.
.
DummyN

there is a way to generate sequence number when selecting rows from a table.

something like select sequence() as ID,* from Data

that which will give

ID  Data    
---------
1  Dummy1
2  Dummy2
3  Dummy3
....
N  DummyN

Thanks.

+2

stackoverflowuser Apr 28 10 at 19:02

2 answers

Use computed column:

CREATE Table MyTAble
(
   ID int identity(1,1), 
   Data varchar(20) AS 'Dummy' + ID
)

0

Joel coehoorn Apr 28 10 at 19:06

marc_s · Accepted Answer · 2010-04-28T19:03:35+0000

Do you want to have a column in your table that is a sequence? Use INT IDENTITY

.

Do you want to add a sequence number to a SELECT statement or view? Use the method ROW_NUMBER() OVER(ORDER BY .....)

.

SELECT 
  ROW_NUMBER() OVER (ORDER BY Data) AS 'ID',
  Data
FROM 
  dbo.YourTable