Re: Sort problem in MS SQL Server 7.0


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Re: Sort problem in MS SQL Server 7.0

Hybrid View

  1. #1
    D. Patrick Hoerter Guest

    Re: Sort problem in MS SQL Server 7.0

    Jason,

    >SQL returns
    >the rows in the order they were last modified


    Also not true. The server retrieves the data in the most expedient manner,
    period. This has nothing to do with when it was last modified, but rather,
    which parts are currently in the cache, and which pages is the data stored
    on, and in what order does the server traverse the pages during retrieval.

    Regards,
    D. Patrick Hoerter

    Jason Rein wrote in message <3823203d@NEWS.DEVX.COM>...
    >
    >The answer already given is a correct one. The reason you are getting

    different
    >sorting in SQL 6.5 and 7.0 without the ORDER BY clause is that SQL returns
    >the rows in the order they were last modified. When you converted from 6.5
    >to 7.0, the entire database had to be exported from 6.5 and imported into
    >the new 7.0 format. This could easily have affected the order in which the
    >rows were last updated. Just an FYI...
    >
    >- Jason Rein
    >
    >"Jayesh Pandya" <pandya@hotmail.com> wrote:
    >>
    >>I have MS SQL Server 6.5 (SP 5) on MS Window NT 4.0 (SP 3). Recenly I

    migrated
    >>my database to MS SQL Server 7.0 (SP 1) on MS Windows NT (SP 3).
    >>
    >>SQL Server 6.5 is installed with default option (sort order, code page

    etc).
    >>SQL Server 7.0 is also installed with default option (sort order, code

    page,
    >>unicode etc)
    >>
    >>I am getting some result set in different order in 7.0 compare to 6.5

    though
    >>the stored procedure is same.
    >>
    >>I am also getting result set in different order with UNION clause in 7.0.
    >>
    >>e.g. without order by clause in select statement.
    >>6.5 returns
    >>state ZIP qty
    >>TX 77050 10
    >>TX 77090 15
    >>
    >>7.0 returns
    >>state ZIP qty
    >>TX 77090 15
    >>TX 77050 10
    >>
    >>some cases if order by state is used then also result set are in different
    >>order. if sort on state, zip in 7.0 then result set are matches.
    >>
    >>what will be reason of this and how can I solve this ?
    >>
    >>Last choice is to add order by clause to every stored procedure which is
    >>returning result set.
    >>
    >>Thanks
    >>
    >>Jayesh
    >>
    >>sp_helpsort in 6.5
    >>Sort Order Description
    >>------------------------------------------------------------------
    >>Character Set = 1, iso_1
    >> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
    >>Sort Order = 52, nocase
    >> Case-insensitive dictionary sort order for use with several We
    >> stern-European languages including English, French, and German
    >> . Uses the ISO 8859-1 character set.
    >>Characters, in Order
    >>------------------------------------------------------------------
    >> ! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { | }
    >> ~ *
    >> 0 1 2 3 4 5 6 7 8 9 A=a = = = = = = = B=b C
    >> =c = D=d E=e = = = = F=f G=g H=h I=i = = = = J
    >> =j K=k L=l M=m N=n = O=o = = = = = = P=p Q=q R=r S
    >> =s T=t U=u = = = = V=v W=w X=x Y=y = Z=z = =
    >>
    >>----------------------------------
    >>
    >>sp_helpsort in 7.0
    >>
    >>Unicode data sorting
    >>----------------------
    >>Locale ID = 1033
    >> case insensitive, kana type insensitive, width insensitive
    >>
    >>Sort Order Description
    >>--------------------------------------------------------------------------

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    -----------------------------
    >>
    >>Character Set = 1, iso_1

    >
    >>

    >
    >>

    >
    >>
    >> ISO 8859-1 (Latin-1) - Western European 8-bit character set.

    >
    >>

    >
    >>

    >
    >>
    >>Sort Order = 52, nocase_iso

    >
    >>

    >
    >>

    >
    >>
    >> Case-insensitive dictionary sort order for use with several We

    >
    >>

    >
    >>

    >
    >>
    >> stern-European languages including English, French, and German

    >
    >>

    >
    >>

    >
    >>
    >> . Uses the ISO 8859-1 character set.

    >
    >>

    >
    >>

    >
    >>
    >>Characters, in Order

    >
    >>

    >
    >>

    >
    >>
    >>--------------------------------------------------------------------------

    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------
    -----------------------------
    >>
    >> ! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { | }

    >
    >>

    >
    >>

    >
    >>
    >> ~ *

    >
    >>

    >
    >>

    >
    >>
    >> 0 1 2 3 4 5 6 7 8 9 A=a = = = = = = = B=b C

    >
    >>

    >
    >>

    >
    >>
    >> =c = D=d E=e = = = = F=f G=g H=h I=i = = = = J

    >
    >>

    >
    >>

    >
    >>
    >> =j K=k L=l M=m N=n = O=o = = = = = = P=p Q=q R=r S

    >
    >>

    >
    >>

    >
    >>
    >> =s T=t U=u = = = = V=v W=w X=x Y=y = Z=z = =

    >
    >>

    >
    >>

    >
    >>
    >>

    >



  2. #2
    Damian Guest

    Re: Sort problem in MS SQL Server 7.0


    Unless you specify an ORDER BY clause, SQL Server returns the rows in the
    order it found them on the database, and that order depends on the index
    or primary key it uses to find the data. The index used depends on the where
    conditions, the joins, etc.

    You can try creating an index on state and zip, and if you are lucky enough
    SQL Server will use it to return the data, but my advice is If you really
    need that the order is state and zip, put the ORDER BY clause and stop to
    worry, if you don't do that a lot of things can happen that change the order
    of the rows (creation of a new index, changing on the where clause, change
    of the SQL Server version, etc).



    "D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
    >Jason,
    >
    >>SQL returns
    >>the rows in the order they were last modified

    >
    >Also not true. The server retrieves the data in the most expedient manner,
    >period. This has nothing to do with when it was last modified, but rather,
    >which parts are currently in the cache, and which pages is the data stored
    >on, and in what order does the server traverse the pages during retrieval.
    >
    >Regards,
    >D. Patrick Hoerter
    >
    >Jason Rein wrote in message <3823203d@NEWS.DEVX.COM>...
    >>
    >>The answer already given is a correct one. The reason you are getting

    >different
    >>sorting in SQL 6.5 and 7.0 without the ORDER BY clause is that SQL returns
    >>the rows in the order they were last modified. When you converted from

    6.5
    >>to 7.0, the entire database had to be exported from 6.5 and imported into
    >>the new 7.0 format. This could easily have affected the order in which

    the
    >>rows were last updated. Just an FYI...
    >>
    >>- Jason Rein
    >>
    >>"Jayesh Pandya" <pandya@hotmail.com> wrote:
    >>>
    >>>I have MS SQL Server 6.5 (SP 5) on MS Window NT 4.0 (SP 3). Recenly I

    >migrated
    >>>my database to MS SQL Server 7.0 (SP 1) on MS Windows NT (SP 3).
    >>>
    >>>SQL Server 6.5 is installed with default option (sort order, code page

    >etc).
    >>>SQL Server 7.0 is also installed with default option (sort order, code

    >page,
    >>>unicode etc)
    >>>
    >>>I am getting some result set in different order in 7.0 compare to 6.5

    >though
    >>>the stored procedure is same.
    >>>
    >>>I am also getting result set in different order with UNION clause in 7.0.
    >>>
    >>>e.g. without order by clause in select statement.
    >>>6.5 returns
    >>>state ZIP qty
    >>>TX 77050 10
    >>>TX 77090 15
    >>>
    >>>7.0 returns
    >>>state ZIP qty
    >>>TX 77090 15
    >>>TX 77050 10
    >>>
    >>>some cases if order by state is used then also result set are in different
    >>>order. if sort on state, zip in 7.0 then result set are matches.
    >>>
    >>>what will be reason of this and how can I solve this ?
    >>>
    >>>Last choice is to add order by clause to every stored procedure which

    is
    >>>returning result set.
    >>>
    >>>Thanks
    >>>
    >>>Jayesh
    >>>
    >>>sp_helpsort in 6.5
    >>>Sort Order Description
    >>>------------------------------------------------------------------
    >>>Character Set = 1, iso_1
    >>> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
    >>>Sort Order = 52, nocase
    >>> Case-insensitive dictionary sort order for use with several We
    >>> stern-European languages including English, French, and German
    >>> . Uses the ISO 8859-1 character set.
    >>>Characters, in Order
    >>>------------------------------------------------------------------
    >>> ! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { | }
    >>> ~ *
    >>> 0 1 2 3 4 5 6 7 8 9 A=a = = = = = = = B=b C
    >>> =c = D=d E=e = = = = F=f G=g H=h I=i = = = = J
    >>> =j K=k L=l M=m N=n = O=o = = = = = = P=p Q=q R=r S
    >>> =s T=t U=u = = = = V=v W=w X=x Y=y = Z=z = =
    >>>
    >>>----------------------------------
    >>>
    >>>sp_helpsort in 7.0
    >>>
    >>>Unicode data sorting
    >>>----------------------
    >>>Locale ID = 1033
    >>> case insensitive, kana type insensitive, width insensitive
    >>>
    >>>Sort Order Description
    >>>--------------------------------------------------------------------------

    >----------------------------------------------------------------------------
    >----------------------------------------------------------------------------
    >-----------------------------
    >>>
    >>>Character Set = 1, iso_1

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>> ISO 8859-1 (Latin-1) - Western European 8-bit character set.

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>>Sort Order = 52, nocase_iso

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>> Case-insensitive dictionary sort order for use with several We

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>> stern-European languages including English, French, and German

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>> . Uses the ISO 8859-1 character set.

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>>Characters, in Order

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>>--------------------------------------------------------------------------

    >----------------------------------------------------------------------------
    >----------------------------------------------------------------------------
    >-----------------------------
    >>>
    >>> ! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { | }

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>> ~ *

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>> 0 1 2 3 4 5 6 7 8 9 A=a = = = = = = = B=b C

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>> =c = D=d E=e = = = = F=f G=g H=h I=i = = = = J

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>> =j K=k L=l M=m N=n = O=o = = = = = = P=p Q=q R=r S

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>> =s T=t U=u = = = = V=v W=w X=x Y=y = Z=z = =

    >>
    >>>

    >>
    >>>

    >>
    >>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center