Select with serial number column

Hi Friends,
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

Regards,
ArunDhaJ

Re: Select with serial number column

ArunDhaJ (arundhaj@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Select with serial number column

On Jul 2, 2:13 pm, Erland Sommarskog 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/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx- Hide quoted text -
>
> - Show quoted text -

Thanks Erland