|
|
Posted by Erland Sommarskog on 07/02/07 09:13
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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|