Is this behaviour correct?

It is intentional (by design) behaviour; whether it is 'correct' or not is more a matter of opinion.

The general point is: SQL Server does not guarantee the timing or number of evaluations of expressions. This behaviour exists to give the query optimizer the freedom it needs to find good execution plans.

As a consequence, query optimization may change the query's output or runtime behaviour, if there is a dependency on the timing or number of executions of an expression (or scalar function).

In the question's examples, the runtime behaviour depends on whether the query optimizer identifies each expression as a runtime constant or not. The execution engine may decide to evaluate (and cache) runtime constant expressions before query execution begins.

If the optimizer identifies the expression 1/0 as a runtime constant, the XML form of show plan output will show it labelled as ConstExprxxxx:

Runtime constants like this can be evaluated once before query execution begins, and the result cached, as mentioned previously. Where this occurs, a divide by zero error is returned during this pre-query constant expression evaluation.

Adding the index to the table happens to take a route through query optimization that does not result in 1/0 being labelled a runtime constant (no ConstExprxxxx below):

In this particular case, the presence of a uniqueness constraint means the optimizer decides there is no benefit in caching the result of an expression that will only be evaluated once. The start-up filter means the faulty expression is never evaluated.

These behaviours are not documented or completely determined by the presence of ConstExpr labels in all cases. The answer is not to write code that depends on the timing or number of executions of expressions.

Is this behaviour correct?

It is intentional (by design) behaviour; whether it is 'correct' or not is more a matter of opinion.

The general point is: SQL Server does not guarantee the timing or number of evaluations of expressions. This behaviour exists to give the query optimizer the freedom it needs to find good execution plans.

As a consequence, query optimization may change the query's output or runtime behaviour, if there is a dependency on the timing or number of evaluations of an expression (or scalar function).

In the question's examples, the runtime behaviour depends on whether the query optimizer identifies each expression as a runtime constant or not. The execution engine may decide to evaluate (and cache) runtime constant expressions before query execution begins.

If the optimizer identifies the expression 1/0 as a runtime constant, the XML form of show plan output will show it labelled as ConstExprxxxx:

Runtime constants like this can be evaluated once before query execution begins, and the result cached, as mentioned previously. Where this occurs, a divide by zero error is returned during this pre-query constant expression evaluation.

Adding the index to the table happens to take a route through query optimization that does not result in 1/0 being labelled a runtime constant (no ConstExprxxxx below):

In this particular case, the presence of a uniqueness constraint means the optimizer decides there is no benefit in caching the result of an expression that will only be evaluated once. The start-up filter means the faulty expression is never evaluated.

These expression evaluation behaviours are not documented or completely determined by the presence of ConstExpr labels in all cases. The answer is not to write code that depends on the timing or number of evaluations of expressions by the execution engine.

Is this behaviour correct?

It is intentional (by design) behaviour; whether it is 'correct' or not is more a matter of opinion.

The general point is: SQL Server does not guarantee the timing or number of evaluations of expressions. This behaviour exists to give the query optimizer the freedom it needs to find good execution plans.

As a consequence, query optimization may change the query's output or runtime behaviour, if there is a dependency on the timing or number of executions of an expression (or scalar function).

In the question's examples, the runtime behaviour depends on whether the query optimizer identifies each expression as a runtime constant or not. The execution engine may decide to evaluate (and cache) runtime constant expressions before query execution begins.

If the optimizer identifies the expression 1/0 as a runtime constant, the XML form of show plan output will show it labelled as ConstExprxxxx:

Runtime constants like this can be evaluated once before query execution begins, and the result cached, as mentioned previously. Where this occurs, a divide by zero error is returned during this pre-query constant expression evaluation.

Adding the index to the table happens to take a route through query optimization that does not result in 1/0 being labelled a runtime constant (not ConstExprxxxx below):

The behaviour is not documented or completely determined by the presence of ConstExpr labels in all cases. The answer is not to write code that depends on the timing or number of executions of expressions.

Is this behaviour correct?

It is intentional (by design) behaviour; whether it is 'correct' or not is more a matter of opinion.

The general point is: SQL Server does not guarantee the timing or number of evaluations of expressions. This behaviour exists to give the query optimizer the freedom it needs to find good execution plans.

As a consequence, query optimization may change the query's output or runtime behaviour, if there is a dependency on the timing or number of executions of an expression (or scalar function).

In the question's examples, the runtime behaviour depends on whether the query optimizer identifies each expression as a runtime constant or not. The execution engine may decide to evaluate (and cache) runtime constant expressions before query execution begins.

If the optimizer identifies the expression 1/0 as a runtime constant, the XML form of show plan output will show it labelled as ConstExprxxxx:

Runtime constants like this can be evaluated once before query execution begins, and the result cached, as mentioned previously. Where this occurs, a divide by zero error is returned during this pre-query constant expression evaluation.

Adding the index to the table happens to take a route through query optimization that does not result in 1/0 being labelled a runtime constant (no ConstExprxxxx below):

In this particular case, the presence of a uniqueness constraint means the optimizer decides there is no benefit in caching the result of an expression that will only be evaluated once. The start-up filter means the faulty expression is never evaluated.

These behaviours are not documented or completely determined by the presence of ConstExpr labels in all cases. The answer is not to write code that depends on the timing or number of executions of expressions.