Best would be to do it client side. The numbering implies some kind of
order but there is no ORDER BY clause in your example query. Here's an
example:

SELECT
(SELECT COUNT(*)
FROM pubs.dbo.authors
WHERE au_id <= A.au_id) AS row_num,
*
FROM pubs.dbo.authors AS A
ORDER BY au_id ;

--
David Portas
SQL Server MVP
--