Understanding SQL Server Int Data Types

Question

I have a table with a column called PtObjId of the INT data type.

From my understanding by looking at the thread located Microsoft Documentation here. I can only store up to values that are +/- 2,147,483,647

If I run this query:

Select top 100 *
from [table]
where [PtObjId] IN (44237141916)

Shouldn't it error out?

Why does this query error out below:

select top 100 *
from [table]
where [PtObjID] IN ('44237141916')

but the top query doesn't error out?

Answer 1

This sqlshack-article explains details about implicit conversions in SQL-Server.

One value must implicitly be cast for the comparison. The literal 44237141916 is treated as decimal, which has a higher precedence than int, so the other operand is cast to decimal.

A full list of precedences (and a table of possible conversions) are given in the article, an extract:

10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint 
...
25. nvarchar (including nvarchar(max))

^{(lower number = higher precedence)}

In the case of int and nvarchar, the one with higher precedence is int, and this leads to an overflow for 44237141916.