Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

T-SQL – Real of Decimal datatype?

Dear experts,

I have an SQL 2008R2 database with a filed in it which is now Money datatype. But it look I’ll need to store more than 4 digits and as you know it is limit to for example 0.2345, but now I should be able to write a value like 0.234567 (up to 10 digit depends of customer precision selected).

So my question is – what data type I should select in SQL ? Decimal or real or float? This table is expected to have milions of records in few years and will be heavy used.

This amount will be used for :
1) A lot of Sum over it
 2)Delete other Money value to this value and then and then multiple by this value – 10 times rather than 1)

I think for now to use Decimal – like (12,9)
0
dvplayltd
Asked:
dvplayltd
  • 3
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I should be able to write a value like 0.234567 (up to 10 digit depends of customer precision selected).
numeric(11, 10) if we're talking only a single number left of the decimal, and up to 10 digits after.
The 11 can go as high as 38 total digits.

decimal and numeric are functionally the same

float and real are approximate data types, and I haven't had experience with this but it would scare most uses away from it.
0
 
dvplayltdAuthor Commented:
To Jim,

Thanks for your time. The value may be 99.23456 that is why 12,9.

So you tell me to use numeric(12,9) instead decimal(12,9) ? This is different types ?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Functionally they're the same animal.  Not sure why they both exist though, my guess would be backwards compatibility.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
HuaMinChenBusiness AnalystCommented:
Hi,
You can have more decimal precision by using the data type like
decimal(23,7)
0
 
dvplayltdAuthor Commented:
10x
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now