چه مواقعی مجاز هستیم از نوع داده REAL استفاده کنیم؟

مقدمه

من پایگاه داده‌های زیادی را بررسی کردم که در آن‌ها ستون‌های عددی از نوع REAL یا FLOAT تعریف شده‌اند. این نوع داده‌ها که مقادیر را به صورت تقریبی نمایش می‌دهند، در پایگاه داده، برای نشان دادن اعداد بسیار بزرگ یا بسیار کوچک به کار می‌روند. همچنین محدوده وسیعی از مقادیر را نمایش می‌دهند و بیشتر برای اعداد سیستم‌های علمی و آزمایشگاهی کاربرد دارند. اما در بیشتر مواقع، اعدادی را نیاز داریم تا در پایگاه داده ذخیره کنیم که مقادیر دقیقی هستند و به صورت تقریبی محاسبه نشده‌اند. اعدادی مانند ۱۵.۸۷۳ یا ۹۸.۲ از این گروه هستند. این قبیل اعداد می‌توانند مربوط به میزان تراکنش، اندازه محصول یا حتی میزان دمای اتاق باشد.

استفاده از انواع داده REAL یا FLOAT برای موقعیتی که برنامه به یک عدد دقیق نیاز دارد، می‌تواند باعث مشکلاتی در اجرا شود. ممکن است وقتی برنامه‌ای که انتظار یک مقدار دقیق را دارد، با دریافت یک مقدار تقریبی، رفتار غیرمنتظره‌ای را بروز دهد و نتایج اشتباهی را تولید کند.

اخیراً درگیر یک پروژه انتقال داده بودم که در آن از من خواسته شد تا داده‌های مالی را از سیستم فعلی به سیستم جدید منتقل کنم. متأسفانه تمام ستون‌هایی که داده‌های مالی را در پایگاه داده فعلی ذخیره کرده بود، به صورت REAL تعریف شده بودند. قبل از اینکه مباحث مقاله را ادامه دهم، مفاهیم پیش‌نیاز در مورد انواع داده را شرح می‌دهم تا ادامه آن را بهتر متوجه شوید.

انواع داده های عددی در SQL Server

به طور کلی، انواع داده‌های عددی در SQL Server شامل دو دسته می‌شود. انواع داده‌های عددی که SQL Server ارائه می‌دهند، عبارتند از: داده‌های عددی دقیق و داده‌های عددی تقریبی.

انواع داده‌های عددی دقیق شامل:

• خانواده اعداد صحیح (integer ها) که عبارتند از TINYINT ،SMALLINT ،INT و BIGINT
• انواع دهدهی (DECIMAL) و عددی (NUMERIC)
• انواع MONEY و SMALLMONEY

انواع داده‌های تقریبی شامل:

• نوع داده‌‌ای FLOAT
• نوع داده‌ای REAL

کمی توضیح به موارد بالا اضافه می‌کنم. انواع داده‌های خانواده اعداد صحیح (integer ها) تنها در اندازه ذخیره‌سازی و محدوده مقادیر صحیح بایکدیگر تفاوت دارند. مطلبی بیش از این برای توضیح وجود ندارد. انواع DECIMAL و NUMERIC معادل هم هستند. این دو نوع مشابه‌اند؛ بنابراین در این مقاله، در مورد DECIMAL صحبت می‌کنیم.

داده DECIMAL

نوع DECIMAL شامل دو پارامتر است:

1. precision (p) 
2. scale (s)

precision، حداکثر تعداد کل ارقام اعشاری را که می‌توان در سمت چپ و راست نقطه اعشار ذخیره کرد، مشخص می‌کند. scale عبارت است از تعداد ارقام سمت راست اعشار. scale فقط برای اعدادی استفاده می‌شوند که رقم سمت چپ اعشار صفر نباشد. بنابراین رابطه ۰ <= s <= p برقرار است. نکته مهم در مورد نوع داده DECIMAL، دقیق بودن آن است؛ طوری‌که دارای تعداد ثابتی از ارقام در سمت چپ و راست نقطه اعشار بوده و می‌توانید هرمقدار اعشاری دقیق را که در این محدوده قرار می‌گیرد، نشان دهید. مقادیر در محدوده -۱۰^۳۸ + ۱ تا ۱۰^۳۸ – ۱ را دربرمی‌گیرد.

