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 Ð=ð Þ=þ
>
>>
>
>>
>
>>
>>
>