Add a row number to result set of a SQL query

The typical pattern would be as follows, but you need to actually define how the ordering should be applied (since a table is, by definition, an unordered bag of rows). One way to do that if you don't care about a specific order otherwise is to use the leading key(s) of a covering index, the leading key(s) of the clustered index, or the columns in any group by / order by clauses. In this case I'll assume A is the single-column clustering key for t:

SELECT t.A, t.B, t.C, number = ROW_NUMBER() OVER (ORDER BY t.A)
  FROM dbo.tableZ AS t
  ORDER BY t.A;

If you truly don't care about order, you can generate arbitrary/nondeterministic row numbering using:

ROW_NUMBER() OVER (ORDER BY @@SPID)

-- or for serial plans

ROW_NUMBER() OVER (ORDER BY @@TRANCOUNT)

Little tricks I picked up from Paul White in this article (see "Paul's Solution").

Not sure what the variables in your question are supposed to represent (they don't match).

SELECT
    t.A,
    t.B,
    t.C,
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS number
FROM tableZ AS t

See working example at SQLFiddle

Of course, you may want to define the row-numbering order – if so, just swap OVER (ORDER BY (SELECT 1)) for, e.g., OVER (ORDER BY t.C), like in a normal ORDER BY clause.