3

I need to choose a data type for SQL Server( BigInt vs Decimal) on the basis of performance and querying (10 digit number)

  • Since you are only concerned with 10 digit number (max), you should go for decimal. Decimal wont loose fraction when doing division. – Kin Shah Jun 1 '15 at 14:54
  • Are you pulling this value into app code? If so, what language? – Solomon Rutzky Jun 1 '15 at 20:33
7

If you don't need fractions, choose bigint. In your case it will be a byte smaller:

10 digit decimal is 9 byte: https://msdn.microsoft.com/it-it/library/ms187746.aspx

bigint is 8 byte: https://msdn.microsoft.com/it-it/library/ms187745.aspx

Plus, it is faster (it has no variable part, and it is an integer type, much faster processed by cpu).

  • Can i get any proper documentation showing BigInt is faster than Decimal? – MADHAV Jun 1 '15 at 15:53
  • Take for example a look here: mssqltips.com/sqlservertip/3323/… – user_0 Jun 1 '15 at 17:14
  • @user_0, although one would think bigint would be faster than numeric/decimal, I recently ran some performance tests (large multi-million row join queries) to validate this theory for a non-functional user story. There was no significant difference in performance with our application so we decided not to proceed with the change. I would use bigint for new development, though. – Dan Guzman Jun 2 '15 at 12:02
0

If you are only dealing with whole numbers then BIGINT will be a higher performance datatype because it won't have to deal with any rounding.

If you need decimal places you must use decimal

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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