Why doesn't this windowed expression result in a divide by zero error?
You're not allowed integer literals in this ORDER BY but you are allowed expressions returning integers - otherwise you wouldn't be able to use e.g. CASE expressions.
So that's why you're not allowed OVER (ORDER BY 1) but are apparently allowed OVER (ORDER BY 1/0). I don't think it's a deliberate feature to allow you to write this. You're certainly not allowed a divide by zero if your expression is actually dependent on any columns from the rows - this generates an error:
select name,object_id,ROW_NUMBER() OVER (ORDER BY 1/(object_id-3))
from sys.objects
So, I'd put it down to "the optimizer is smart enough to realise that this expression doesn't depend on any row values, therefore it's the same value for all rows so we do not need to compute it". In a sane language, this would generate a warning.
Let's try a few more examples...
ROW_NUMBER() OVER (ORDER BY 2/1)
Windowed functions and NEXT VALUE FOR functions do not support integer indices as ORDER BY clause expressions.
The problem with 2/1 is that it gets constant folded to 2 early in the optimisation process so is treated the same as ROW_NUMBER() OVER (ORDER BY 2) which is not permitted.
ROW_NUMBER() OVER (ORDER BY LOG(1))
Windowed functions and NEXT VALUE FOR functions do not support constants as ORDER BY clause expressions.
Again constant folding kicks in - this time the result is not an integer index but SQL Server doesn't allow constants anyway.
ROW_NUMBER() OVER (ORDER BY LOG(-1))
This succeeds on recent versions of SQL Server - on old versions such as SQL Server 2008 you will see An invalid floating point operation occurred.. This specific case is mentioned in the context of CASE here. The compile time constant folding broke the semantics of CASE and this was fixed in more recent versions.
The suppression of constant folding in these error cases (LOG(-1) and 1/0) is enough for it to bypass the checks that give the error messages above. However SQL Server still recognizes that the expression is in fact a constant and can be optimized out later (so you don't get a sort operation to order the rows by the result of this expression).
During the simplification phase ROW_NUMBER ORDER BY non_folded_const_expression is simplified to just ROW_NUMBER and non_folded_const_expression removed from the tree as no longer referenced. Thus it causes no problems at runtime as it does not even exist in the final execution plan.