This is very easy in SQLServer2005/2008 as there is new function Row_number() available for the same.
--Query for SQL2005/2008
Select Row_Number() over (order by emp_id) AS [Sr. No.], * from myTable
But there is some tricky way while using SQl2000
, I prefer to use following query :
--Query for SQL2000 Note:
SELECT (SELECT COUNT(*) FROM myTable tbl WHERE tbl.id <= mTbl.id) AS rownumber,*
FROM myTable mTbl ORDER BY id
You can change myTable with your table and field id with your table's field.