Click or drag to resize

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
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0081

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

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

 MessageLineColumn
1SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale.314
2SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale.414
3SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale.614
4SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale.814
5SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale.1311
6SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale.1411
7SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale.1817
8SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale.1920
9SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale.2217
10SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale.2320
...
See Also

Other Resources