Adding auto increment extra column to view which is not present in table in SQL Server

Question

I want to create an extra column while fetching data, and that column should increment values like

id marks myextcolumn
--------------------
1   89        1
2   99        2
4   67        3
6   77        4
.
.
.

Answer 1

You need to use row_number function

Schema:

CREATE TABLE #TAB (ID INT, MARKS INT)

INSERT INTO  #TAB
SELECT 1 ,  89   
UNION ALL
SELECT 2 ,  99  
UNION ALL
SELECT 4 ,  67 
UNION ALL
SELECT 6  , 77  

Do select the above table with Rownumber for Extra column

SELECT 
    ID, MARKS, 
    ROW_NUMBER() OVER(ORDER BY (SELECT 1)) EXTRA_COL 
FROM #TAB

The result will be

+----+-------+-----------+
| ID | MARKS | EXTRA_COL |
+----+-------+-----------+
|  1 |    89 |         1 |
|  2 |    99 |         2 |
|  4 |    67 |         3 |
|  6 |    77 |         4 |
+----+-------+-----------+