Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A developer is creating a relational database in Azure SQL Database. They need to define a column that stores monetary values and ensures precise calculations without loss of precision, which is critical for financial transactions. Which data type should they choose for this column?
- AMONEY
- BDECIMAL
- CFLOAT
- DREAL
Show answer & explanationAnswer & explanation
Correct answer: B. DECIMAL
The DECIMAL (or NUMERIC) data type stores exact numeric values with a fixed precision and scale. This is crucial for financial data where even small rounding errors from floating-point types (like FLOAT or REAL) are unacceptable. `DECIMAL(P,S)` allows explicit control over the number of digits before and after the decimal point.
Why the other options are wrong
- A. MONEY is a specific data type in SQL Server for monetary values, but DECIMAL offers more explicit control over precision and scale and is generally preferred for strict financial accuracy, and is more universally applicable across relational databases (NUMERIC is the ANSI standard).
- C. FLOAT is an approximate-number data type, which can lead to rounding errors and is unsuitable for precise financial calculations.
- D. REAL is also an approximate-number data type (single-precision float) and shares the same precision issues as FLOAT for monetary values.
DECIMAL Data Type
A SQL data type used to store exact numeric values with a fixed precision and scale, often used for monetary or other precise calculations.
- Stores exact numeric values
- Prevents rounding errors common with floating-point types
- Syntax: `DECIMAL(precision, scale)`
- Ideal for financial and scientific data
Memory trick: Numbers are exact (DECIMAL) or approximate (FLOAT).