Decimal/Int Data Types in SQL Server

Question

I need to enter a value which can be either decimal or int. What is the data type I need to set for the column in the database?

@Radu, I used decimal(3,2). But when I tried to insert the value 12, it gives the error: "Msg 8115, Level 16, State 8, Line 2 Arithmetic overflow error converting int to data type numeric. The statement has been terminated." — MusicLovingIndianGirl
– MusicLovingIndianGirl, Commented Sep 19, 2013 at 6:50
Try decimal(4,2). Btw how many didgit int value you can have? — Dhwani
– Dhwani, Commented Sep 19, 2013 at 6:57
Decimal(3,2) means three digits all in all, two of which being after the decimal separator. So you can only enter one digit before the comma. Usually you'd use something like DECIMAL(18,3) or so. — Thorsten Dittmar
– Thorsten Dittmar, Commented Sep 19, 2013 at 7:03

nimdil · Accepted Answer · 2013-09-19 07:15:45Z

2

considering you have two different value types it may be more beneficial to have to NULLable columns with different data type. If you have like 100-1000 rows in a table it's not an issue and you can go with big DECIMAL(18,3) or even DECIMAL(38,10) as needed and convert everything to this big format. However if you hit billions of rows you should keep in mind that while standard INT is only 4 bytes big, the smallest DECIMAL is 5 bytes and that's for precision under 10. For wider values it can easily grow into 9 bytes or more, more than doubling storage space used.

answered Sep 19, 2013 at 7:15

nimdil

1,38111 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Decimal/Int Data Types in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related