-2

This question already has an answer here:

while I inserting records in a table it throws :

Arithmetic overflow error converting expression to data type int.

Code:

create table JEmp
(
J_Id int Identity,
J_Name varchar(100),
J_Contact int,
J_City varchar(100),
primary key(J_Id)
)

insert into JEmp(J_Name, J_Contact, J_City) 
values ('David Warner', 9821766418, 'New York')

marked as duplicate by Max Vernon, Paul White sql-server Nov 1 '18 at 20:36

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

5

It's because 9821766418 is greater than the integer maximum of 2147483647.

If you want to be able to store phone numbers, you need to use a BIGINT, or even better, store them as a string. You'll either be searching for them by a direct equality, or a partial condition anyway. Storing them as a number doesn't really help you in any way, here.

2

Int datatype's max limit is 2147483647. Based on the wiki - you should store phone numbers as strings (varchar or nvarchar).

1

Adding to the other answers, you could use the numeric datatype for large numbers. If, for instance, you’re working with phone numbers that go to ten digits, you can use numeric(10, 0), meaning a number of (up to 10 digits, 0 of which are to the right of the decimal point).

While bigint ”only” goes to about 19 digits, you can go all the way to 38 with numeric, but numeric does use a little more storage space for each equivalent value, compared to int/bigint. However, numeric still uses about half of the storage of a comparable varchar.

  • 4
    The problem with using numeric (and int and bigint) for phone numbers is that numbers like 123456789 and 0123456789 and 00123456789 will be treated as the same number. The leading 0s are lost. – ypercubeᵀᴹ Jun 25 '18 at 18:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.