0.5 を四捨五入するときは…

いきなりですが、次のクエリの実行結果どうなると思います？

SELECT ROUND(0.5, 0, 0)
SELECT ROUND(CAST(0.5 AS float), 0, 0)
SELECT ROUND(CAST(0.5 AS real), 0, 0)
SELECT ROUND(CAST(0.5 AS decimal(1,1)), 0, 0)

実行結果はこちら。

これのカラクリはこちらに載ってます。

データ型の変換 (データベースエンジン) より引用。

decimal 型データと numeric 型データの変換
SQL Server では、decimal 型と numeric 型の場合、有効桁数と小数点以下桁数の組み合わせが異なる場合は、異なるデータ型と見なされます。たとえば、decimal(5,5) と decimal(5,0) は異なるデータ型と見なされます。

Transact-SQL ステートメントでは、小数点の付いた定数は、必要最小限の有効桁数と小数点以下桁数で自動的に numeric 型の値に変換されます。たとえば、定数 12.345 は有効桁数が 5、小数点以下桁数が 3 の numeric 型に変換されます。

数値リテラルは、小数点が付いていると必要最小限の有効桁数の numeric にされるんですね。で、0.5 は numeric(1, 1) になりこれを四捨五入すると 1.0 になり桁溢れしてしまう様です。
ちなみに、1.5 の場合は、numeric(2, 1) になるので、四捨五入しても大丈夫です。0.〜のリテラルは、numeric(n, n) になるので注意が必要です。