how to add identity column to a view

[Database Administrator]

hi all,

how to add identity column to a view in sql server 2000.
plz help.

[Database Administrator]

Amit,

Sorry, but you cannot add an identity column to a view. What is your need?
Are you trying to get a row number attached to each row returned? Must it
be in a view or can you use some other mechanism?

You can do the following, but it will get expensive if the result set is
quite large.

SELECT (select count(*) from MyTable where KeyColumn <= a.KeyColumn) AS
RowNumber, a.*
FROM MyTable AS a
ORDER BY RowNumber

RLF

"amit sharma" wrote in message
news:351A9D82-3E62-465C-90AE-1405C2747667@microsoft.com...
> hi all,
>
> how to add identity column to a view in sql server 2000.
> plz help.
>

[Database Administrator]

Hi

Along with Russells reply..

You should probably add the "row number" in the client application as this
would be the most efficient way to generate a sequence number.

John

"amit sharma" wrote:

> hi all,
>
> how to add identity column to a view in sql server 2000.
> plz help.
>

[Database Administrator]

hi Russell,
thanx for your reply.
i have a table with identity column and values are not continous in that
column.
thats why i want to create a view which has continous values in identity
column.
so that i can implement custom paging in my front end application.



"Russell Fields" wrote:

> Amit,
>
> Sorry, but you cannot add an identity column to a view. What is your need?
> Are you trying to get a row number attached to each row returned? Must it
> be in a view or can you use some other mechanism?
>
> You can do the following, but it will get expensive if the result set is
> quite large.
>
> SELECT (select count(*) from MyTable where KeyColumn <= a.KeyColumn) AS
> RowNumber, a.*
> FROM MyTable AS a
> ORDER BY RowNumber
>
> RLF
>
> "amit sharma" wrote in message
> news:351A9D82-3E62-465C-90AE-1405C2747667@microsoft.com...
> > hi all,
> >
> > how to add identity column to a view in sql server 2000.
> > plz help.
> >
>
>
>

[Database Administrator]

Hi

You may want to read
http://databases.aspfaq.com/database...recordset.html

John

"amit sharma" wrote:

> hi Russell,
> thanx for your reply.
> i have a table with identity column and values are not continous in that
> column.
> thats why i want to create a view which has continous values in identity
> column.
> so that i can implement custom paging in my front end application.
>
>
>
> "Russell Fields" wrote:
>
> > Amit,
> >
> > Sorry, but you cannot add an identity column to a view. What is your need?
> > Are you trying to get a row number attached to each row returned? Must it
> > be in a view or can you use some other mechanism?
> >
> > You can do the following, but it will get expensive if the result set is
> > quite large.
> >
> > SELECT (select count(*) from MyTable where KeyColumn <= a.KeyColumn) AS
> > RowNumber, a.*
> > FROM MyTable AS a
> > ORDER BY RowNumber
> >
> > RLF
> >
> > "amit sharma" wrote in message
> > news:351A9D82-3E62-465C-90AE-1405C2747667@microsoft.com...
> > > hi all,
> > >
> > > how to add identity column to a view in sql server 2000.
> > > plz help.
> > >
> >
> >
> >

[Theofilus]

http://support.microsoft.com/default...b;en-us;186133
is a nice solution from MS using rank().

I like the one given by Russel though:

SELECT (select count(*) from Table where KeYCol<=a.KeyCol) AS RowNo, a.*
FROM Table AS a
ORDER BY RowNo

Thank you Russel!!!
A very smart solution.