How does SQL Server implicit type casting work in this case?

Question

When I try this ad-hoc query in SQL Server (assume UserId is a NVARCHAR field):

SELECT * FROM MyUser WHERE UserId = 123456

I get this error:

Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value 'foo' to a column of data type int.

Obviously there is a value 'foo' somewhere down my UserId column.

Why is SQL Server trying to convert my entire column to INTEGER instead of doing what seems obvious to me: converting my search value to NVARCHAR?

Answer 1

The comparison is done using the rules of Data Type Precedence:

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

Precedence 1 means the highest. So NVARCHAR type (precedence 25) is converted to int (precedence 16), because NVARCHAR has a lower precedence in this scale.