How to generate sequential row number in tsql?

There is no need to avoid Analytic Functions if your database supports them e.g ROW_NUMBER()

    SELECT        ROW_NUMBER() OVER (ORDER BY [<PRIMARYKEY_COLUMN_NAME>]) AS Number    FROM        [<TABLE_NAME>]

The syntax is Func([ arguments ]) OVER (analytic_clause) you need to focus on OVER (). This last parentheses make partition(s) of your rows and apply the Func() on these partitions one by one. In above code we have only single set/partition of rows. Therefore the generated sequence is for all the rows.

You can make multiple set of your data and generate sequence number for each one in a single go. For example if you need generate sequence number for all the set of rows those have same categoryId. You just need to add Partition By clause like this (PARTITION BY categoryId ORDER BY [<PRIMARYKEY_COLUMN_NAME>]).

Remember that after FROM you can also use another extra ORDER BY to sort your data differently. But it has no effect on the OVER ()

If sort column contains unique values, you can also do it without the new built-in Row_Number() function, by using a subquery based on a sort column.

  Select [other stuff],      (Select count(*) From table        where sortCol < a.sortCol) rowNum  From table a  Order by sortCol

change < to <= to start counting at 1 instead of 0