SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale

Using DECIMAL or NUMERIC types without specifying precision and scale can lead to unintended storage and calculation issues.

Description

When you define columns, variables, or parameters using DECIMAL or NUMERIC data types without specifying precision and scale, SQL Server defaults to a precision of 18 and a scale of 0. This can potentially lead to problems such as inefficient storage utilization and unexpected calculation results.

For example:

SQL
Copy
1-- Example of problematic declaration
2DECLARE @Rate DECIMAL;

When declared this way, @Rate can only store whole numbers up to 18 digits, making it inefficient for handling values requiring fractional precision, like monetary values.

  • Excessive storage: Unnecessarily large precision may lead to larger storage than needed.

  • Potential data truncation: Without appropriate scale, fractional values will be truncated to whole numbers, potentially leading to data precision loss.

How to fix

This guide provides instructions to ensure that DECIMAL or NUMERIC data types are defined with appropriate precision and scale, preventing storage inefficiency and calculation errors.

Follow these steps to address the issue:

  1. Evaluate the data range and precision your application requires. Decide on the specific precision and scale needed for DECIMAL or NUMERIC columns or variables.

  2. Modify the declaration of DECIMAL or NUMERIC types to include explicit precision and scale. Use the format DECIMAL(p, s) or NUMERIC(p, s), where p is the precision and s is the scale.

  3. Review database schema to ensure any existing columns are updated to match the defined precision and scale, if necessary.

For example:

SQL
Copy
1-- Example of corrected declaration
2DECLARE @Rate DECIMAL(10, 2);
3-- Ensures @Rate can store numbers with up to 8 digits before and 2 digits after the decimal point

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
RequireNumericToHaveBothScaleAndPrecision

The parameter enables a requirement for the NUMERIC type to have both scale and precision.

yes
RequireDecimalToHaveBothScaleAndPrecision

The parameter enables a requirement for the DECIMAL type to have both scale and precision.

yes
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1DECLARE @var0 DECIMAL(20,0)
 2DECLARE @var1 numeric(11,0)
 3DECLARE @var2 DECIMAL
 4DECLARE @var3 numeric
 5
 6DECLARE @var4 numeric(14)
 7DECLARE @var5 numeric(14,2)
 8DECLARE @var6 [numeric](14)
 9
10DECLARE @var7 numeric       -- IGNORE:SA0081
11DECLARE @var8 [numeric](14) -- IGNORE:SA0081
12
13DECLARE @n numeric
14DECLARE @d decimal
15DECLARE @n180 numeric(18,0)
16DECLARE @d180 decimal(18,0)
17
18SET @n = CONVERT(numeric, @n);
19SET @n180 = CONVERT(numeric(18), @n180); 
20SET @n180 = CONVERT(numeric(18, 0), @n180);
21
22SET @d = CONVERT(decimal, @d);
23SET @d180 = CONVERT(decimal(18), @d180);
24SET @d180 = CONVERT(decimal(18, 0), @d180);
25SET @d180 = CONVERT([decimal](18), @d180);
26SET @n180 = CONVERT([numeric](18), @d180);
Analysis Results
  Message Line Column
1 SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale. 3 14
2 SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale. 4 14
3 SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale. 6 14
4 SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale. 8 14
5 SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale. 13 11
6 SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale. 14 11
7 SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale. 18 17
8 SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale. 19 20
9 SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale. 22 17
10 SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale. 23 20
…
See Also

Other Resources

Analysis Rules