T-SQL ROW_NUMBER() function

Number you eggs before they hatch! :)) Bad one!!

I search a lot about giving row number to each resultant row of select statement but it seemed such automatic row numbering function is not available in T-SQL (SQL which runs with MS SQL server). But my colleague, Subamanin, gave a wonderful solution which I want share with you. Code goes like this notice the ROW_NUMBER() function.

CODE:

declare @EMP table
(
emp_name VARCHAR(10),
emp_post varchar(10)
)

insert into @EMP values('A11','00A')
insert into @EMP values('A12','00B')
insert into @EMP values('A13','00C')
insert into @EMP values('A14','00A')
insert into @EMP values('A14','00A')

select *
from @EMP --Produces Table(0)

select *,
ROW_NUMBER() over(order by emp_name) as ID_NUM
from @EMP --Produces Table(1)

And this is RESULT:

TABLE(0)

emp_name	emp_post
A11	00A
A12	00B
A13	00C
A14	00A
A14	00A

TABLE(1)

emp_name	emp_post	ID_NUM
A11	00A	1
A12	00B	2
A13	00C	3
A14	00A	4
A14	00A	5

But unfortunately over(order by emp_name) function has to be there. Here in 'over' function you can order by column of your choice which may or may not be your primary or unique key.