On Jul 2, 2:13 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> ArunDhaJ (arund...@gmail.com) writes:
> > Help me in retrieving a table with a SNo column
>
> > Select Name From SysObjects Where type = 'U' Order By Name asc
>
> > the above query would fetch the Table names. as
>
> > Name
> > Table1
> > Table2
> > Table3
> > Table4
>
> > i need a view which should also include the serial number
>
> > SNo Name
> > 1 Table1
> > 2 Table2
> > 3 Table3
> > 4 Table4
>
> On SQL 2005:
>
> SELECT name, row_number() OVER(ORDER BY Name)
> FROM sysobjects
> ORDER BY name
>
> On SQL 2000:
>
> SELECT name, (SELECT COUNT(*) FROM sysobjects b
> WHERE a.name <= b.name)
> FROM sysobjects
> ORDER BY name
>
> On for larger data sets, the performance of the SQL 2000 solution is
> so poor that it is about unusual. The alternative is to bounce data over
> a temp table with an IDENTITY column.
>
> The SQL 2005 solution has excellent performance.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

Thanks Erland