How to get a fixed number of rows in sql

Question

If I have 4 rows in database like these.

 |____A____|____B_____|
 |   a1    |    b1    |
 |   a2    |    b2    |
 |   a3    |    b3    |
 |   a4    |    b4    |

But I need to display 10 rows by added NO column to get serial number for each row like these

 __NO__|____A____|____B_____|
   1   |   a1    |    b1    |
   2   |   a2    |    b2    |
   3   |   a3    |    b3    |
   4   |   a4    |    b4    |
   5   |         |          |
   6   |         |          |
   7   |         |          |
   8   |         |          |
   9   |         |          |
  10   |         |          |

How to query by sql server?

Answer 1

Fiddle here : http://sqlfiddle.com/#!3/9a9dd/1

WITH CTE1
AS
(
    SELECT 1 AS [NO]
    UNION ALL
    SELECT [NO]+1 FROM CTE1 WHERE [NO]<10
),
CTE2 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN, A,B 
FROM  YOURTABLE
)

SELECT C1.[NO],A,B
FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.[NO] = C2.RN