none
ORDER BYを指定しないSELECT結果が互換性レベルによって異なる件 RRS feed

  • 質問

  • インデックスのないテーブルへ順にInsertして、ORDER BYを指定しないでSELECTした場合、
    互換性レベルの違いにより結果の順序が異なります。

    データベースのプロパティ>オプション>互換性レベルの設定変更で
    SQL Server 2008(100)からSQL Server 2012(110)以降へ変更すると次のようになります。

    SQL Server 2012以降の変更点の説明に、関連しそうな記述は見当たりませんでした。
    何が変わったのか教えてもらえないでしょうか。

    [SQL]
    CREATE TABLE #TBL1([NUM] INT NOT NULL)

    INSERT INTO #TBL1 VALUES (3)
    INSERT INTO #TBL1 VALUES (1)
    INSERT INTO #TBL1 VALUES (2)

    INSERT INTO #TBL2 SELECT * FROM #TBL1 ORDER BY NUM DESC --降順にInsert

    SELECT * FROM #TBL2 --ORDER BY を指定しない場合

    [結果]
    SQL Server 2008(100):3,2,1
    SQL Server 2012(110):3,1,2
    SQL Server 2014(120):3,1,2
    SQL Server 2016(130):3,1,2

    [実行プラン] 実行プランでも違いがあるようです。

    SQL Server 2008(100)
     INSERT - Table Insert(#TBL2) - Top - Sort - Table Scan(#TBL1)
      …クエリ4でInsertとSort(並び替え)が行われる

    SQL Server 2012(110) ~ SQL Server 2016(130)
     INSERT - Table Insert(#TBL2) - Table Sacn(#TBL1)
      …クエリ4でInsertのみ

    以上、よろしくお願いします。


    • 編集済み kykthcgmy 2019年7月7日 14:09 資料追加
    2019年7月4日 15:07

回答

  • order byを指定しない場合、並びは不定になります。よって、不定以外の並びを記述した文書はおそらく見つからないと思います。

    (参考)
    DO’s&DONT’s #13: 絶対にやってはいけないこと – ORDER BY が指定されていないクエリの結果が一定の順番に並んでいると仮定すること
    https://blogs.msdn.microsoft.com/jpsql/2012/02/12/dosdonts-13-order-by-1/


    ★良い回答には質問者は回答済みマークを、閲覧者は投票を!

    • 編集済み trapemiya 2019年7月5日 1:15 一部削除
    • 回答としてマーク kykthcgmy 2019年7月14日 15:28
    2019年7月5日 1:14

すべての返信

  • order byを指定しない場合、並びは不定になります。よって、不定以外の並びを記述した文書はおそらく見つからないと思います。

    (参考)
    DO’s&DONT’s #13: 絶対にやってはいけないこと – ORDER BY が指定されていないクエリの結果が一定の順番に並んでいると仮定すること
    https://blogs.msdn.microsoft.com/jpsql/2012/02/12/dosdonts-13-order-by-1/


    ★良い回答には質問者は回答済みマークを、閲覧者は投票を!

    • 編集済み trapemiya 2019年7月5日 1:15 一部削除
    • 回答としてマーク kykthcgmy 2019年7月14日 15:28
    2019年7月5日 1:14
  • kykthcgmyさん、こんにちは。フォーラムオペレーターのHarukaです。
    MSDNフォーラムにご投稿くださいましてありがとうございます。

    まず、ALTER DATABASE(Transact-SQL)互換性レベルに参照し、互換性レベルの違いを見つけてください。 

    次に、INSERT ... SELECTステートメントでORDER BYを指定すると、SQL Serverは指定された順序でクエリを実行します。 
    ただし、必ずしも行がその順序で挿入されるわけではありません。 
    特定の順序で行を挿入する必要がある場合は、順序を保証するためにインデックスを使用することがあります。 

    ターゲットテーブルを順番に並べたい場合は、ターゲットテーブルにインデックスを作成してみてください。

    どうぞよろしくお願いいたします。


    MSDN/ TechNet Community Support Haruka

    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、
    ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~

    2019年7月9日 6:21
    モデレータ