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

In SQL Server, using data types like FLOAT and REAL can lead to precision issues because they are approximate-number types. This means not all values can be represented exactly, leading to rounding errors, which is problematic when precision is critical. Similarly, MONEY and SMALLMONEY do not store currency information alongside numeric values and have limited precision, which can also lead to inaccuracies in calculations. The SQL_VARIANT data type, while flexible, has numerous compatibility limitations and is not supported in several SQL Server features.

For example:

1-- Avoid using FLOAT for precise calculations
2CREATE TABLE Transactions (
3    Amount FLOAT
4);

Using FLOAT for Amount can lead to rounding errors in financial calculations. It’s recommended to use DECIMAL or NUMERIC for better precision.

• MONEY and SMALLMONEY lack currency context and may cause precision loss in complex calculations.

• FLOAT and REAL are imprecise and can lead to rounding errors, making DECIMAL a better choice.

• SQL_VARIANT is limited by its lack of support in several SQL Server features, including computed columns and full-text functions.

Replace deprecated or imprecise data types with more precise and supported alternatives in SQL Server.

Follow these steps to address the issue:

1. Identify columns that use deprecated or imprecise data types like FLOAT, REAL, MONEY, SMALLMONEY, and SQL_VARIANT.

2. Determine the appropriate replacement data type based on the use case:

   • For precise numeric calculations, replace FLOAT or REAL with DECIMAL or NUMERIC. Specify precision and scale to suit your needs.

   • For currency-related values, replace MONEY or SMALLMONEY with DECIMAL or NUMERIC and add a currency context if needed.

   • If SQL_VARIANT is used, consider using a specific data type that aligns with the intended use to avoid compatibility issues.

3. Alter the table to change the data type of the identified columns:

   SQL

   Copy

   1-- Example of altering a table to change data type
   2ALTER TABLE Transactions
   3ALTER COLUMN Amount DECIMAL(18, 2);

4. Review any application logic or stored procedures that interact with these columns to ensure that they are compatible with the new data types.

5. Test your database and application to verify that the change does not introduce errors or unexpected behavior.

For example:

1-- Correct usage with DECIMAL for precise calculations
2CREATE TABLE Transactions (
3    Amount DECIMAL(18, 2)
4);

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.

Other Resources