انواع داده MONEY و SMALLMONEY برای نشان دادن مقادیر مالی هستند و پیاده‌سازی آن بسیار شبیه به انواع DECIMAL و NUMERIC است و نیازی به توضیح بیشتر ندارند.

انواع داده‌ FLOAT و REAL

انواع داده‌ای که بیشتر علاقمند هستم در این مقاله در مورد آن صحبت کنم، انواع داده‌‌ای FLOAT و REAL است. این انواع داده برای اعداد تقریبی کاربرد دارند و براساس استاندارد IEEE 754 برای محاسبات ممیز شناور پیاده‌سازی شده‌اند. ممیز شناور ساختاری است که برای نمایش اعداد بسیار بزرگ یا بسیار کوچک کاربرد دارند. در این ساختار، اعداد تقریبی با تعداد ثابتی از اعداد نمایش داده می‌شوند و scale آن‌ها با توان (exponent) نشان داده می‌شود.

نمایش داخلی یک عدد FLOAT یا REAL از ۳ قسمت تشکیل شده است که عبارتند از: sign bit (S) ،exponent (E) ،fraction (F). در استاندارد IEEE 754 انواع مختلفی از نمایش ممیز شناور تعریف شده است که اندازه قسمت‌های exponent و fraction باهم متفاوت هستند. در SQL Server تنها دو نوع پشتیبانی می‌شود: single precision و double precision. جدول زیر اندازه‌های این دو نوع را نشان می‌دهد:

FLOAT به ما اجازه می‌دهد تا هر دو نوع را با یک پارامتر نشان دهیم که تعداد بیت‌ها در قسمت fraction ذخیره می‌شود. اما در SQL Server درواقع هر عدد بین ۱ تا ۲۴ را به عنوان ۲۴ در نظر می‌گیرد که به معنای یک عدد single precision خواهد بود و هر عدد بین ۲۵ تا ۵۳ را به عنوان ۵۳ در نظر می‌گیرد که به معنای یک عدد double precision خواهد بود. البته این موضوع مشکل خاصی نیست؛ چون می‌توانید به صورت FLOAT(25) یا FLOAT(37) مشخص کنید. در هر دو صورت شما نهایتاً یک عدد double precision خواهید داشت که ۵۲ بیت را برای بخش fraction و در کل ۸ بایت را در نظر می‌گیرد. نوع داده REAL درواقع مانند FLOAT(24) است که یک عدد single precision را نشان می‌دهد که ۲۳ بیت را برای بخش fraction و درکل ۴ بایت را در نظر می‌گیرد. پس در ادامه مقاله، از REAL صحبت می‌کنیم و به FLOAT نمی‌پردازیم؛ چون مشابه هم هستند. همچنین همانطور که بالاتر گفته شد، در این مقاله از DECIMAL صحبت می‌کنیم و نوع داده مشابه یعنی NUMERIC را در نظر نمی‌گیریم.

داده REAL

شاید کمی به نظرتان پیچیده به نظر می‌رسد. به مثال زیر دقت کنید:

DECLARE
    @Real AS REAL = 1.234567;

SELECT
    BinaryRepresentation = CAST (@Real AS BINARY(4));
GO

نمایش باینری عدد ۱.۲۳۴۵۶۷ که عددی REAL است به صورت 0x3F9E064B خواهد بود. این عدد را تحلیل می‌کنم. بیت اول که sign bit (S) است، تعیین می‌کند که عدد مثبت است یا منفی. مقدار مثبت با ۰ و مقدار منفی با ۱ نمایش داده می‌شود. ۸ بیت بعدی نشان دهنده exponent (E) است و ۲۳ بیت بعدی نشان دهنده fraction (F) هستند. در اینجا نحوه محاسبه S ،E و F نشان داده شده است:

DECLARE
  @Binary	AS BINARY(4)	= 0x3F9E064B ,
  @S		AS INT ,
  @E		AS INT ,
  @F		AS INT;

SET @S = CAST (@Binary AS INT) & 0x80000000;
SET @E = (CAST (@Binary AS INT) & 0x7F800000) / POWER (2,23);
SET @F = (CAST (@Binary AS INT) & 0x007FFFFF) * 2;

