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.
44237141916 is treated as decimal not bigint - this is for backwards compatibility reasons as the behaviour existed before bigint datatype was in the product
where cast([PtObjId] as bigint) IN (44237141916).