こんにちは、nagino です。
# 投稿直前にうっかり消してしまって書き直したので、ちょっと凹み中です。
# 書き直したものの分量が半減したので、何か記載漏れがあるかも・・・？
SQL Server では、比較演算子など SQL の基本的な事項は ANSI の標準規格に準拠しています。
単純に比較演算子の挙動について知りたいのであれば、ANSI の規格書をあたるのが確実ですが、なかなかな値段になります。
文字列同士の比較演算では、基本的には辞書式順序で比較します。
ただし、照合順序が関係してくるといくつか考慮すべきポイントが生じます。
ここまでの流れを追うと照合順序の話が出ていますので、照合順序と文字列の比較との関連を知りたいということのようですね。
公開されていない詳細な仕様については、Microsoft の有償サポートに問い合わせされるのが一番確実ですが、参考までに私が把握している範囲でいくつか補足しておきます。
先ず、ここでは Windows 照合順序に限定して考えます。
SQL 照合順序は互換性のために維持されており現在は Windows 照合順序を利用することが推奨されているということと、実装の過程で特殊な実装がされたものが見受けられることから、ここでは触れません。
ちなみに余談ですが、Windows 照合順序は基本的に Windows OS が管理しています。
SQL Server はその管理されている照合順序を利用している形です。
ですので、.NET Framework でアプリケーションを作った場合も、SQL Server と同様の並び替え順序を実現することができます。
（そのため、SQL Server に関するドキュメントより、Windows OS における Unicode のサポートや、Windows 上の開発での文字列処理に関するドキュメントなどの方が詳細が記載されているかもしれません。）
辞書式順序が広く一般に使われている中で、Windows OS や ANSI の SQL がそれに従ったという流れではないかと思います。
また、スペイン語の Ch から始まる単語の扱いなど、言語ごとに特殊な要因がありますので、日本語についてのみ考えます。
日本語も特殊な要因がありますが、そのうちのいくつか理解しておくべき事項については最後に触れます。
さて、照合順序と文字列の比較との関連を理解するためには 2 点基礎的な内容を理解しておく必要があります。
1 つめは文字コード（コードポイント）です。
Windows OS では、いくつかの理由から Shift JIS と Unicode がサポートされています。
SQL Server でも同様です。
現在はいずれも JIS やユニコードコンソーシアムといったところが標準を定義しており、Microsoft が定義しているわけではありません。
ただし、実装の過程で歴史上の問題などから、一部標準と差異（Shift JIS は全角チルダ、Unicode はサロゲートペアや結合文字）がありますので注意が必要です。
個々の差異については検索すれば情報が出てきますし、ここでは全体像や基本事項をテーマとして考えていますので、詳細は割愛します。
2 つめはバイナリ照合順序です。
これは、文字列を比較する際に辞書式順序を使い、各文字の比較は文字コード別に行います。
細かい話をすると、文字列の長さが異なる場合は短いほうに半角スペースを追加して長さを揃えてから比較します。
このあたりの半角スペースによる長さ揃えなども ANSI の SQL-92 で定義された動作で、Microsoft が定義しているわけではありません。
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316626
（日本語版の KB は機械翻訳ですが、仕様について記載した厳密性を要求されるドキュメントのため、とても読めるレベルにありません。また、KB 自体は LIKE 演算子だけ ANSI SQL-92 と異なる挙動をすることに関する内容です。）
ここまでの 2 点を確認するためのクエリを 1 つ例示しておきます。
全角の「Ａ」とカタカナの「ア」を比較しています。
バイナリ照合順序で文字コードによって文字の大小関係が変化していることが確認できます。
DECLARE @ShiftJis1 char(2)
DECLARE @ShiftJis2 char(2)
DECLARE @Unicode1 nchar(1)
DECLARE @Unicode2 nchar(1)
SET @ShiftJis1 = 'Ａ'
SET @ShiftJis2 = 'ア'
SET @Unicode1 = N'Ａ'
SET @Unicode2 = N'ア'
SELECT
CASE
WHEN @ShiftJis1 < @ShiftJis2 COLLATE Japanese_90_BIN2 THEN 'ShiftJis <'
ELSE 'ShiftJis >'
END
SELECT
CASE
WHEN @Unicode1 < @Unicode2 COLLATE Japanese_90_BIN2 THEN 'Unicode <'
ELSE 'Unicode >'
END
ここまでを前提として、日本語のバイナリ照合順序以外の照合順序を考えます。
これらは日本語特有の要因が関連しています。
日本語では半角の「0」と全角の「０」、ひらがなの「あ」とカタカナの「ア」など、同じ意味を持つ文字が多数あります。
これらを同じとみなすかどうかについて、照合順序のサフィックスでその動作を定義することができます。
http://technet.microsoft.com/ja-jp/library/ms143515.aspx
ただし、バイナリ照合順序以外の場合は、この同じ意味かどうかを判断するための情報を持つ必要があります。
そのため、文字コードではなくそれらを勘案した重みを別途管理して、それに基づき比較を行っています。
この重みを管理しているデータを過去の経緯から私は重み付けテーブルと呼んでいるので、ここではこの（非公式な）用語で以下記載します。
実際の実装はさておき、バイナリ照合順序は文字コードで、バイナリ以外の照合順序は重み付けテーブルで比較すると理解されると良いと思います。
（実際の実装は非公開ですので、厳密には重み付けテーブルが存在するかどうか自体も謎です。ただし動作を理解しやすいので、以下では存在を前提に記載します。）
重み付けテーブルには各文字の重みを設定します。
基本的には文字コードに対応する重みを設定し、同じ意味と判断する文字だけ文字コードと異なる重み（同じ意味の文字には同じ重み）を設定します。
重み付けテーブルを作成した時点で文字が割り当てられていない文字コードについては当然設定しません。
しかし Unicode が 既に 5.0 まで拡張されたように、後から追加された文字が多数あります。
これらは重み付けテーブルでは重みが無いため、並び替えなどでは空文字と同様に処理されます。
重み付けテーブルはバージョンごとに更新されていて、それらが照合順序の Japanese、Japanese_90、Japanese_100 といった数字（世代）に該当します。
また、同じ文字とする文字についてはどちら側の文字として処理されるか（半角の「0」を全角の「０」と読み替えるのか、全角の「０」を半角の「0」と読み替えるのか）については明記したドキュメントは無かったかと思います。
そのため、実態としては Microsoft 独自仕様と言えなくもありません。
バイナリ照合順序は文字コードによる単純比較なので、重み付けが無い今後追加される文字もソートできる、パフォーマンスが良い、仕様が明確です。
ですので、仕様を明らかにする必要があるケースではバイナリ照合順序を使用するのがお勧めです。
他システムとの互換性や、一般的なアプリケーションの動作でよいという場合は適切な照合順序を選択すれば良いかと思います。
ただしその場合も、重み付けテーブルの内容を考えて可能な限り新しい世代の照合順序を使うことがお勧めです。
システムにおける照合順序は基本的にはアーキテクトが判断、決定する事項だと思いますので、通常開発者や管理者はここまで深い理解は必要なく Technet/MSDN ライブラリで公開されている程度の情報で十分だと思います。
ただ、いくつか関連情報も公式にリリースされていますので、参考までにあげておきます。
ただ、なにはともあれ ANSI の規格書が一番の基本です。
●SQL Server の JIS2004 対応に関するガイドライン
http://www.microsoft.com/downloads/details.aspx?FamilyId=E942342A-719F-4841-A9D2-F6D9FD58299F&displaylang=ja
●マイクロソフト公式解説書 Unicode による JIS X 0213 実装入門
ISBN 978-4-89100-608-2
（※手元の初版は誤字、誤植が非常に多く、とても読めません。そろそろ新しい版をもう 1 冊買おうと思いつつ忘れていました・・・。）
他に余談として、他の言語の BIN2 と Japanese_90_BIN2 との違いは何か？（実際挙動に差異は無いが、異なる照合順序同士は比較演算できないので COLLATE 句の指定が必要）ですとか、SQL Server 2008 / Windows Server 2008 から実装された部首画数順（文字コード順ではない）ですとか、色々掘り下げると技術的に面白い事項ではあります。
私も ANSI の SQL-92 を全て把握しているわけではないので、ANSI で照合順序がどういう位置づけなのかといったことはちょっと分かりません。
10 数部からなる規格書が JSA で販売されていますが、1 部数万円するので個人ではちょっと手が出ません。（いずれは欲しいですが・・・）
誤記や不足にお気づきの方は、補足いただければ幸いです。
MCITP（Database Developer/Database Administrator）
