SA0247A : Don’t use FLOAT, REAL, MONEY, SMALLMONEY or SQL_VARIANT data types
The topic describes the SA0247A analysis rule.
Don’t use FLOAT, REAL, MONEY, SMALLMONEY or SQL_VARIANT data types
The rule checks the database for tables, views, functions and stored procedures, which have parameters or columns 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 ContextOnly scope and is applied only on current server and database schema.
Rule has no parameters.
The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
Design Rules, Bugs
There is no additional info for this rule.