MSSQLBugПочему не использовать ORDERED и затем HASH JOIN с сохранением порядка?
вы сами себе ответили
MSSQLBugПолучается, что HASH JOIN в Microsoft SQL Server никогда не сохраняет порядок записей?
Хэш джойн не сохраняет порядок. Вот что у Крейга Фридмана написано :
Nested Loops JoinMerge JoinHash JoinBest for …Relatively small inputs with an index on the inner table on the join key.Medium to large inputs with indexes to provide order on the equijoin keys and/or where we require order after the join.DW queries with medium to large inputs. Parallel execution that scales linearly.ConcurrencySupports large numbers of concurrent users.Many-to-one join with indexes to provide order supports large numbers of concurrent users.Best for small numbers of concurrent users with high throughput requirements.Stop and goNoNoYes (build input only)Equijoin requiredNoYes (except for full outer join)YesOuter and semi-joinsLeft joins only (full outer joins via transformation)All join typesAll join typesUses memoryNoNo (may require sorts which use memory)YesUses tempdbNoYes (many-to-many join only)Yes (if join runs out of memory and spills)Requires orderNoYesNoPreserves orderYes (outer input only)Yes No
Касательно того, почему такой тип соединения уже ответил invm, потому что дешевле. Проверьте сами, выполнив в одном окне и посмотрев на стоимость одного относительно другого (тот случай, когда проценты можно сравнивать).

MSSQLBugТак это же явно неправильное решение в такой ситуации.
Если у меня сравнить стоимости планов с JOIN-ом c TEST_InvLocs и без него, получится:
C JOIN: 2447.83
Без JOIN: 192.82

Не совсем понятно, как это "без join"?
Если сравнивать по типу соединения - то с Nested Loops дороже, что и объясняет выбор оптимизатора. Если вы считаете что он ошибается, то оформляйте запрос на microsoft connect со своим примером, и либо учтут, либо объяснят где не правы =)

MSSQLBug1998 год, между прочим. Почему бы это не реализовать в сервере?

Ох, не сыпьте соль на сахар, задайте этот вопрос продуктовой группе =) Кое-кто, вообще считает что сиквел закончился на 2005
версии, я не столь радикален, но видимо хекатон и прочее важнее, чем доработка существующего. В защиту МС скажу, что старые вещи тоже допиливают, взять тот же Cardinality Estimator. Правда, есть авторитетное мнение (не мое, человека из МС), что в некоторых случаях он хуже и наблюдаются регрессы. Ну на то есть трейсфлаги для отката к старому поведению.

MSSQLBugДа просто упорядоченная выборка из этой таблицы:

Не сравнивайте разные запросы по критерию стоимости, просто не имеет смысла.

MSSQLBugЖаль, выигрыш мог бы быть на порядок в аналогичных случаях.
Ну так вперед, оформляйте на SQL Server | Microsoft Connect , чего попросту жалеть.

MSSQLBugКак-то странно, что это больше никому не нужно (аналогичного запроса там вроде нет).
Ну это не повод отказываться от своей мысли. Вы создайте, а народ проголосует если нужно. Кстати, киньте сюда ссылку потом, чтоб активней голосовали. А в МС киньте ссылкой на документ. Я правда не читал его, не знаю насколько он релевантен.

Вообще, хэш джоин интересная тема, у меня есть в планах про него подробно написать, даже статья в черновиках лежит. После этой темы, подробнее изучу вопрос про сохранение порядка и документ этот почитаю. Интересно то, что некоторые нюансы меняются, но это нигде не афишируются, я вот описывал случай: Забавный случай упрощения соединений - поди узнай, что там внутри в 2012 сервере поведение при упрощении соединений поменялось, хотя в книге архитектора оптимизатора описано другое поведение. ИМХО, остро не хватает актуального материала и литературы по теме оптимизации. Можно, конечно, копать самому, что я и делаю, но очень много времени на это уходит. По этому, приводя ссылку на тот же блог Крейга, неплохо бы убедиться, что информация актуальна и по сей день. Видимо, в отношении Хэш Джойна, информация актуальна, но без самостоятельного изучения сказать вот так прямо, что он не сохраняет порядок во всех случаях тоже нельзя.