SA0247B : Don’t use FLOAT, REAL, MONEY, SMALLMONEY or SQL_VARIANT data types
The topic describes the SA0247B analysis rule.
Don’t use FLOAT, REAL, MONEY, SMALLMONEY or SQL_VARIANT data types
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;
The rule has a Batch scope and is applied only on the SQL script.
Rule has no parameters.
The rule does not need Analysis Context or SQL Connection.
Design Rules, New Rules
There is no additional info for this rule.
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
|1||SA0247B : Use NUMERIC or DECIMAL type instead of deprecated data type MONEY and SMALLMONEY.||7||13|
|2||SA0247B : Use specific type instead of sql_variant data type.||8||18|
|3||SA0247B : Use DECIMAL type instead of data type FLOAT and REAL.||9||25|
|4||SA0247B : Use DECIMAL type instead of data type FLOAT and REAL.||10||18|
|5||SA0247B : Use DECIMAL type instead of data type FLOAT and REAL.||13||25|
© Ubitsoft Ltd. All Rights Reserved.