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

Understanding and avoiding potential pitfalls with certain SQL Server data types like FLOAT, REAL, MONEY, SMALLMONEY, and SQL_VARIANT can enhance database precision and reliability.

Description

The use of specific T-SQL data types can lead to various issues in SQL Server databases, which could impact data accuracy and performance. For instance:

For example:

SQL
Copy
1-- Example of problematic variable declarations
2DECLARE @price MONEY;
3DECLARE @amount FLOAT;
4DECLARE @variant SQL_VARIANT;

Using these data types can introduce challenges. MONEY and SMALLMONEY lack currency info and can cause rounding errors. Instead, DECIMAL is recommended for precise currency calculations. FLOAT and REAL are approximate, which makes DECIMAL a better alternative when precision is critical. The SQL_VARIANT type comes with several limitations, such as limited type support and incompatibility with Azure SQL Database.

  • Using MONEY may lead to precision loss and rounding errors during calculations.

  • FLOAT and REAL may not represent all values exactly, introducing inaccuracy in numeric data.

  • SQL_VARIANT is not fully supported across various SQL Server environments, which limits its practical use.

How to fix

This fix addresses the use of deprecated or problematic SQL Server data types that can impact data precision and reliability. The solution involves identifying these data types and replacing them with more suitable alternatives.

Follow these steps to address the issue:

  1. Review your SQL Server database schema and identify the usage of deprecated or problematic data types such as FLOAT, REAL, MONEY, SMALLMONEY, and SQL_VARIANT.

  2. Replace MONEY and SMALLMONEY data types with DECIMAL or NUMERIC to ensure accurate currency and financial calculations.

  3. Replace FLOAT and REAL with DECIMAL or NUMERIC for increased precision in numeric data, especially where exact values are critical.

  4. Avoid using SQL_VARIANT due to its limitations across various SQL Server environments. Instead, use specific data types that are fully supported and appropriate for the data being stored.

  5. Test the modifications in a development environment to ensure that the changes do not introduce new issues and that data operations perform as expected.

For example:

SQL
Copy
1-- Example of corrected variable declarations
2DECLARE @price DECIMAL(19,4);
3DECLARE @amount DECIMAL(18,4);
4-- Avoid using SQL_VARIANT where possible
5DECLARE @example INT;

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.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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
  Message Line Column
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
See Also

Other Resources

Analysis Rules