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

Introduction

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:

-- Example of problematic declaration
DECLARE @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:

-- Example of corrected declaration
DECLARE @Rate DECIMAL(10, 2);
-- 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

DECLARE @var0 DECIMAL(20,0)
DECLARE @var1 numeric(11,0)
DECLARE @var2 DECIMAL
DECLARE @var3 numeric

DECLARE @var4 numeric(14)
DECLARE @var5 numeric(14,2)
DECLARE @var6 [numeric](14)

DECLARE @var7 numeric       -- IGNORE:SA0081
DECLARE @var8 [numeric](14) -- IGNORE:SA0081

DECLARE @n numeric
DECLARE @d decimal
DECLARE @n180 numeric(18,0)
DECLARE @d180 decimal(18,0)

SET @n = CONVERT(numeric, @n);
SET @n180 = CONVERT(numeric(18), @n180);
SET @n180 = CONVERT(numeric(18, 0), @n180);

SET @d = CONVERT(decimal, @d);
SET @d180 = CONVERT(decimal(18), @d180);
SET @d180 = CONVERT(decimal(18, 0), @d180);
SET @d180 = CONVERT([decimal](18), @d180);
SET @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
	…

Analysis Rules