Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureHard
A data engineer is working with an Azure SQL Database. The database stores sensor readings, and the `ReadingValue` column needs to store precise numerical data, such as temperature or pressure, which can have varying numbers of digits before and after the decimal point. The maximum value expected is 999.99999, and the minimum is -99.99. Which data type should be chosen to ensure accuracy and minimize storage waste?
- AFLOAT
- BDECIMAL(8,5)
- CNUMERIC(7,5)
- DREAL
Show answer & explanationAnswer & explanation
Correct answer: B. DECIMAL(8,5)
DECIMAL(8,5) provides the necessary precision (5 decimal places) and scale (8 total digits) to store values like 999.99999 (3 before, 5 after = 8 total) and -99.99 (2 before, 2 after = 4 total, fits within 8 total). This ensures exact precision without floating-point inaccuracies.
Why the other options are wrong
- A. FLOAT is approximate numeric and can suffer from precision issues, not suitable for exact values. It also doesn't specify precision/scale directly.
- C. NUMERIC(7,5) allows only 7 total digits, which is insufficient for 999.99999 (8 total digits).
- D. REAL is a single-precision floating-point number, offering even less precision than FLOAT and unsuitable for exact decimal storage.
DECIMAL Data Type
An exact numeric data type in SQL Server that stores numbers with a fixed precision and scale. It is used for financial data or any values where exact decimal precision is critical.
- Syntax: DECIMAL(P, S), where P is precision (total digits) and S is scale (digits after decimal point).
- Precision includes digits on both sides of the decimal point.
- Provides exact storage, unlike FLOAT or REAL which are approximate.
Memory trick: Decimal: Precision, Scale, Exact.