how can create view autonumber

now i have two table

1st table
type_id type_name
4 a
5 b
6 c

2nd table
category_id category_name type_id
6 aaa 4
7 bbb 5
9 ccc 6

i want to join two table to create view
autonumber type_name category_name
1 c ccc
2 b bbb
3 a aaa

how can i write this sql to create autonumber field

thanksI am not sure you can do that with a view, but you can create a table that reference the primary keys of the other two tables and set the primary key for this table to be an Identity - which will get you the autonumber feature.

That is:
Table3:
id int PK IDENTITY
table1_id int FK
table2_id int FK

You set a primary key as IDENTITY in the Enterprise Manager in the design view for the table.