generating sequence number

Question

Hi

Based on following TableA

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

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

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

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

Thanks.

Answer 1

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

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

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

Answer 2

Use a computed column:

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