Q: SQL Server ROW_NUMBER Left Join + when you don't know column names I'm writing a page that will create a query (for non-db users) and it create the query and run it returning the results for them. 
I am using row_number to handle custom pagination.
How do I do a left join and a row_number in a subquery when I don't know the specific columns I need to return. I tried to use * but I get an error that 

The column '' was specified multiple times

Here is the query I tried:
SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (ORDER BY Test) AS ROW_NUMBER, *
   FROM table1 a
   LEFT JOIN table2 b 
   ON a.ID = b.ID) x
WHERE ROW_NUMBER BETWEEN 1 AND 50 


A: Your query is going to fail in SQL Server regardless of the row_number() call.  The * returns all columns, including a.id and b.id.  These both have the same name.  This is fine for a query, but for a subquery, all columns need distinct names.
You can use row_number() for an arbitrary ordering by using a "subquery with constant" in the order by clause:
SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (ORDER BY (select NULL)) AS ROW_NUMBER, *
   FROM table1 a
   LEFT JOIN table2 b 
   ON a.ID = b.ID) x
WHERE ROW_NUMBER BETWEEN 1 AND 50 ;

This removes the dependency on the underlying column name (assuming none are named ROW_NUMBER).

A: Try this sql. It should work.
SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (ORDER BY a.Test) AS ROW_NUMBER, a.*,b.*
   FROM table1 a
   LEFT JOIN table2 b 
   ON a.ID = b.ID) x
WHERE ROW_NUMBER BETWEEN 1 AND 50 

