Click or drag to resize

SA0247B : Don’t use FLOAT, REAL, MONEY, SMALLMONEY or SQL_VARIANT data types

The topic describes the SA0247B analysis rule.

Message

Don’t use FLOAT, REAL, MONEY, SMALLMONEY or SQL_VARIANT data types

Description

The rule checks T-SQL code for variables, columns and parameters declared as FLOAT, REAL, MONEY, SMALLMONEY or SQL_VARIANT data types.

Each of the these data types have some issues to be considered and should be avoided.

- MONEY and SMALLMONEY: There is no currency information that is stored along with the numeric value. The precision of the and the precision of the underlying type is BIGINT and INT, which may produce loss of precision and rounding errors when used with more complicated calculations. In these cases is better to use the DECIMAL or NUMERIC types.

- FLOAT and REAL: The FLOAT and REAL are approximate-number data types for use with floating point numeric data. Not all values in the data type range can be represented exactly as the floating point data is approximate.

As it is not usual for databases to perform intensive number calculations or scientific calculations, the FLOAT and REAL types can be usually better replaced by the DECIMAL, which is fixed precision and scale numeric type.

It is not usual for the not used in some scientific calculations, the DECIMAL type can be better replacement.

- SQL_VARIANT: This a data type stores values of a number of supported data types, but has some issues and restrictions, which have to be considered.

- not all data types are supported as its underlying base data type;

- It is not supported by Microsoft Azure SQL Database;

- it is not supported in computed columns, in the LIKE predicate, full-text indexes and full-text functions CONTAINSTABLE and FREETEXTTABLE;

- ODBC does not fully support it - SQL_VARIANT columns are returned as binary data;

- not well supported by ORM frameworks;

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1CREATE TABLE [dbo].[Employee]
 2( 
 3    [Title]  [nvarchar]( 8 ) NULL
 4  , [FirstName]  [dbo].[Name] NOT NULL
 5  , [MiddleName]  [dbo].[Name] NULL
 6  , [LastName]  [dbo].[Name] NOT NULL
 7  , [Salary] money  NOT NULL
 8  , [Description] sql_variant                  
 9  , [UnicodeDescription] [real]
10  , [Coefficient] float  NOT NULL
11);
12
13DECLARE  @coefficient AS float

Analysis Results

 MessageLineColumn
1SA0247B : Use NUMERIC or DECIMAL type instead of deprecated data type MONEY and SMALLMONEY.713
2SA0247B : Use specific type instead of sql_variant data type.818
3SA0247B : Use DECIMAL type instead of data type FLOAT and REAL.925
4SA0247B : Use DECIMAL type instead of data type FLOAT and REAL.1018
5SA0247B : Use DECIMAL type instead of data type FLOAT and REAL.1325
See Also

Other Resources