You are here: Re: Select with serial number column « MsSQL Server « IT news, forums, messages
Re: Select with serial number column

Posted by ArunDhaJ on 07/02/07 09:35

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация