Different Methods to generate Row Numbers without using Row_Number function

Well as far as I know there are two methods-

Method 1 | Using Temp Table

--

-----------Method 1----------------
--In this method we are generating row numbers while inserting data in a temp table

SELECT 
	  IDENTITY(INT,1,1) rowNum
	, dt 
INTO #temp FROM testRowNumber


SELECT * FROM #temp


DROP TABLE #temp

--

Output of the above query is –

Method 2 | Using SQL Statements

Adding a row number for each row is not easy using SQL. Below method only works if there is at least one unique column or the combination of column is unique in the table.

--

-----------Method 2----------------
--Here we are generating row number with Plain SQL

SELECT 
	  (SELECT COUNT(*)  FROM testRowNumber i WHERE o.dt >= i.dt) rowNum 
	, dt
FROM testRowNumber o
--

Output of the above query is –

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com