Do you want an incrementing integer column returned with your recordset? If so: -
--Check for existance
if exists (select * from dbo.sysobjects where [id] = object_id(N'dbo.t') AND objectproperty(id, N'IsUserTable') = 1)
drop table dbo.t
go
--create dummy table and insert data
create table dbo.t(x char(1) not null primary key, y char(1) not null)
go
set nocount on
insert dbo.t (x,y) values ('A','B')
insert dbo.t (x,y) values ('C','D')
insert dbo.t (x,y) values ('E','F')
--create temp table to add an identity column
create table dbo.#TempWithIdentity(i int not null identity(1,1) primary key,x char(1) not null unique,y char(1) not null)
--populate the temporary table
insert into dbo.#TempWithIdentity(x,y) select x,y from dbo.t
--return the data
select i,x,y from dbo.#TempWithIdentity
--clean up
drop table dbo.#TempWithIdentity