I am trying to find out "why" this behaviour is seen and believe that there is definitely something influencing this (I am just not able to find out what). It's not Data Type Precedence; but can be COLLATION Precedence.

Based on what I found in BOL, I developed the following small test:

SELECT CONVERT(NVARCHAR(2),0) -- returns 0

-- returns 0, i.e. is NOT a Numeric value

SELECT ISNUMERIC('')

-- returns 0

SELECT CONVERT(INT,N'')

SELECT CONVERT(INT,N' ')

SELECT CAST(N' ' AS INT)

/*

Data type conversion (Database Engine):

"Character expressions that are being converted to an exact numeric data type must consist of digits, a decimal point, and an optional plus (+) or minus (-). Leading blanks are ignored. Comma separators, such as the thousands separator in 123,456.00, are not allowed in the string."

*/

-- returns Error

SELECT CONVERT(NUMERIC,N'')

/*

CAST & CONVERT :

"SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal."

*/

If conversion to NUMERIC returns NULL, why does the conversion to INT succeed, and why does a blank string convert to 0?