Select with serial number column

#1

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

#2

ArunDhaJ (arundhaj (AT) gmail (DOT) com) writes:

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#3

On Jul 2, 2:13 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

ArunDhaJ (arund... (AT) gmail (DOT) 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... (AT) sommarskog (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Select with serial number column

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Select with serial number column - 07-02-2007 , 03:37 AM

Re: Select with serial number column - 07-02-2007 , 04:13 AM

Re: Select with serial number column - 07-02-2007 , 04:35 AM