ORDER BYを指定しないSELECT文の並び順の保障について

Question

ORDER BYを指定しないSELECT文の並び順の保障について

相談があります。
当方で、データをINSERTして、インサートした順序で処理をするストアドプロシージャを作っています。

そこで、問題となっているのが、レコード単位の処理の順序です。
下記のどっちが正しいのでしょうか？

１）PKもクラスタインデックスもない場合は、SQL ServerはINSERTした順序で結果を返すことを保証する。

２）SQL ServerはたまたまINSERTした順序で結果を返しているので、きちんと並び順を制御しなければ、INSERTした順序でSELECTは結果を返さない。

私のほうでは、２）だと思っているのですが、その文献がないうえ、
テストでも１）となるため、１）でストアドがリリースされてしまいそうで、困っております。

なにが正しいかご存知の方ご教授お願いいたします。

↓調査している方もいるようです。

http://d.hatena.ne.jp/odashinsuke/20100518/1274185809

 

Answer 1

どちらも正しいとは言えないと思いますよ。

１）に関しては「保証」も「保障」もしてないはずです。（ソースが見付からない・・・）

２）に関しては表現の問題ですが、クラスタ化インデックスが無い場合にINSERTした順序で結果が返っているのは「たまたま」ではないと思います。

※「絶対的なもので無い = たまたま」 という認識であればその通りだと思います。

 

ただしここで注意すべきなのは、１）の前提で作成した場合、バグが生じたら完全に自己責任になるという事だと思います。

Microsoftとして保証しないとしている機能を、テストして問題なさそうだからそのまま使うというのは少し暴挙かなぁと・・・

 

該当テーブルにプライマリキーが無いのであれば、IDENTITY のキーを保持した上でその列でORDER BY句を指定する。

というのが取るべき対応だと思います。

Answer 2

ヒープテーブルについては詳しくはないですが、クラスタ化されたテーブルについては、

・INSERT した順序やクラスタ化インデックスの順序とは関係ない順序で返される

ですね。

クラスタ化キー（何も設定せずに作成されたプライマリキー）は、エクステント上での並び順を保証しますが、抽出時には複数のエクステントから抽出されることも当然あります。このときに Order By が指定されていないとエクステントが並べ替えられないため、HDD 上で連続した領域が優先的に取り出され、部分部分は連続するが全体としては連続しない状態になります。

このため、たとえクラスタ化されたプライマリキーであっても、Order By 句に指定しない場合には並び順は保証されません。

このことは、ヒープテーブルについても同じことが言えるのではないかと思います。複数のエクステントに分割されたヒープテーブルに対してクエリをかけると、INSERT 順ではなくて格納されているエクステントの処理順に取り出されるかと。

 

Answer 3

直接は記述されていませんが、テーブルとインデックスの編成にある

データ行は特定の順序で格納されず、データ ページの並びにも特定の順序はありません。

という記述を根拠に 2) たまたまINSERTした順序と判断できるのではないでしょうか。

Answer 4

ヒープ構造 http://msdn.microsoft.com/ja-jp/library/ms188270.aspx

をみて、

CREATE TABLE Heap1(N INT NOT NULL);

INSERT INTO Heap1 VALUES (1);
INSERT INTO Heap1 VALUES (2);
INSERT INTO Heap1 VALUES (3);
INSERT INTO Heap1 VALUES (4);
INSERT INTO Heap1 VALUES (5);

DELETE FROM Heap1 WHERE N = 2;

INSERT INTO Heap1 VALUES (6);

SELECT N FROM Heap1;

N
--
1
6
3
4
5

挿入順なんて保証されていませんね。

 

Answer 5

ORDER BYを指定しないSELECT文の並び順は保障できませんよ

↓参照

http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order

There is no default sort order. Even if the table has a clustered index, you are not guaranteed to get the results in that order. You must use an order by clause if you want a specific order.

---

• The blog of typewriter職人
• Convert C# to VB.NET
• /*If my concept is wrong ,please correct me.Thanks.*/