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

The topic describes the SA0081 analysis rule.

Message

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

Description

The rule checks the T-SQL code for use DECIMAL or NUMERIC data types without specifying length.

Avoid defining columns, variables and parameters using DECIMAL or NUMERIC data types without specifying precision, and scale. If no precision and scale are provided, SQL Server will use its own default values – (18, 0).

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.

Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 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