ROWNUM [ PL/SQL ] Analog for T-SQL

There is no ROWNUM Analog in T-SQL. ROWNUM is used in PL/SQL to to get the number of rows the query returns in the same query. There is no easy way to perform the same trick in T-SQL query. Small snippet below demonstrates the easy way to achieve the same goal using inner view.

SELECT (SELECT SUM(1) 
          FROM employee t1 
         WHERE t1.emp_id <= t2.emp_id) AS rownum, t2.* 
  FROM employee t2

Use in small tables only, since it is not performant on large databases.

Related Posts:

• MSSQL Tip: Get Connected Columns in Related Tables
• How To Shrink Transaction Log in MSSQL 2000
• T-SQL: Output Parameter in sp_executesql
• MSSQL: Change tables owner to dbo with sp_changeobjectowner
• MSSQL: Recompile Stored Procedures, Views, Functions
• MSSQL Tip: Rebuild Clustered Indexes
• T-SQL: Aggregate Values Multiplication Like SUM Aggregates Values Sum
• SQL Tip: HowTo Convert Delimited String to Table