Re: Select with serial number column
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 2 Jul 2007 09:13:42 +0000 (UTC)
ArunDhaJ (arundhaj@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: Select with serial number column
- From: ArunDhaJ
- Re: Select with serial number column
- References:
- Select with serial number column
- From: ArunDhaJ
- Select with serial number column
- Prev by Date: Select with serial number column
- Next by Date: Re: Select with serial number column
- Previous by thread: Select with serial number column
- Next by thread: Re: Select with serial number column
- Index(es):
Relevant Pages
|