SELECT
  S	= @S ,
  E	= @E ,
  F	= @F;
GO

در مورد عددی که مثال زدیم، قسمت‌های آن به این صورت خواهد بود: S = 0 ،E = 127 ،F = 3935382. اکنون، طبق استاندارد IEEE 754 برای نمایش اعداد ممیز شناور، فرمول محاسبه عدد به صورت زیر خواهد بود:

(-۱)^S x 2^(E-127) x 1.F

پیاده‌سازی آن در T-SQL به صورت زیر خواهد بود:

DECLARE
  @S	AS INT	= 0 ,
  @E	AS INT	= 127 ,
  @F	AS INT	= 3935382;

SELECT
  RealValue =	CAST
        (
          CAST (POWER (-1.0 , @S) AS REAL) *
          CAST (POWER (2.0 , @E-127) AS REAL) *
          CAST ((@F | 0x01000000) AS REAL) / POWER (2 , 24)
          AS REAL
        );
GO

خروجی کد بالا ۱.۲۳۴۵۶۷ خواهد بود و این نشان می‌دهد تا اینجا، همه روال‌ها صحیح است. اما به یاد داشته باشید که REAL یک نوع داده تقریبی است. می‌خواهیم بررسی کنیم این تقریبی بودن به چه معنا است. به مثال زیر توجه کنید:

DECLARE
  @Real1	AS REAL	= 1.۲۳۴۵۶۷۸۹ ,
  @Real2	AS REAL	= 1.۲۳۴۵۶۷۸۹۰۱;

SELECT
  Real1	= @Real1 ,
  Real2	= @Real2;

SELECT
  BinaryReal1	= CAST (@Real1 AS BINARY(4)) ,
  BinaryReal2	= CAST (@Real2 AS BINARY(4));
GO

هر دو عدد در مثال بالا به صورت ۱.۲۳۴۵۶۸ نمایش داده می‌شوند و نمایش باینری هر دو عدد به صورت 0x3F9E0652 خواهد بود. اما این دو عدد متفاوت هستند که با یک مقدار تقریبی نشان داده شده‌اند. در واقع نمی‌توانید اعداد دقیق اصلی را در نوع داده REAL نشان دهید و برای نشان دادن اعداد دقیق، باید از نوع داده DECIMAL استفاده کنید.

به مثال زیر دقت کنید:

DECLARE
  @Real1	AS REAL	= 1.۲۳۴۵۶۷۸ ,
  @Real2	AS REAL	= 1.۲۳۴۵۶۷۸۹;

SELECT
  Real1	= @Real1 ,
  Real2	= @Real2;

SELECT
  BinaryReal1	= CAST (@Real1 AS BINARY(4)) ,
  BinaryReal2	= CAST (@Real2 AS BINARY(4));
GO

در این مثال، ما فقط کمی اعداد اصلی را تغییر دادیم. اکنون هر دو عدد به صورت ۱.۲۳۴۵۶۸ نمایش داده می‌شوند؛ اما نمایش‌های باینری متفاوت هستند. عدد اول به صورت 0x3F9E0651 و عدد دوم به صورت 0x3F9E0652 است. این مورد نیز باعث بروز مشکل می‌شود؛ چون اعداد باینری بایکدیگر متفاوت هستند و وقتی آن‌ها را در قالب اعداد باینری مقایسه می‌کنید، نتیجه عدم برابری دو عدد را نشان خواهد داد؛ درحالی که هر دو عدد تا دقت ۷ رقم اعشار، باهم برابرند.

DECLARE
  @Real1	AS REAL	= 1.۲۳۴۵۶۷۸ ,
  @Real2	AS REAL	= 1.۲۳۴۵۶۷۸۹;

SELECT
  Real1	= @Real1 ,
  Real2	= @Real2;

SELECT
  BinaryReal1	= CAST (@Real1 AS BINARY(4)) ,
  BinaryReal2	= CAST (@Real2 AS BINARY(4));

IF
  @Real1 = @Real2
BEGIN

  PRINT N'Numbers are identical';

END
ELSE
BEGIN

  PRINT N'Numbers are not the same';

