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?
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.