END;
GO

پس از این پیش‌نیازهایی که گفته شد، به مسئله اصلی باز می‌گردیم. از من خواسته شده بود که داده‌ها را از پایگاه داده فعلی، که ستون‌های مالی آن از نوع REAL تعریف شده بود، به یک پایگاه داده جدید منتقل کنم. جدول اصلی به نام Billing.Transactions و دارای دو ستون OriginalAmount و BillingAmount بود که هر دو این ستون‌ها، به صورت REAL تعریف شده بودند. یکی از فرآیندهای انتقال داده‌ها، نیاز به استخراج تراکنش‌هایی بود که در آن‌ها original amount با billing amount یکسان باشد. برای این کار، کوئری زیر را پیاده‌سازی کردم:

SELECT
  ...
FROM
  Billing.Transactions
WHERE
  OriginalAmount = BillingAmount;
GO

این کوئری حدود ۱۲۰۰۰ رکورد را برگرداند. درحالی که نظر مشتری این بود که این تعداد رکورد منطقی نیست و انتظار داشت حدود ۱۹۰۰۰ رکورد را کوئری برگرداند. با توجه به آنچه که در این مقاله ارائه شد، احتمالاً تاکنون توانسته‌اید حدس بزنید که چه اتفاقی افتاده است. حدود ۷۰۰۰ ردیف وجود داشت که در آن‌ها original amount با billing amount درواقع یکسان بود اما نمایش‌های دودویی متفاوتی داشتند.

سؤالی که پیش می‌آید این است که چگونه این اتفاق افتاده است؟ و علت این تفاوت چیست؟ پاسخ این است که این اعداد، احتمالاً قبل از رسیدن به مقادیر نهایی، در انواع محاسبات، چه در برنامه و چه در پایگاه داده، وارد شده‌اند. برای مثال، چنین محاسباتی می‌تواند عملیات aggregation یا rounding باشد. از آن‌جایی که این اعداد از نوع داده‌های تقریبی هستند، نتایج محاسبات ممکن است متفاوت باشد.

مشتری نمی‌تواند متوجه بحث نمایش باینری اعداد شود. او انتظار داشت که با ۷۰۰۰ تراکنش باقی مانده، مشابه ۱۲۰۰۰ تراکنش دیگر رفتار شود. پس از گرفتن تأیید از مشتری، دقت اعداد را تا دو رقم اعشار در نظر گرفتم و کوئری را به صورت زیر بازنویسی کردم:

SELECT
  ...
FROM
  Billing.Transactions
WHERE
  CAST (ROUND (OriginalAmount , 2) AS DECIMAL(19,2))
  =
  CAST (ROUND (BillingAmount , 2) AS DECIMAL(19,2));
GO

این کوئری ۱۹۰۰۰ رکورد مورد انتظار را برگرداند. اما اگر این کوئری ناکارآمد باشد، بهینه‌ساز نمی‌تواند از هیچ index ای استفاده کند. زمانی که ستون‌های مالی را از اول به صورت REAL تعریف می‌کنید، این مشکلات را هم در ادامه خواهید داشت.

جمع بندی

در مثالی که در این مقاله آورده شد، مشکل فراتر از نادیده گرفته شدن ۷۰۰۰ رکورد است. تصور کنید مشتری در مورد عدد مورد انتظارش هشدار نمی‌داد و همان ۱۲۰۰۰ رکورد را به عنوان نتیجه نهایی می‌پذیرفت. درنتیجه، استفاده از کوئری همچنان ادامه پیدا می‌کرد و در آینده، در سیستم جدید باعث باگ جدی می‌شد و احتمالاً پیدا کردن باگ در آن شرایط، بسیار زمان‌گیرتر و با تلاش بسیار بیشتری امکان‌پذیر بود.

نتیجه‌ای که از این مقاله می‌توان گرفت، هرگز از انواع داده‌ای REAL یا FLOAT استفاده نکنید؛ مگر آن‌که واقعاً داده‌های شما از نوع تقریبی باشد. در ۹۹ درصد موارد، آن‌چه که واقعاً به آن نیاز دارید، DECIMAL است؛ چون شما نیاز دارید اعداد دقیق را نشان دهید.